2

I'm trying to activate a certain sheet in my workbook but I still get the same

error 9 "Subscript out of range"

when trying to run it from the userform. When I use Debug fuction, it says that "materialWorksheet = Nothing"

Private Sub UserForm_Initialize()
    
    ' Activate excel sheet to store/extract data
        Set objWorkbook = Excel.workbooks("Aircraft_Final_Master_Design_Table_LITE")
        Set materialWorksheet = objWorkbook.Worksheets.item("Materials")
        materialWorksheet.Activate

What I am trying to get as a result is to be able to acquire the values from that Materials sheet and use it for my userform.

braX
  • 11,506
  • 5
  • 20
  • 33
Crocknyld Crock
  • 21
  • 3
  • 3
    Always safer to include the file extension when referencing an Excel file by its name. On some machines excluding the extension may cause your code to fail, depending on the user's settings. See https://stackoverflow.com/a/38748754/478884 – Tim Williams Jan 12 '23 at 22:21
  • @ScottCraner - there's no problem using `Item` here ? `? worksheets.Item("Sheet1").name` for example does not throw any error. – Tim Williams Jan 12 '23 at 22:24
  • Holy Crap @TimWilliams! Thank you so much! It was actually the extension that was missing and it works now! Thank you so much! – Crocknyld Crock Jan 12 '23 at 22:26
  • @TimWilliams you are probably correct and was thinking that it would not throw the error the OP is encountering even if it was an error. I missed the extension, which is probably why the error. – Scott Craner Jan 12 '23 at 22:26

0 Answers0