Trouble creating Excel drop down list using VBA 1

[socomfort]

Hi,

I would like to create a drop down list on the fly using only VBA. I know how to create a single column drop down using the following:

  Public Sub CreateList()
    With ActiveCell
      .Validation.Delete
      .Validation.Add xlValidateList, , , "=" & "MyRange"
    End With
  End Sub

Rather than storing a list on the worksheet named "MyRange", I am wondering if it is possible to store the values in an array and then use the contents of the array in something like the code above:

  Dim MyArray as String
  MyArray = Array("Value1", "Value2", "Value3", "Value4")

I am stumped, so any direction you could give me is most appreciated.

thanks,

Ben

 

[rmikesmith]

Ben,

Try the following modified procedure:

Public Sub CreateList()
  With ActiveCell
    .Validation.Delete
    .Validation.Add xlValidateList, , , "Value1,Value2,Value3,Value4"
  End With
End Sub

From the VBA Help, the Formula1 parameter of the Validation Add method takes either a comma-delimeted list or a worksheet reference to a list. You can, of course, create the list on the fly if need be.

Regards,
Mike

 

[socomfort]

Thanks for you help, Mike! That did the trick. I'm rather new at all of this, and the help sections of the IDE can be quite confusing.

Just wondering, is there a book or website that you can recommend for MS Excel VBA? I have a copy of VBA for Dummies (4th ed.) but it doesn't go as much in depth as I'd like for it to.

Anyway, thanks again for your help.

best regards,

Ben

 

[rmikesmith]

Ben,

I would have no qualms about recommending John Walkenbach's "Excel xxxx Power Programming With VBA" series. I have the XL 2000 version and still refer to it. Keep in mind that no book will cover all the myriad bits of the Excel object model nor have an answer for its endless idiosyncracies. That just takes a combination of exploring the VBA help (pretty good on some topics, less than helpful on others), experimentation (i.e. trial & error) and asking for help in these forums (someone else has likely already suffered through the same issue).

Good Luck
Mike