Using INDIRECT in Data Validation with VBA

music_al

Board Regular
Joined
Nov 26, 2008
Messages
131
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
Hi Al

You're trying to pass the variable Cur_Cell in the formula so :-
Code:
xlBetween, Formula1:=[COLOR=#ff0000]"=INDIRECT(" & Cur_Cell & ")"[/COLOR]
should hopefully solve your problem.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Well-known Member
Joined
Jul 26, 2014
Messages
614
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,304
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top