Command for checking a checkbox in VBA?

[gumercindo]

Hi, simple question that I couldn't seem to find the answer for using the search command. I have a worksheet with a series of checkboxes. I'd like to run a macro that would check SOME of them and then uncheck them.

I tried recording the macro first, but the recording did not recognize me checking the checkbox. I also tried writing a VBA script that would add a space (" ") to that checkbox and that seemed to work but not optimal for adding the check makrs back in.

Any ideas?

Thanks!

btw, it's a form checkbox.

 

[gumercindo]

Hey tiger:

I have a worksheet with a series of checkboxes. Once a checkbox is "checked", the values will appear in the adjoining cell. If it's unchecked, no values (the worksheet performs some sort of lookup to another main sheet with all the data). Does this make sense to you?

So, I'm trying to create a macro that would uncheck certain boxes based on a certain scenario.

 

[Evagrius Ponticus]

okay - so I would suggest that RORYA'S solutions would work best with maybe an If...Else...statment.

So, for example,

If some condition = this then 
Sheets("worksheet1").Checkboxes("Checkbox17").Value = False
else
Sheets("worksheet1").Checkboxes("Checkbox17").Value = True
End if

 

[RoryA]

Or even:

Sheets("worksheet1").Checkboxes("Checkbox17").Value = (somecondition = this)

 

[gumercindo]

If it's a Forms checkbox, you can use:

Sheets("worksheet1").Checkboxes("Checkbox17").Value = False

using the relevant name.

Thanks.

I tried this in this format:

Sub mymacro()

Sheets("My worksheet name here").CheckBoxes("Checkbox17").Value = False


End Sub

And the error message I recieve says "Run-time error 1004", Unable to get the CheckBoxes property of the Worksheet class.

 

[RoryA]

I'd check the name of the check box - by default it would be:

Checkboxes("Check Box 17")

rather than:

Checkboxes("Checkbox17")

if it's a Forms checkbox.

 

[gumercindo]

I'd check the name of the check box - by default it would be:

Checkboxes("Check Box 17")

rather than:

Checkboxes("Checkbox17")

if it's a Forms checkbox.

That did it; thanks!

 

[working4change]

Hi - This is from the help file - it will explain Me far better than I can. You don't have to use Me though, you can just refer to the respective checkbox by its index number, i.e. checkbox1.value = this or checkbox2.value = that. You can also asign a variable(i) to the index number of the checkbox to cycle through all of your checkboxes.



The Me keyword behaves like an implicitly declared variable. It is automatically available to every procedure in a class module. When a class can have more than one instance, Me provides a way to refer to the specific instance of the class where the code is executing. Using Me is particularly useful for passing information about the currently executing instance of a class to a procedure in another module. For example, suppose you have the following procedure in a module:

Sub ChangeFormColor(FormName As Form)
FormName.BackColor = RGB(Rnd * 256, Rnd * 256, Rnd * 256)
End Sub

You can call this procedure and pass the current instance of the Form class as an argument using the following statement:

ChangeFormColor Me

Can you explain how to use the me.control? When I run the code, I get a compilation error reading:

Method or data member not found​