VBA for Data Validation "Input message" text from offset cell value

[edgarsrom]

Hello,

could anybody help with VBA code please? I am not sure if it is even possible, below is what I am trying to achieve:

For example, I have column "A" with various text string values(notes).

Say A1= Action text 1
A2 = Action text 2
A3 = Action text 3

all the way down to the LastRow

Now, I would like to pick up cell values from Range("A1:A" & LastRow) all values and add them into Data Validation --> "Input Message" into column "B"

Something like that:

Sub ValidationTest()
Dim LastRow As Long


LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    With Range("b2:b" & LastRow).Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "ACTION TEXT"
        .ErrorTitle = ""
        .InputMessage = ???
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

I am not able to figure out how to add offset(,-1) value for each cell in "B" column into .InputMessage =

Any help much appreciated!

Thanks

 

[MickG]

Perhaps:-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastRow As Long

If Target.Column = 2 Then
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    With Range("b2:b" & LastRow).Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "ACTION TEXT"
        .ErrorTitle = ""
        .InputMessage = Target.Offset(, -1).Value
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End If
End Sub

 

[edgarsrom]

Works perfect, thank you very much for your help!!! Much appreciated

 

[MickG]

You're welcome