Error message making sheets visible after using xlVeryHidden

[alexisb]

I started this thread in the Office forum since it had to do with Excel but I was told that it should be posted in this VBA forum.

I wrote code to hide sheets in a workbook using xlVeryHidden until a password is entered, when I unhide certain sheets.

Dim Ans As String
ActiveWorkbook.Unprotect ("12345")
Ans = InputBox("Please input your password")
If Ans = "12345" Then
Sheets("Title Sheet").Visible = True
Sheets("Page2").Visible = True
Sheets("Page3").Visible = True
' Sheets("Page4").Visible = True
Sheets("Page5").Visible = True
' Sheets("Page6").Visible = True
Sheets("Page7").Visible = True
Else
MsgBox "Wrong password, try again."
End If

Two of the 7 sheets cause a run-time error - "exception has been thrown by the target of an invocation." The code works fine if I comment out the two lines above. I double-checked that these two pages have the same setup as the others - they're all password protected with the same password. Does anyone have any idea what this could be? I thought it might be a permission issue, which is why I confirmed the passwords.

Thanks,
Alexis

 

[GlennUK]

What version of Excel are you using? Are there any events coded in the problem sheets ( please check there aren't even any empty event routines )?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[alexisb]

Thanks, Glenn. I have Excel 2003. I'm not sure what you mean by problem sheets but I went through each sheet and made sure there were no empty event routines. Someone created this sheet that I am now modifying. I found a couple subs that had no code in them but otherwise nothing else. There is no VB code behind any of the sheets now. My VB code is all under ThisWorkbook. It's strange because the logic works fine for the other sheets; just these two sheets don't work.

I keep trying to find something different about these 2 sheets. Some (most) of the sheets have charts and graphs; some have buttons to display help files and some do not. But I don't see anything unusual or different about the two that don't work.

Thanks,
Alexis

 

[alexisb]

Does anyone have any suggestions on this issue?
Thanks.

 

[rmikesmith]

Alexis,

I don't have a magic bullet; can honestly say I've never encountered a run-time error like the one you report. Sounds like a problem beyond any usual code failure; possibly corruption. Along those lines, try recreating the worksheets causing the error then deleting the originals. Don't use Edit|Move or Copy Sheet... Instead, insert new worksheets and copy the relevant data, etc. from old to new. Save workbook and see what happens.


Regards,
Mike

 

[alexisb]

Thanks, Mike. I was just logging on to say that I found problem, after looking at this so many times. There was an extra space at the end of the sheet name on those two sheets. Once I removed the extra space, it worked like a charm.
Alexis

 

[rmikesmith]

Alexis,

Glad you found the problem, but I'm still scratching my head over the specific error. When I add an extra space to the sheet name then try to access via code, the error I receive is 'subscript out of range'.

One other suggestion: The Sheets (or Worksheets) Visible property actually takes an enumerated value, not True/False. The xlSheetVisibility enumerated type has the following constants available:
xlSheetHidden (value = 0)
xlSheetVeryHidden (value = 2)
xlSheetVisible (value = -1)

Hope this helps.
Mike

 

[alexisb]

Thanks again, Mike. Actually I changed my code to xlSheetVisible instead of True earlier today in hopes that this was the problem. But it didn't work until I removed the extra space from the sheet name.

Yes, it would have been nice to have a more meaningful error. I searched in this forum and other forums and my VB books, and just couldn't pin it down. I looked at this so many times until I finally saw the extra space a little while ago. It was a big relief as I had no idea what else to try.

Regards,
Alexis