#1
|
|||
|
|||
Adding validation list based on a selection by macro?
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 |
#2
|
|||
|
|||
Quote:
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 |
#3
|
|||
|
|||
thank you very much, NoSparks. The problem was solved.
|
Tags |
macro, validation |
Thread Tools | |
Display Modes | |
|
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 |