Checkboxes and Macros

[Exzel]

I am trying to understand Checkboxes and how to use them with Macros.

To use a "Check Box", you need to define it?

If so, how?


What does this command mean:

CheckBoxes("Check Box 17")


I tried to play with this command and received the following error:

Runtime error 1004
Unable to get the Checkboxes property of the worksheet class



Thanks!


Exzel

 

[philR]

You don't need the brackets and everything. First, I would rename you check box.

Make sure you are in design mode, right click the check box you are interested in and select properties. Then rename it by changing the name property. I tend to use the same 3 letter prefix for similar objects, so I would call it something like chkOne or chkReply or whatever. This is just a tip 'cos you sound quite new to this, anyway it works well for me.

Next, the code to use it would look something like this:

Sub test()
If chkOne.Value = True Then
mymsg = "Box is ticked"
Else
mymsg = "Box is not ticked"
End If
MsgBox (mymsg)
End Sub

That's if it is going into the sheet module. If you are creating a general module, it is always safest to use the sheet name as well, so the line above would change to:

if Sheets("Sheet1").chkOne.Value = True then

Hope this helps, if I have patronised I am sorry, but it's very hard to tell what level you are at.

Phil.

 

[Exzel]

Thank you for your response.

I do not have a lot of experience in working with "Checkboxes" so be patient with me.
Any basic input is greatly appreciated!

Now, A very basic question, what is the "design mode" that you are referring to?

I am trying to work with a workbook that already has "Checkboxes" and then add additional ones.


Exzel

 

[phantom1975]

If you RIGHT CLICK anywhere on the toolbar, you will have the ability to add/remove toolbars. Add the toolbar called CONTROL TOOLBOX. Once this toolbar is added, the leftmost button in the toolbar is the DESIGN MODE toggle button. While in design mode, you are able to click on a checkbox and edit it's ability. When you are NOT in design mode, clicking on the checkbox makes is checked/unchecked.

 

[Exzel]

Ok. I have been able to find the design mode button and I am in that mode.

The current spreadsheet that I am reviewing has buttons.

1)When I right click, I do not get the "Property s" of the button. (The menu choices are cut, copy, paste(grayed), exit edit text,grouping, order, assign macro,format control)

2)How do I find out the the correct check box of the button?


Exzel

 

[Mudface]

It sounds like you've added a checkbox from the Forms menu, rather than the Control menu.

Add one from the Control toolbar (bring it up like phantom suggested) then right-click on it and you'll have Properties in there you can modify, including the Name. If you double-click on the checkbox whilst in Design Mode, you'll create the default event sub for the checkbox (the Click event) and you can put your code in there. Note it also has other events (click on the right-hand dropdown while in the VB editor to see them) allowing you to 'play around' when certain things happen to it.

 

[Exzel]

You are right!

Then my question is what is the difference between the checkbox from the "Forms menu" then from the "Control menu"?

When using the Forms menu checkbox, how do you determine the correct Checkbox value? (to use in a Macro)


Exzel

 

[Exzel]

My question is what is the difference between the checkbox from the "Forms menu" then from the "Control menu"?

When using the Forms menu checkbox, how do you determine the correct Checkbox value? (to use in a Macro)


Exzel

 

[ChrisM]

I believe the difference is just where they're intended to be used. A form checkbox gets placed on a user form, a normal checkbox gets placed directly into/onto your spreadsheet.

use checkbox.value to find out what is selected, it returns either TRUE or FALSE, so you could do something like:

if checkbox.value = true then
'your code here
end if

 

[phantom1975]

From what I can tell, the FORMS CheckBox only has the ability to run a macro that is in the MODULES folder in the Visual Basic Editor. The CONTROL CheckBox has code that is placed within the Worksheet Module. Because of this, you can write your code directly into the worksheet module.

Basically, if you use the FORMS CheckBox, you can write ONE code that can be used over and over again.

If you use the CONTROL CheckBox, the code is limited to JUST the worksheet.

Personally, I would use the CONTROL CheckBox because you will usually limit your code to within that sheet only. Even if you did want to use the same code within other sheets, it's really easy to do it.

I find that by keeping my MODULES folder as clean as possible, I can better see what is going on within my procedures.

I hope that this helps.