VBA Checkbox

[Hiport]

Hi, the below code removes the tick from checkbox when workbook opens, how can i add this to a module so i can call this from a module sub rather than Thisworkbook?

ThisWorkbook.Worksheets("Tasks").CheckBox1.Value = False

 

[jeffreybrown]

Maybe you can try...

Sub RemoveCheck()
    Worksheets("Tasks").CheckBox1.Value = False
End Sub

 

[Hiport]

Maybe you can try...

Sub RemoveCheck()
    Worksheets("Tasks").CheckBox1.Value = False
End Sub

Ok i did this and got an error "Method or data member now found CheckBox1

Sub Test
Dim CheckBox1  As Object
Dim shtTask As Worksheet

Set shtTask = ThisWorkbook.Worksheets("Tasks")
shtTask.CheckBox1.Value = False

End Sub

 

[Rasm]

Are you using a form control or an activeX control - I suggest you use an ActiveX control. dont dim the checkbox - no need

 

[Hiport]

Are you using a form control or an activeX control - I suggest you use an ActiveX control. dont dim the checkbox - no need

Active x control

 

[jeffreybrown]

If you are using a form checkbox...

Sub Test()
    Dim shtTask As Worksheet
    Set shtTask = Worksheets("Tasks")
    shtTask.CheckBoxes("Check box 1").Value = False
End Sub

 

[Hiport]

If you are using a form checkbox...

Sub Test()
    Dim shtTask As Worksheet
    Set shtTask = Worksheets("Tasks")
    shtTask.CheckBoxes("Check box 1").Value = False
End Sub

I'm am using active x, your code in #2 works, but why doesnt it work for my method of setting worksheet?

 

[shg]

As Jeffrey says, the last method he shows is for Forms toolbar checkboxes.

Why not use the codename for the sheet, and get the benefit of IntelliSense?

Sheet1.CheckBox1.Value = False

Change Sheet1 to the codename for the sheet.

 

[Hiport]

As Jeffrey says, the last method he shows is for Forms toolbar checkboxes.

Why not use the codename for the sheet, and get the benefit of IntelliSense?

Sheet1.CheckBox1.Value = False

Change Sheet1 to the codename for the sheet.

This Works


Worksheets("Tasks").CheckBox1.Value = False
</pre>
But this does not work


Dim shtTask As Worksheet

Set shtTask = ThisWorkbook.Worksheets("Tasks")
shtTask.CheckBox1.Value = False
</pre>

 

[shg]

Right, it doesn't.

Checkbox1 only exists in the context of a particular worksheet. I believe it is the case when you pick an object from the worksheets collection (set wks = worksheets(...)), you create a container that gets searched for the checkbox object at runtime.

When you set a worksheet object variable, that doesn't happen.

That's as well as I can explain it.