Can't set ControlFormat.Value on VBA drop down list in Mac

1
votes

I've made a navigation system in Excel that used two interrelated dropdown boxes (form control) in VBA. First is a main category (Drop Down 186), the second is sub categories in that main category.(Drop Down 187)

When I choose the main category(186) I reset the sub category (187) to list item #1 using:

Sheets("Equipment").Shapes("Drop Down 187").ControlFormat.Value = 1

This works fine on a PC, but gives a 1004 error in Excel 2016 with a message of "Unable to set the Value property of the DropDown class.

Any ideas? Pretty sure this works fine on Excel 2011.

1
excelvbamacos
I don't have my MAC handy but what happens when you replace the word Sheets("Equipment") with Worksheets("Equipment") in Excel 2011? I know what I am asking is weird but then so is This – Siddharth Rout

1 Answers

0
votes

Edit: Ok, so I lied below. My answer above fixed another crash that was happening in Excel for Mac 2011, but we still get the crash on 2016. Anyone have any ideas?

Siddharth, I tried changing Sheets to Worksheets, but get the same error. Thanks for the idea.

OK, I figured this out. The dropdown boxes are not populated by default when you launch the file. Mac Excel has a weird way of displaying and using the dropdown boxes, which requires the user to click and hold the arrow on the dropdown box to select a list item. If you just click on it, no list item is selected, but the associated macro runs still. And throws an error since there is no variable to process. So I've populated the dropdown boxes on startup. It wasn't a problem in Windows since there is no way to do this. For the record, by the look of things Excel 2016 looks WAY better in the way of buttons etc than 2011 did. Thanks, Joel