drop down list in VBA

[Blobajob88]

Hi,

Can anyone tell me why the following code won't work:

Range("M5").Validation.Delete
Range("M5").Validation.Add Type:=xlValidateList AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Orange,Apple,Mango,Pear,Peach"
Range("m5").Validation.InCellDropdown = True

 

[Fluff]

You're missing a comma before AlertStyle

 

[Blobajob88]

What an idiot I am.

Thanks

 

[Fluff]

Easily missed, unfortunately.

 

[Blobajob88]

Hi,

This is possibly another silly mistake but now I've changed my table drop down to a range in another sheet but it will only work if that sheet is activated. Is there anything I can add so it doesn't need to be on the sheet called Sheet1?

Range("M5").Validation.Delete
Range("M5").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Sheet1!B4:B8"
Range("M5").Validation.InCellDropdown = True

 

[Fluff]

Do you have a sheet called "Sheet1" as seen on the sheet tab?

 

[Blobajob88]

Yes

 

[Blobajob88]

Just realised. I had the sheet protected. I unprotected it and it works fine.

One final question - Do you know if you can change the font size of the text within the dropdown so it is clearer without just zooming in?

 

[Fluff]

Do you know if you can change the font size of the text within the dropdown

Not that I'm aware of, without changing you system defaults.