Help with CommandButton vs Excel 2002

[YoungGuy]

Hi,
I am fairly new at programming for Excel so please forgive my naivety. I am trying to port an Excel workbook with macros from a Windows machine to a Mac. After opening the workbook on the Mac I found that none of the macros seemed to work. Upon further investigation I found that whatever version of Excel that was used to create the workbook used an "=EMBED("Forms.CommandButton.1","")" construct for all of the buttons on the spreadsheet. I read several articles on line about using command buttons and tried some of the examples but the versions that I have (2002 on Windows and 2004 on Mac) do not create an "=EMBED()" construct. Instead they just create a macro call like "Sub Button1_Click()". When I went through the workbook and deleted all of the "=EMBED()" constructs and just linked each button to the appropriate macro it seemed to work fine on both the Windows and Mac machines.
Now I have gotten deeper into the workbook and have found that "=EMBED("Forms.CheckBox.1","")" and "=EMBED("Forms.OptionButton.1","")" are also used. When I tried to create my own checkbox or option button I found that these also did not use the "=EMBED()" construct any more and just linked to a macro such as "Sub MyCheck_Click()" or "Sub MyOption_Click()". With the "=EMBED()" form you could get the value of the check box by looking at, for example, MyCheck.Value but that no longer seems to be the way to get at the information on if the box is checked or not. I tried the following two tests but without success. They always return "False".

Sub MyCheck_Click()
If MyCheck = True Then
MsgBox "Checked"
ElseIf MyCheck = False Then
MsgBox "Unchecked"
Else
MsgBox "Neither state returned"
End If
End Sub

Sub MyOption_Click()
If MyOption = True Then
MsgBox "Checked"
ElseIf MyOption = False Then
MsgBox "Unchecked "
Else
MsgBox "Neither state returned "
End If
End Sub


So, my question is twofold: 1) Have things changed with respect to the form of the "CommandButton" between versions prior to 2002 and 2002?, and 2) How do I now get to the value of the checkbox?


Thanks for your help!

[Dave Peterson]

I don't use a Mac, but from posts I've read, Mac's don't support controls from
the Control toolbox toolbar.

Maybe you could use the checkboxes/optionbuttons/buttons from the Forms toolbar.

On re-reading, it sounds like that's what you did.

You can create a macro in a General module and assign it to each checkbox.

Option Explicit
sub CheckBoxClick()
dim myCBX as checkbox
set mycbx = activesheet.checkboxes(application.caller)

if mycbx.value = xlon then
msgbox "It's checked
else
msgbox "it's not checked"
end if
end sub

(xlon or xloff are what you'd use to check.)

Option Explicit
sub OptionButtonClick()
dim myOptbtn as OptionButton
set myoptbtn = activesheet.OptionButtons(application.caller)

if myOptBtn.value = xlon then
msgbox "It's checked
else
msgbox "it's not checked"
end if
end sub

Although, it doesn't quite make as much sense with an optionbutton (if you click
on it, it's on).



YoungGuy wrote:
>
> Hi,
> I am fairly new at programming for Excel so please forgive my
> naivety. I am trying to port an Excel workbook with macros from a
> Windows machine to a Mac. After opening the workbook on the Mac I
> found that none of the macros seemed to work. Upon further
> investigation I found that whatever version of Excel that was used to
> create the workbook used an "=EMBED("Forms.CommandButton.1","")"
> construct for all of the buttons on the spreadsheet. I read several
> articles on line about using command buttons and tried some of the
> examples but the versions that I have (2002 on Windows and 2004 on Mac)
> do not create an "=EMBED()" construct. Instead they just create a macro
> call like "Sub Button1_Click()". When I went through the workbook and
> deleted all of the "=EMBED()" constructs and just linked each button to
> the appropriate macro it seemed to work fine on both the Windows and Mac
> machines.
> Now I have gotten deeper into the workbook and have found that
> "=EMBED("Forms.CheckBox.1","")" and "=EMBED("Forms.OptionButton.1","")"
> are also used. When I tried to create my own checkbox or option button
> I found that these also did not use the "=EMBED()" construct any more
> and just linked to a macro such as "Sub MyCheck_Click()" or "Sub
> MyOption_Click()". With the "=EMBED()" form you could get the value of
> the check box by looking at, for example, MyCheck.Value but that no
> longer seems to be the way to get at the information on if the box is
> checked or not. I tried the following two tests but without success.
> They always return "False".
>
> Sub MyCheck_Click()
> If MyCheck = True Then
> MsgBox "Checked"
> ElseIf MyCheck = False Then
> MsgBox "Unchecked"
> Else
> MsgBox "Neither state returned"
> End If
> End Sub
>
> Sub MyOption_Click()
> If MyOption = True Then
> MsgBox "Checked"
> ElseIf MyOption = False Then
> MsgBox "Unchecked "
> Else
> MsgBox "Neither state returned "
> End If
> End Sub
>
> So, my question is twofold: 1) Have things changed with respect
> to the form of the "CommandButton" between versions prior to 2002 and
> 2002?, and 2) How do I now get to the value of the checkbox?
>
> Thanks for your help!
>
> --
> YoungGuy
> ------------------------------------------------------------------------
> YoungGuy's Profile: http://www.excelforum.com/member.php...o&userid=29654
> View this thread: http://www.excelforum.com/showthread...hreadid=493663

--

Dave Peterson

[YoungGuy]

David,
You're right. I finally found a reference that says that ActiveX controls, which are employed by the "Command Toolbox" and which use the embedded object scheme "=EMBED()", are not available on Mac versions of Excel. It seems that all of these embedded objects need to be changed to "Form" buttons, check boxes, and option buttons. I've put together a short section of code to switch option buttons on and off. I am having trouble switching just one button off. When I set the button to xlOff, all of the buttons turn off. The method seems to work fine if I use a check box. What I would like to do in the end with the option buttons is put out a set of discrete choices with an option button next to each. When a particular option button is selected I want to turn all the other ones off.

Sub TestButton2_Click()
Dim myButton1 As OptionButton
Set myButton1 = ActiveSheet.OptionButtons(Application.Caller)

If myButton1.Value = xlOn Then
Call MsgBox("Checked")
ElseIf myButton1.Value = xlOff Then
Call MsgBox("Unchecked")
Else
Call MsgBox("Neither")
End If
myButton1.Value = xlOff
End Sub

Sub TestButton3_Click()
Dim myButton3 As OptionButton
Set myButton3 = ActiveSheet.OptionButtons(Application.Caller)

If myButton3.Value = xlOn Then
Call MsgBox("Checked")
ElseIf myButton3.Value = xlOff Then
Call MsgBox("Unchecked")
Else
Call MsgBox("Neither")
End If
End Sub

When I select TestButton2, a message displays showing that TestButton2 is on, and then both buttons reset when I only want TestButton2 to reset.
Help!