Using INDIRECT in Data Validation with VBA

music_al

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

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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.
 
Upvote 0
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 ?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,214,658
Messages
6,120,778
Members
448,992
Latest member
prabhuk279

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
Back
Top