MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

runtime error 1004 fix

  Asked By: Jodon    Date: Mar 26    Category: MS Office    Views: 1109

I created just a simple macro that takes the user to another tab in my
spreadsheet. The macro works fine normally, but when I protect the
sheet and try to use the macro it does not work. I get a message
saying "runtime error 1004" "Select Method of worksheet class failed"
All my other macros on the sheet work fine when I protect the sheet.
Any ideas on what is causing this problem or how to fix it? Thank you
for the help. Here is the code:

Private Sub CommandButton2_Click()
'Takes user to instructions tab
End Sub



3 Answers Found

Answer #1    Answered By: Faiza Mian     Answered On: Mar 26

When you hide a sheet using hidden or very hidden and then you want
to use a macro to view the sheet you will need to tell the procedure
to un-hide the sheet first, to do this use the following code

Private Sub CommandButton1_Click()
Sheets("instructions").Visible = True

End Sub

Then put the following code in the instructions sheet

Sub worksheet_deactivate()
Sheets("instructions").Visible = xlSheetVeryHidden

End Sub

And when you click off the sheet it will hide it again.
If you passworded the sheet you would have to do the same for the
password as for the hidden sheet.

Answer #2    Answered By: Felix Gray     Answered On: Mar 26

Thank you for the help, but I never hid the sheet, I just protected
it. The macro works when the sheet is unprotected, but will not
work when protected. I have other macros that go to other tabs that
still work when I protect it, just not this one. Any further

Answer #3    Answered By: Sultana Tabassum     Answered On: Mar 26

I wonder if the other sheets have one or more unprotected cells and this sheet
has all its cells protected? Could you unprotect a blank cell so this cell
can have the focus when the sheet is selected?

Didn't find what you were looking for? Find more on runtime error 1004 fix Or get search suggestion and latest updates.