working with Validation List in Excel (Add method)

[Dougy]

Hello !

I'm trying to dynamically modify Validation Lists in an Excel document but I bumped into an issue I have been trying to work out for days without success. :-(

The weird thing is that it works fine when I run the code as a macro but it notifies me with an error when I run it through an event ; such as a CommandButton1_Click() in this case.

Error notified on the ".Add Type:=..." line :
Run-time error '1004':
Application-defined or object-defined error

Code originally created by the Macro generator, and slightly modified afterwards :
The modification consist of : "Worksheets("Sheet1&quot.Range("a1&quot" wich was originally : "Selection" .

Sub Macro1()
With Worksheets("Sheet1&quot.Range("a1&quot.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="a,b,c,d,x21"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

I even tried to copy and paste code from the Micrososft help example related to the "Validation object" and to run it, but it simply notified me an error at the same place on the line begining with ".Add ...".

I guess I should have missed some important concept in Visual Basic programmation, but I just can figure out what ???

Thanks in advance for your help if anybody has got an idea

 

[nath]

Hi Dougy,

I tried the code, and it works fine, from a button or from the module.

When you say you run it through an event, I am not sure what you mean. Did you add a button on your spreadsheet and associate the macro to it (right click on button, click on assign macro and select Macro1)? I also tried with a button for which I don't assign a new macro but put the code in the Button1_Click() event in a module and it works fine as well.

I think it might be useful to
1. try the code on a fresh spreadsheet and create the button from scratch (just in case you spreadsheet got corrupted somehow)
2. If no success, let us know exactly, step by step how you create things.

Hope this might help

Nath

 

[Dougy]

Yeepee !

Thanks Nath. You'r right it works fine.

The thing is that sometime one invents problem that shouldn't be there...

What I did was :

The code was created by the macro generator, so it was somehow still considered as a macro. Even after my small modification.

If I go to the menu Tools/Macro/Macro, the macro Macro1 appear. But if I create from scratch a procedure in a module it will never appear in the Menu stated above.

That is the poitn I think.

I edited by myself the code within the Button event procedure : CommandButton1_Click() and added just a call to the Macro1.

Private Sub CommandButton1_Click()
Macro1
End Sub

It seems the environement is different when you run a macro called from an event edited manually than when you run a macro from the menu or from a button that has been linked to the macro (as you did with the help of the contextual Right-click menu).

When I was having a look at the debug window, the Validation object seemed to be strangely initialized (many properties not defined, even when I manually added a Validation list and removed the ".Delete" line).

Well, it might be a bug as well in fact ? Might it not ?

Thanks anyway Nath
Great !