Adding validation list based on a selection by macro?

soroush.kalantari · #1 11-27-2021, 04:49 AM

I want to write a macro such that it add a validation list based on a selection in a cell lying immediately before selection. For example, if I selected A101:A103, This macro would add a validation list in A100, including values of A101:A103. I have written following macro, but it doesn’t lead to my expected result. (It add a validation list in A100, including just “range01:range02". In the other words, the macro treats range01 and range02 as texts not parameters). Can anybody guides me on this issue?

Sub exdatalist()

Dim range01 As Variant
Dim range02 As Variant
Dim n As Variant
n = Selection.Rows.Count
range01 = Selection.Rows(1).Address
range02 = Selection.Rows(1).Address

Selection.Rows(1).Offset(rowOffset:=-1, columnOffset:=0).Activate

With ActiveCell.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="range01:range02"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End Sub

NoSparks · #2 11-27-2021, 08:55 AM

Quote:

For example, if I selected A101:A103, This macro would add a validation list in A100, including values of A101:A103.

Code:

Sub testing()
 
With Selection.Cells(1).Offset(-1).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=" & Selection.Address
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With
    
End Sub

soroush.kalantari · #3 11-27-2021, 09:00 PM

thank you very much, NoSparks. The problem was solved.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Text based on selection of dropdown list	cloud67	Word VBA	2	08-09-2019 06:46 AM
Autofill a field based on drop down list selection	lascough20	Word	2	09-21-2018 02:24 AM
how to make sections hidden/appear based on selection in Cont. Ctrl. Drop-down list	Irimi-Ai	Word	5	04-25-2017 09:31 AM
Data Validation List based on Two Named Ranges	Rich18144	Excel	4	01-14-2016 04:37 AM
Dynamically changing drop-down list based on selection? (Word Form)	laurarem	Word	1	02-21-2013 10:17 PM