VBA - 运行时错误“1004”

debugcn 发表于 Dev

用户9773487

有点上下文，这是我第一次在 excel 中使用 VBA 进行编程，我正在尝试创建一个填写电子表格的表单。

我目前收到错误消息：“运行时错误‘1004’：对象‘_Global’的方法‘工作表’失败

我在网上搜索并尝试了各种解决方案，但我认为基本上归结为我缺乏理解。

Private Sub CommandButton1_Click()
'When pressing save, save values in spreedsheet locations
ActiveSheet.range("c8").Value = ContactName.Value
ActiveSheet.range("b19").Value = ModelNumber.Value
ActiveSheet.range("d19").Value = SerialNumber.Value
ActiveSheet.range("g19").Value = IncidentNumber.Value
ActiveSheet.range("j19").Value = Description.Value
ActiveSheet.range("c7").Value = PortLocation.Value

'save file
ActiveWorkbook.SaveAs Filename:= _
  "D:\Users\611281\Downloads\Zebra\EmailMeToZebra.xlsx", FileFormat:=xlOpenXMLWorkbook, ReadOnlyRecommended:=False, CreateBackup:=False

End 'after pressing save, close down sheet.

End Sub


Private Sub UserForm_Initialize()

Me.PortLocation.List = Worksheets("Data lookup_ports").range("e3:e200").Value

Dim MyTempWkBk As Workbook
Dim MyCurrentWin As Window

Set MyCurrentWin = ActiveWindow
Set MyTempWkBk = Workbooks.Open("D:\Users\611281\Downloads\Zebra\GUI.xlsm")
MyCurrentWin.Activate      'Allows only a VERY brief flash of the opened workbook
MyTempWkBk.Windows.Visible = False 'Only necessary if you also need to prevent
                                    'the user from manually accessing the opened
                                    'workbook before it is closed.

'Operate on the new workbook, which is not visible to the user, then close it...

End Sub

Private Sub UserForm_Terminate()
    End 'when pressing x, close down window, do not save.
End Sub

我收到代码错误：

Me.PortLocation.List = Worksheets("Data lookup_ports").range("e3:e200").Value

这只是我试图从电子表格范围填充 ListBox

亲爱的男孩

您是否尝试过命名工作簿？这是假设有错误的行指的是本工作簿中的单元格范围。此外，请务必检查您所指的工作表名称是否正确拼写（如果拼写正确，还要检查是否有滞后空格。）此外，工作表是否隐藏？如果是这样，这可能需要在调用工作表之前添加。

wb.sheets("Data lookup_ports").Visible = True

同时可以尝试下面的编辑。

Private Sub UserForm_Initialize()

Dim MyTempWkBk As Workbook
Dim MyCurrentWin As Window
Dim WB as Workbook
   Set WB = ThisWorkBook
wb.sheets("Data lookup_ports").Visible = True 

Me.PortLocation.List = WB.Sheets("Data lookup_ports").Range("E3:E200").Value



Set MyCurrentWin = ActiveWindow
Set MyTempWkBk = Workbooks.Open("D:\Users\611281\Downloads\Zebra\GUI.xlsm")
MyCurrentWin.Activate   
MyTempWkBk.Windows.Visible = False 'Only necessary if you also need to prevent
                                'the user from manually accessing the opened
                                'workbook before it is closed.

'Operate on the new workbook, which is not visible to the user, then close it...

End Sub

Private Sub UserForm_Terminate()
    End 'when pressing x, close down window, do not save.
End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。