I needed to put a Checkbox on a Worksheet. The standard Checkbox options in Excel FormControl and ActiveX Control were too small. I found a work-around using the following macro attached to an ActiveX Label that was formatted as Wingdings font. The macro basically changes the character from a empty box Wingdings Chr(168) to a checked box Wingdings Chr(254) when the user clicks on the label. That part is solved. See below for my question/problem.
Private Sub Label1_Click()
If Label1.Caption = Chr(254) Then
Label1.Caption = Chr(168)
Else
Label1.Caption = Chr(254)
End If
End Sub
My issue - I have a separate macro that will copy data from a response worksheet (ResponseWst) into my master workbook (SummaryWst). I need to make the checkbox status on the ResponseWst, whether it is Chr(168) or Chr(254), copy into the SummaryWst as well.
I've tried this:
If ResponseWst.Shapes("Label1").Caption = Chr(254) Then
SummaryWst.Shapes(Label1).Caption = Chr(254)
Else
SummaryWst.Shapes("Label1").Caption = Chr(168)
End If
But I get this error on an ErrorHandler that I have included in the macro.
"An error has occurred. Error number =438. Error Description=Object doesn't support this property or method."
Bookmarks