1

I am trying to make another worksheet active when command button is clicked, but I'm staying within the same active workbook:

Sub Submit_Warranty()
    'Set warranty data worksheet as active page
    Sheets("Sheet2").Activate

    'Show the submit warranty user form
    Warranty_Input.Show
End Sub

I keep getting "subscript out of range" error. Any ideas?

pnuts
  • 58,317
  • 11
  • 87
  • 139
John
  • 371
  • 1
  • 4
  • 21

1 Answers1

3

If the code you posted is everything, then that error pretty much has to be from an invalid reference. So my guess would be that the actual displayed name is something like "Warranty_Data", while "Sheet2" is likely the VBA object name (maybe you're using them in reverse).

There are a lot of ways to select a worksheet, with various advantages and disadvantages. When it comes to selecting by name, the major gotcha to watch out for is that sheets actually have two names assigned, and you're employing both methods of selection in the code you posted. The one name is what's displayed in the sheet's workbook tab, the other name is internal to VBA. Here's a screenshot to demonstrate how to use both types of names.

enter image description here

u8it
  • 3,956
  • 1
  • 20
  • 33
  • pretty well explained :) hopefully microsoft will change the "name shown in tab" to something like "caption" in the future... ppl get so often confused by having 1 object with 2 different name-propertys – Dirk Reichel Nov 02 '15 at 14:21
  • Good job guessing worksheet name! Haha. Im just learning VBA, so I apologize for my ignorance. Seeing your example is perfect. This made the most sense. Many thanks!! – John Nov 02 '15 at 15:05