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