Excel 2011 for Mac - VBA - Form Control Combo Box - Return Selected Value

[jarrah]

Hello,

I have a macro that needs to work on both Windows (Excel 2010) and Macs (Excel 2011), but I'm having problems with the Combo Box.

On Windows the following code works fine:

    On Error Resume Next
    With wsTK.Shapes("month").ControlFormat
        selMonth = .List(.Value)
    End With
    On Error GoTo 0
    If selMonth = 0 Then
        MsgBox "Please select a month from the drop-down box"
        Exit Sub
    End If

However on a Mac it doesn't return the selected value to "selMonth" and so as this variable is empty, it displays the msgbox and exits the sub.

Would someone be able to help find a solution as I've been Googling for the past hour with no luck.

Thanks.

 

[jarrah]

Hoping to catch someone in the evening with my question before it disappears off the first page.

Thanks.

 

[jarrah]

I eventually found the problem - basically the Mac version of office doesn't like passing worksbooks/worksheets as a variable to ControlFormat.

I basically have to set the worksheet name as it won't accept a variable:

With Worksheets("Sheet1").Shapes("month").ControlFormat
        wbkName2 = .List(.Value)
End With

instead of

wsTK = worksheets("Sheet1")

With wsTK.Shapes("month").ControlFormat 
    selMonth = .List(.Value) 
End With

On a different but similar note, this code to detect an option button works ok on a Mac and Windows

If wsTK.OptionButtons("option1").Value = 1 Then
      msgbox "You have chosen x option"
end if

The problem also happens with Check Boxes, so is there a command similar to "OptionButtons" for Check Boxes that would work like below because it seems "ControlFormat" is the issue here?

if wsTK.checkbox("checkbox1").value = 1 then call function1

Instead of

if worksheets("Sheet1").shapes("checkbox1").controlFormat.value = 1 then call function1

Thanks.