运行时错误“1004”：对象“_worksheet”的方法“range”失败

Question

当使用此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

但是，如果我只是打开工作表并单击复选框，我会得到运行时错误'1004':object'_worksheet'的方法'range'失败错误。

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

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

令我感到奇怪的是，如果我在模块中首次按下一个带有以下代码的按钮，我不会再得到错误，即使它是我放入Workbook_open事件中的代码：

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

    rown = 3
    comment = "F" & rown

End Sub

谢谢你的帮助，我是一个VBA新手！

Answer 1

您需要将全局变量声明为public，否则workbook_open将创建并处理自己的变量，因为这些变量超出了他的范围

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