CODEWITHSUNDEEP
vbaexcel
Mustika
Mustika

Reputation: 191

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

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!

Upvotes: 1

Views: 34660

Answers (2)

brettdj
brettdj

Reputation: 55672

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

Upvotes: 0

z̫͋
z̫͋

Reputation: 1561

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

Upvotes: 1

Related Questions