Using INDIRECT in Data Validation with VBA

[music_al]

Hi

I have 2 data validated columns, lets say the first is FOOD_TYPE and the second is the FOOD_ITEM to use the old favourite.

Rather than set the Data Validation for the second column with the INDIRECT Function within the cell itself, I want to do this in VBA code. The validation of the second column being based on the contents of the cell immediately to its left. This is what I have...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cur_Cell As String
'Returns the address of the cell immediately to the left of the selected cell.
Cur_Cell = ActiveCell.Offset(0, -1).Address

'Sets the Data Validation for the cell dependent on the contents of the cell immediately to its left.
With ActiveCell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=indirect(cur_cell)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

The code highlighted in red seems to be what is causing the problem. What should the correct syntax for this be?

Regards

Al

 

[ukmikeb]

Hi Al

You're trying to pass the variable Cur_Cell in the formula so :-

xlBetween, Formula1:=[COLOR=#ff0000]"=INDIRECT(" & Cur_Cell & ")"[/COLOR]

should hopefully solve your problem.

 

[music_al]

Thank you Mike. That works perfectly.


Al

 

[Jonmo1]

If you're going to be using VBA for this, why bother with Indirect in the Validation?
Why not just put what the indirect ends up referring to in the validation?

What is in ActiveCell.Offset(0, -1) ?
A text string of a Named Range?
Why not just put THAT in the validation ?

 

[SBF12345]

With Range("A37").Validation        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT("A36")"
        .InCellDropdown = True
    End With

Same problem, still won't run with the added " ". Any thoughts? A37 contains an existing drop down menu.