Advanced Data Validation question in Excel/VBA

[MrM121]

Hi Everyone,

Got a bit of a problem and I have been racking my brains out over it!

Essentially the problem is this:

I want to be able to create validation drop-down boxes dynamically, say for instance, when a commandbutton is pressed. Code is:

With Range("E2").Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$A$30:$A$50"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With


However, this generates an error message (when you run it from a commandbutton). If you run it using the debug window, everything is just fine!

One possible solution is that I just use a 'Button' from the forms menu, and attach the macro to it. I would prefer not too, as this substantially reduces the capabilities of the program.

Does anyone have any solutions to this problem, or even reasons for it's occurance?

Thank you,

Nick

 

[byundt]

Nick,
You need to activate the cell first.

Sub CommandButton1_Click()
    With ThisWorkbook.Worksheets("Sheet1").Range("E2")
        .Activate
        .Validation.Delete 'Do this in case there is an existing validation
        .Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=$A$30:$A$50"
    End With
End Sub

Brad

 

[xlbo]

Nope - you just need to set the "TakeFocusOnClick" property of the textbox to FALSE.

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[MrM121]

byundt,

Thank you very much - it worked like a dream - I can't believe I didn't think of trying it to be honest - I feel a bit silly now.

Once again, thank you.

Nick Meacoe

 

[MrM121]

xlbo,

Your's is a fantastic solution as well - I am torn between which one to use.

Thank you.

PS - I just need to find a way to programatically add buttons at runtime on a spreadsheet now. Cheers

 

[xlbo]

Just record yourself adding a button - then you have the basic code to do so going forwards...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question