Checkbox as variable not defined vba

hellscar

New Member
Joined
Feb 20, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi everyone!

I have a sub that I want to use on various checkboxes on various sheets. So I set the checkbox as a variable called Cbx:
Here's what I've got:
VBA Code:
Sub Checkbox(Copysheet As Worksheet, ButtonSheet As Worksheet, Cbx As Checkbox)
    Application.ScreenUpdating = False
    If Workbooks("BCTF").ButtonSheet.Cbx.Value = 1 Then
        Copysheet.Select
        Range("A2:E" & Range("A" & Rows.Count).End(xlUp).Row).Select
        Selection.Copy
        With Sheets("tdq")
            .Select
            .Range("C" & Rows.Count).End(xlUp).Offset(1).Select
            .Paste
            .PasteSpecial xlPasteColumnWidths
        End With
        Workbooks("BCTF").ButtonSheet.Activate
    Else
        Reset
    End If
    Application.ScreenUpdating = True
End Sub

And when I call in another sub such as:
VBA Code:
Sub CheckBox11Off()
    Call Checkbox(Sheets("Off"), Sheets("MCO"), Checkbox("Check Box 2"))
End Sub

I get an error message for the line "Call Checkbox". In the version above the message being "Type mismatched". I've also tried putting in the variable as Checkboxes("Check Box 2") (Here message "sub or function not defined"), or Checkbox(Check Box 2) or Check box 2 (Here message "ByRef Argument type mismatched)... In the first Sub I've also tried writing "Cbx As Checkbox" and "Cbx As Checkboxes"
Does anyone know how to refer to the Checkbox so that it works??

Thanks in advance!

(P.S: Also in the first Sub
VBA Code:
 .Paste
 .PasteSpecial xlPasteColumnWidths
work but if I put in the next line
VBA Code:
. PasteSpecial xlPasteFormats
I get an error message "PasteSpecial Method of Worksheet Class Failed"
If someone knows the answer to this other problem I would be grateful as well!)
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,473
Office Version
  1. 2019
Platform
  1. Windows
Hi
welcome to forum

try following changes to your parameters & see if resolves your issues

VBA Code:
Sub Checkbox(ByVal Copysheet As Worksheet, ByVal ButtonSheet As Worksheet, ByVal Cbx As Object)

Dave
 

hellscar

New Member
Joined
Feb 20, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi
welcome to forum

try following changes to your parameters & see if resolves your issues

VBA Code:
Sub Checkbox(ByVal Copysheet As Worksheet, ByVal ButtonSheet As Worksheet, ByVal Cbx As Object)

Dave
Hi thanks!

In fact, I've realised I've been calling the function Checkbox in the variables and not the actual checkbox. So I've changed the name of the first sub to Chckbx (ByVal Copysheet As Worksheet, ByVal ButtonSheet As Worksheet, ByVal Cbx As Object) for less confusion and as you suggested.
Now the second sub is:
VBA Code:
Sub CheckBox11Off()
    Call Chckbx(Sheets("Off"), Sheets("MCT"), Sheets("MCT").[COLOR=rgb(0, 0, 0)]CheckBoxes("Check Box 2")[/COLOR])
End Sub
And it seems to not be a problem anymore.

However now the line
VBA Code:
If Workbooks("BCTF").ButtonSheet.Cbx.Value = 1 Then
tells me "Object doesn't support this property".
Is there a way to keep the properties of a checkbox?
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,473
Office Version
  1. 2019
Platform
  1. Windows
Hi,
you should only need the passed object

VBA Code:
If Cbx.Value = 1 Then

Dave
 

hellscar

New Member
Joined
Feb 20, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Yes thank you so much works perfectly now!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top