Problem adding checklist in Excel worksheet using VBA

Question

I have an excel sheet that loads a dynamic result set. I need to add a YES / NO dropdown at the end of each line after loading all the data. I have to do this dynamically since I do not know the size of the result set in advance. The following code generates a "Defined or user-defined error":

Dim firstRow As Integer Dim lastRow As Integer Dim I As Integer Dim VOptions As String VOptions = "1. Yes, 2. No" firstRow = GetResultRowStart.row + 1 lastRow = GetResultRowStart.End(xlDown).row For I = firstRow To lastRow Range("AO" & firstRow & ":AO" & lastRow).Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=VOptions .IgnoreBlank = True .InCellDropdown = True .InputTitle = "Options" .ErrorTitle = "" .InputMessage = "Click yes or no" .errorMessage = "" .ShowInput = True .ShowError = True End With Next I 

The GetResultRowStart method gives me a line starting with which the result data is filled in on the sheet. I used this method in other parts of the code too and it works great. Debugging using message boxes suggested an error that occurs in a Range (..) expression. Select

Any ideas on the cause of this error.

Answer 1

Final thoughts on this:

Setting the SetFocusOnClick property of each button in the book to false seems to do the trick (at least for now). But if this is a prerequisite, it should not work at all with a value set to true. However, this sometimes happened. But this is a reliable solution that I found.

Answer 2

Let me try to direct my inner-Spolsky here:

If you are linking to a range other than ActiveSheet , you must fully qualify the link .

Something like the following should work:

 ActiveWorkbook.Sheets("mysheet").Range("AO" & firstRow & ":AO" & lastRow).Select 

Answer 3

I also faced the same problem, "automation error". What I did was activate the sheet on which I was going to include the check list, and the error just disappeared.

Answer 4

The solution I used was to unprotect the sheet before using xx.validation, and then protect, if subsequently. [I did not need to do this in Excel 2000, and I think that I did not need to do this in Excel 2003 until the service pack was added, although it cannot say 100%.]

Answer 5

I just experienced a very similar problem in Excel. I found that the program code for the validation drop-down list worked fine when I ran it in the direct window, but did not work when called using the button on the worksheet. I realized this because the button had focus, and no attempt to try to select or activate a sheet or cell in the code before setting the check did not seem to fix it. However, I just realized that Excel has the "TakefocusOnClick" property, which is set to True by default. By setting this to False, the button never focuses, and I hope my code works fine now to set the check.

This may not be the answer to all the verification problems, but I hope that there can only be one who can benefit from the above.

Greetings.

Answer 6

This is a variant of the Add error of the Verification Error object. Here are the possible causes and solutions:

• Protected Worksheet . The sheet on which data verification is added cannot be protected, even if the cells to which the verification is added are not locked . > and even if the protection mode is UserInterfaceOnly . You must completely remove protection from the sheet, add verification, and then re-protect the sheet.

• Loss of focus on the range of cells in the worksheet . If the focus was made by any control on the worksheet (usually using the command button) that was previously pressed by the user, this error will be caused when the Validation.Add method is called. (Indeed, this is true!) This applies, in particular, to any button on a command that executes code that adds verification, but also applies to any control on a sheet that could be clicked before executing this code. Since there seems to be no legitimate link between the focus state and adding data validation to a cell or range, I believe this is an Excel error. Here are the workarounds:

  A. Prevention of loss of focus over a range of cells in worksheets. Set the TakeFocusOnClick property of all controls in the False sheet.

  B. Get focus for a range of worksheet cells: in VBA code, call Select before executing the Validation.Add method. > method of any cell on the sheet. The logical choice is to select a cell or range to which data validation is added, but any cell will do.

Answer 7

First of all, you need to get rid of the Selection object. This is best for Macro Recorder :)

btw in your loop every time you select the same block over and over again, even if you do more processing on it, consider choosing a block that will work on each iteration, or delete the whole loop.

Can you try this after a cycle?

 With ActiveWorkbook.ActiveSheet.Range("AO" & firstRow & ":AO" & lastRow).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=VOptions .IgnoreBlank = True .InCellDropdown = True .InputTitle = "Options" .ErrorTitle = "" .InputMessage = "Click yes or no" .ErrorMessage = "" .ShowInput = True .ShowError = True End With 

Answer 8

I had the same problem and found that the error was related to setting Application.ReferenceStyle

See the corrected code below -

 If Application.ReferenceStyle = xlR1C1 Then .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=R1C16:R" & foldercnt & "C16" Else .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$P1:$P" & foldercnt End If