VBA - Run-time error '1004'

Question

A bit of context, this is my first time programming using VBA in excel, and I'm trying to create a form that fills out a spreadsheet.

I'm currently getting the error message: "Run-time error '1004': Method 'Worksheets'of object '_Global' failed

I've searched online and have tried various solutions, but I think basically it comes down to a lack of understanding on my part.

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

I'm getting the error on the code:

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

Which is just me trying to populate a ListBox from a spreadsheet range

Answer 1

Have you tried naming the workbook? This is assuming the line with the error is referring to a cell range in thisworkbook. Also, make sure to check the name of the sheet you are referring to for exact spelling (if spelling appears correct, also check for lagging spaces.) Also, is the worksheet hidden? If so, this may need to be added before calling the sheet.

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

Can try the below edit in the meantime.

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