vba 运行时错误“1004”：对象“_worksheet”的方法“范围”失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23626433/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Run-time error '1004': Method 'Range' of object '_worksheet' failed
提问by Mustika
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.
但是，如果我只是打开工作表并单击复选框，则会出现运行时错误“1004”：对象“_worksheet”的方法“范围”失败错误。
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:
对我来说奇怪的是，如果我首先按下模块中包含以下代码的按钮，我不会再收到错误消息，即使它与我放入 Workbook_open 事件的代码相同：
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!
感谢您的帮助，我是VBA新手！
采纳答案by z??
You need to declare your global variables as Publicotherwise the Workbook_Openwill create and work on his own variables since they are out of his scope
您需要声明您的全局变量，Public否则Workbook_Open将创建并处理他自己的变量，因为它们超出了他的范围
Public ws1 As Worksheet
Public ws2 As Worksheet
Public comment As String
Public rown As Integer
回答by brettdj
You should just use ws1directly
你应该只使用ws1直接
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
