Macro to set new data validation each time


M

Munchkin

I need my macro to reset the data validation for D6 each time it is run as
the range of rows will increase as new info is added. How can I do this?

Range("D6").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$A$1:$A$547"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
 
Ad

Advertisements

O

Otto Moehrbach

Munchkin
I would name the list range something like "TheList". Then setup the
Data Validation cell, select List, and in the Source block put "=TheList"
without the quotes. Then place the following macro in the sheet module of
your sheet. To access that module, right-click the sheet tab, select View
Code. "X" out of the module to return to your sheet..
This macro will reset the range "TheList" whenever the user select (clicks
on) D6. Post back if you need more. HTH Otto
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("D6")) Is Nothing Then
Range("A1", Range("A" & Rows.Count).End(xlUp)).Name = "TheList"
Range("D5").Select
End If
End Sub
 
Ad

Advertisements

D

Don Guillett

Instead, I would use d defined name for your validationlist and use that
name
insert>name>define>name it>vallist
in the refers to box use an offset formula such as
=offset($a$1,0,0,counta($a:$a),1)
if blanks use something besides counta
Now, your list will be self adjusting.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top