Command button to create checkbox on Excel Sheet

[GalaxyFlip4]

Hi all,

I am attempting to create a checkbox on an Excel worksheet when a command button is pressed and clear the checkbox caption.

I get an error message: "Compile Error - Invalid or unqualified reference" and the .Left is highlighted in this line: sh.CheckBoxes.Add(.Left + 1, .Top + 15, .Width - 1, .Height - 1).Select

Below is the code:
**********************************************
Private Sub CommandButton1_Click()

Dim lastRow As Double
Dim sh As Worksheet


Set sh = Sheets("data")
lastRow = sh.Range("A" & Rows.Count).End(xlUp).Row + 1

With sh.Range("E" & lastRow)
End With



sh.CheckBoxes.Add(.Left + 1, .Top + 15, .Width - 1, .Height - 1).Select
With Selection
.Caption = ""
'ChkBox.Caption = ""
End With

End Sub
*********************************************
Any ideas on how to fix?

Thank you all very much and be well.

Dion
<>

 

[Domenic]

You'll need to move that part of the code that adds your checkbox within With sh.Range("E" & lastRow)/End With.

By the way, you can avoid selecting your checkbox before setting the Caption.

Also, lastRow can be declared as Long.

Also, you should qualify Rows.Count with .RowsCount.

So your macro can be re-written as follows...

Private Sub CommandButton1_Click()

    Dim lastRow As Long
    Dim sh As Worksheet
  
    Set sh = Sheets("data")
  
    With sh
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    End With
  
    With sh.Range("E" & lastRow)
        With sh.CheckBoxes.Add(.Left + 1, .Top + 15, .Width - 1, .Height - 1)
            .Caption = ""
        End With
    End With

End Sub

Hope this helps!

 

[GalaxyFlip4]

You'll need to move that part of the code that adds your checkbox within With sh.Range("E" & lastRow)/End With.

By the way, you can avoid selecting your checkbox before setting the Caption.

Also, lastRow can be declared as Long.

Also, you should qualify Rows.Count with .RowsCount.

So your macro can be re-written as follows...

Private Sub CommandButton1_Click()

    Dim lastRow As Long
    Dim sh As Worksheet
 
    Set sh = Sheets("data")
 
    With sh
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    End With
 
    With sh.Range("E" & lastRow)
        With sh.CheckBoxes.Add(.Left + 1, .Top + 15, .Width - 1, .Height - 1)
            .Caption = ""
        End With
    End With

End Sub

Hope this helps!

Thank you Dominic!

Very helpful and have a great day.

Dion
<>

 

[Domenic]

You're very welcome, I'm glad I could help.

Cheers!