Run-time error '1004': Method 'Range' of object '_worksheet' failed

1
votes

I am trying to write to a cell when a checkbox is marked using this sub

Sub CheckBox7_Click()
If ws1.Shapes("Check Box 7").OLEFormat.Object.Value = 1 Then
ws2.Range(comment).Offset(0, 2).Value = "1"
Else
ws2.Range(comment).Offset(0, 2).Value = "0"
End If
End Sub

But if I just open the sheet and click the checkbox I get the Run-time error '1004': Method 'Range' of object '_worksheet' failed error.

I defined the variables at the top of the module:

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim comment As String
Dim rown As Integer

And I set the variables when the workbook opens:

Private Sub Workbook_Open()
rown = 3
comment = "F" & rown
Set ws1 = ThisWorkbook.Sheets("Rating test")
Set ws2 = ThisWorkbook.Sheets("Comments test")
End Sub

What is strange to me, if I first press a button with the following code in the module, I do not get the error anymore, even though it is the same code I put into the Workbook_open event:

Sub First_Comment()
Set ws1 = ThisWorkbook.Sheets("Rating test")
Set ws2 = ThisWorkbook.Sheets("Comments test")

    rown = 3
    comment = "F" & rown

End Sub

Thanks for the help, I am a VBA novice!

2
vbaexcel
Where did you define Workbook_open? – z̫͋
Are you sure the Workbook_Open Sub runs? If it does, it has to be in "ThisWorkbook" (under "Microsoft Excel Objects") and all other code has to be there too. – EngJon
Yes the WOrkbook_Open is defined in "ThisWorkbook" (under "Microsoft Excel Objects") The other Code is in the Module1 in "Modules" under the same VBA Project. I am also sure the Workbook_Open Sub runs because when I add "msgbox comment" to the workbook_open, it shows me a msgbox with "F3" – Mustika

2 Answers

1
votes

You need to declare your global variables as Public otherwise the Workbook_Open will create and work on his own variables since they are out of his scope

Public ws1 As Worksheet
Public ws2 As Worksheet
Public comment As String
Public rown As Integer
0
votes

You should just use ws1directly

Sub CheckBox7_Click()
Set ws1 = ThisWorkbook.Sheets("Rating test")
If ws1.Shapes("Check Box 7").OLEFormat.Object.Value = 1 Then
ws2.Range(comment).Offset(0, 2).Value = "1"
Else
ws2.Range(comment).Offset(0, 2).Value = "0"
End If
End Sub