Hello,
I had a user ask me if there was a way to create a custom checkbox control
for use in Excel. Dick Kusleika was kind enough to suggest this macro and
it worked with great success.

Sub CheckCircle()

With Sheet1.Shapes(1).TextFrame
If .Characters.Text = Chr$(252) Then
.Characters.Text = ""
Else
.Characters.Text = Chr$(252)
End If
End With

End Sub

The user was very happy and with him not knowing anything about VBA, he
asked if I could modify a spreadsheet for him. Not thinking, I accepted.
Turns out it is actually three specification spreadsheets that each have a
minimum of 100 of such custom checkbox controls.

You can see that this would be beyond tedious to have to make a macro for
each control in the above fashion.

Is there a way to make this ONE macro respond to which ever custom control
that calls it? How can you interrogate what control called the macro?
something like:

iCallingControl=CallingControl(item)
Sheet1.Shapes(iCallingControl).TextFrame

Sad I know but does my problem make sense? Is there a spiffy solution?

Any help would be appreciated.

Jim

Hi Jim,

Is there a way to make this ONE macro respond to which ever custom control
that calls it? How can you interrogate what control called the macro?
something like:

I have an article about this on my site:

http://www.jkp-ads.com/articles/ControlHandler00.htm

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Jan, I tried your instructions and I must be doing something wrong.
When I follow your instructions and enter:

Option Explicit

Private WithEvents mobtOption As MSForms.OptionButton

Nothing works beyond that. If I backspace over the MSForms.OptionButton
part and press the space bar after the AS word, I do not see MSForms in the
dropdown.

What do I have to do to get that?

Jim




"Jan Karel Pieterse" wrote in message
...
Hi Jim,

Is there a way to make this ONE macro respond to which ever custom
control
that calls it? How can you interrogate what control called the macro?
something like:

I have an article about this on my site:

http://www.jkp-ads.com/articles/ControlHandler00.htm

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

I don't seem to have Microsoft Forms 2.0 library listed in my references
list.
Where does one get the library?

"Jan Karel Pieterse" wrote in message
...
Hi Jim,

Is there a way to make this ONE macro respond to which ever custom
control
that calls it? How can you interrogate what control called the macro?
something like:

I have an article about this on my site:

http://www.jkp-ads.com/articles/ControlHandler00.htm

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Hi Jim,

I don't seem to have Microsoft Forms 2.0 library listed in my references
list.
Where does one get the library?

It should be there, it is a default part of Office. Just insert a userform
and remove it again, that will set the reference automatically.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Jan, thanks, I got the MSForms object but I have another problem.
Your example deals with the OptionButton as he
Public Property Set Control(obtNew As MSForms.OptionButton)
Set mobtOption = obtNew
End Property

Mine deals with a Excel shapes (triangle, square, circles, etc) and uses the
text option to add a checkmark. When I type "MSFORMS." I can see your
optionbutton in the dropdown but there is no "shape" option for me to get.
How would I make your example work with a excel shapes?

Thanks

Jim



"Jan Karel Pieterse" wrote in message
...
Hi Jim,

I don't seem to have Microsoft Forms 2.0 library listed in my references
list.
Where does one get the library?

It should be there, it is a default part of Office. Just insert a userform
and remove it again, that will set the reference automatically.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Hi Jim,

Mine deals with a Excel shapes (triangle, square, circles, etc) and uses the
text option to add a checkmark. When I type "MSFORMS." I can see your
optionbutton in the dropdown but there is no "shape" option for me to get.
How would I make your example work with a excel shapes?

I am inclined to think you cannot. The objects you can create events for are
the ones listed in the dropdown. If your object isn't shown in the list (or as
a member of one of the main entries in that list), it cannot be tied to an
event class.

More generally speaking: only objects that expose their events in the code
window of the worksheet you have placed them on can be automated this way.

You can see what objects can have event code by right clicking the sheet tab,
and selecting "View Code". Then if you click on the lefthand dropdown arrow at
the top of the code editor window, you will see a list of all objects that can
have event code. Selecting any of them will write the default event stub in the
code window and now the righthand dropdown will list the available events. Note
that not all of these events may be available with my technique, MS omitted a
couple of them.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Although I didn't get the class version to work as you suggested, I solved
my problem this way. Would have preferred to go the class route but had the
issues I described.

Thanks for the help.



Sub CheckShape()
Set shp = ActiveSheet.Shapes(Application.Caller)

With ActiveSheet.Shapes(Application.Caller).TextFrame

If .Characters.Text = Chr$(162) Then

.Characters.Text = ""

Else

.Characters.Text = Chr$(162)

End If

End With

End Sub





"Jim" <. wrote in message ...
Jan, thanks, I got the MSForms object but I have another problem.
Your example deals with the OptionButton as he
Public Property Set Control(obtNew As MSForms.OptionButton)
Set mobtOption = obtNew
End Property

Mine deals with a Excel shapes (triangle, square, circles, etc) and uses
the text option to add a checkmark. When I type "MSFORMS." I can see your
optionbutton in the dropdown but there is no "shape" option for me to get.
How would I make your example work with a excel shapes?

Thanks

Jim



"Jan Karel Pieterse" wrote in message
...
Hi Jim,

I don't seem to have Microsoft Forms 2.0 library listed in my references
list.
Where does one get the library?

It should be there, it is a default part of Office. Just insert a
userform
and remove it again, that will set the reference automatically.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com