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

Should `Worksheets("Data lookup_ports")` be `Worksheets("Data lookup ports")` or `Worksheets("Data_lookup_ports")` instead? The error suggest that the worksheet name is wrong — barrowc, May 11 '18 at 01:08

urdearboy · Accepted Answer · 2018-05-11T01:47:22.557

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

Fantastic! that fixed that issue, you were correct in thinking the worksheet was invisible, that resolved the issue. Although now I'm getting an unrelated issue regarding CurrentWin.Activate that says object required but at least its progress! Thanks again. — , May 11 '18 at 03:06
What are you trying to do with currentwin.activate? Just call the sheet to activate if that’s what your aim is. — urdearboy, May 11 '18 at 03:08
It's currently my failed attempt at having my VBA script open instead of my spreadsheet. Worked previously but has since broken.. like i said, noob at VBA. — , May 11 '18 at 03:19
Yeah I should be okay with that, the bigger issue was the one you resolved. — , May 11 '18 at 03:49

VBA - Run-time error '1004'

1 Answers1

Linked