data validation problem with vba

mbenner1

New Member
Joined
Feb 22, 2005
Messages
13
I am trying to use the following code snippet from MSDN.
It does not work.

Private Sub Workbook_Open()
' only the code is from MSDN not the Sub and End Sub
'statements

Range("e5").Validation _
.Modify xlValidateList, xlValidAlertStop, _
xlBetween, "=$A$1:$A$10"

End Sub

What should a valid code look like so that cell e5 get the list of numbers in A1:A10?
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
I basically ended up with the same code by using the macro recorder; the only difference is that my recorder-generated code has

Formula1:="=$A$1:$A$10"

not just the range. Try adding the Formula1:= to your code.
 

LEXCERM

Active Member
Joined
Jun 26, 2004
Messages
306
Hi there,

Try this:-

Private Sub Workbook_Open()

Sheets("sheet3").Select

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

End Sub

A bit long winded but it does the trick.

Regards,
PAUL.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top