Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-27-2021, 04:49 AM
soroush.kalantari soroush.kalantari is offline Adding validation list based on a selection by macro? Windows 10 Adding validation list based on a selection by macro? Office 2016
Novice
Adding validation list based on a selection by macro?
 
Join Date: Jun 2021
Posts: 16
soroush.kalantari is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 11-27-2021, 08:55 AM
NoSparks NoSparks is offline Adding validation list based on a selection by macro? Windows 10 Adding validation list based on a selection by macro? Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 819
NoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the rough
Default

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
Reply With Quote
  #3  
Old 11-27-2021, 09:00 PM
soroush.kalantari soroush.kalantari is offline Adding validation list based on a selection by macro? Windows 10 Adding validation list based on a selection by macro? Office 2016
Novice
Adding validation list based on a selection by macro?
 
Join Date: Jun 2021
Posts: 16
soroush.kalantari is on a distinguished road
Default

thank you very much, NoSparks. The problem was solved.
Reply With Quote
Reply

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
Adding validation list based on a selection by macro? Autofill a field based on drop down list selection lascough20 Word 2 09-21-2018 02:24 AM
Adding validation list based on a selection by macro? 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
Adding validation list based on a selection by macro? Data Validation List based on Two Named Ranges Rich18144 Excel 4 01-14-2016 04:37 AM
Adding validation list based on a selection by macro? Dynamically changing drop-down list based on selection? (Word Form) laurarem Word 1 02-21-2013 10:17 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:12 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft