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

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

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!


--
YoungGuy
------------------------------------------------------------------------
YoungGuy's Profile: http://www.excelforum.com/member.php...o&userid=29654
View this thread: http://www.excelforum.com/showthread...hreadid=493663