Custom Checkbox control in Excel

[Jim]

Hello, I have a user who is asking if it is possible to create a custom
checkbox control for use inside of Excel. This new checkbox control is
essentially a checkbox control that has other graphics around the checkmark
other than a square.

For instance, he need to have a triangle that has a clickable checkmark
inside it. He needs a circle with a clickable checkmark inside as well as a
couple of other shapes that have a clickable checkmark inside (no square
around the checkbox). I've tried to put a regular checkbox on top of a
graphic but you have to deal with the square corners of the checkbox even if
it's set to transparent.

Anyone seen any code or otherwise on how to create a custom or add-in that
would do what I describe?

Thanks
Jim

[Dick Kusleika]

Jim

Create a circle (oval) from the Drawing Toolbar. Format the autoshape
(right click) and set the font to Wingdings (and the size and boldness to
suit) and set the alignment to Center for both horizontal and vertical.

Paste this code into a standard module

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

Right click on the autoshape and choose "assign macro" and assign
CheckCircle to it. Whenever you click the circle, a check mark should
appear and disappear alternately. You can also experiment with different
fonts and different numbers in the Chr$() function to find a check mark more
to your liking.

--
**** Kusleika
MVP-Excel
www.dailydoseofexcel.com
Jim wrote:
> Hello, I have a user who is asking if it is possible to create a
> custom checkbox control for use inside of Excel. This new checkbox
> control is essentially a checkbox control that has other graphics
> around the checkmark other than a square.
>
> For instance, he need to have a triangle that has a clickable
> checkmark inside it. He needs a circle with a clickable checkmark
> inside as well as a couple of other shapes that have a clickable
> checkmark inside (no square around the checkbox). I've tried to put
> a regular checkbox on top of a graphic but you have to deal with the
> square corners of the checkbox even if it's set to transparent.
>
> Anyone seen any code or otherwise on how to create a custom or add-in
> that would do what I describe?
>
> Thanks
> Jim

[Jim]

****, you are a genius. I insist you take the rest of the day off.
Just tell them I said so!


Thanks!

Jim



"**** Kusleika" <dkusleika@gmail.com> wrote in message
news:u$v4pwYQGHA.6008@TK2MSFTNGP10.phx.gbl...
> Jim
>
> Create a circle (oval) from the Drawing Toolbar. Format the autoshape
> (right click) and set the font to Wingdings (and the size and boldness to
> suit) and set the alignment to Center for both horizontal and vertical.
>
> Paste this code into a standard module
>
> 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
>
> Right click on the autoshape and choose "assign macro" and assign
> CheckCircle to it. Whenever you click the circle, a check mark should
> appear and disappear alternately. You can also experiment with different
> fonts and different numbers in the Chr$() function to find a check mark
> more to your liking.
>
> --
> **** Kusleika
> MVP-Excel
> www.dailydoseofexcel.com
> Jim wrote:
>> Hello, I have a user who is asking if it is possible to create a
>> custom checkbox control for use inside of Excel. This new checkbox
>> control is essentially a checkbox control that has other graphics
>> around the checkmark other than a square.
>>
>> For instance, he need to have a triangle that has a clickable
>> checkmark inside it. He needs a circle with a clickable checkmark
>> inside as well as a couple of other shapes that have a clickable
>> checkmark inside (no square around the checkbox). I've tried to put
>> a regular checkbox on top of a graphic but you have to deal with the
>> square corners of the checkbox even if it's set to transparent.
>>
>> Anyone seen any code or otherwise on how to create a custom or add-in
>> that would do what I describe?
>>
>> Thanks
>> Jim
>
>

[jvhilbs]

Addition features I added to the brilliant code above:

The list of features it now incorporates:
Change the color of the shape
Copy & Paste the shape without having to create an individual macro for each shape!!!
Link the shape to a cell!!!

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[jvhilbs]

Can also add

.Shadow.Transparency = 1

to use the cell's value & create your own custom formatted background

[jvhilbs]

Sorry not .Shadow, but .Fill (see code below)

I also tried to hide the cell's value (true or false) with editing the font color. Better is to control it with conditional formatting while formatting the cell's background color.

Please Login or Register  to view this content.

[arlu1201]

jvhilbs,

Welcome to the forum.

I have added code tags to your posts. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

[rodoramco]

Hey I came up with a simple solution for what I needed, I wanted to use custom images and this works for either transparent pictures or different pictures altogether, you'll know what I mean if you ever need to, believe me.

Anyways, I wanted to use a custom checkbox and have it work in the same way a checkbox works in excel, by entering either False or True inside a cell while checked or unchecked. So, I uploaded the custom checkbox image (a green circle with a white check-mark in the middle), then I uploaded it again, and set the transparency to like 60%, the two now look a bit different. I entered this code and applied the same macro to both overlapped images, the reason being, when the image is set to visible = false you cannot click it, hence you need to have the background image with transparency, below.

Please Login or Register  to view this content.