Data Validation Macro, custom specified range

Question

I am writing a code in excel (VBA) that involves creating a Data Validation list from a range of cells in the same sheet. However this range changes by size and by content.

Part of the code I am using pertained to data validation is found herein:

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

Where RangeData is the variable range that I defined earlier.

The first issue I have is with the RangeData, I am getting an error. I would like to know if there is an alternate way to specify the range. Please keep in mind that the range is frequently changing.

Second, I would like the macro to launch whenever I insert a new row in the table. I can't seem to work out how to do it.

Your responses are highly appreciated and thanks in advance for you time!

Post the code that Dims RangeData and establishes its value. — Gary's Student
– Gary's Student, Commented Feb 17, 2015 at 13:41

Davesexcel · Accepted Answer · 2015-02-17 12:13:14Z

Right click the sheet tab and select view code, you can either type the code into the Worksheet_Change event or just copy and paste it.

Private Sub Worksheet_Change(ByVal Target As Range) Dim Rws As Long, Rng As Range Rws = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range(Cells(1, 1), Cells(Rws, 1)) Rng.Name = "RangeData" With Range("C1").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=RangeData" End With End Sub

Not knowing what is the selected cells, I used range("C1") for your data validation.

enter code

data val

Collectives™ on Stack Overflow

Data Validation Macro, custom specified range

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related