Error - Method ‘Visible’ of object’_Worksheet’failed

[ABabeNChrist]

I am getting an error message when I go from print preview to normal view, then directly select a button that hides sheet.

Error message.JPG

Here is the code to hide

Code: Select all

    Worksheets("Grounds").Visible = xlSheetHidden
    UserForm8.Show

And then if I were to return to same sheet using

Code: Select all

    Worksheets("Grounds").Visible = xlSheetVisible
    Worksheets("Grounds").Select
    ActiveSheet.Range("A8").Select
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                        Password:="", UserInterfaceOnly:=True
    Unload UserForm8

I would then receive error message.
I also tried selecting a cell upon hiding, thinking maybe it needs to tab out

Code: Select all

    ActiveSheet.Range("A8").Select

Any thoughts what I might try

[HansV]

I'm confused by your description. When exactly does the error occur - when you try to hide the sheet or when you try to unhide it?

[ABabeNChrist]

I apologize, the error message appears when I unhide sheets.

[HansV]

I think we'll have to see (a copy of) the workbook to determine the cause - the code itself looks OK.

[ABabeNChrist]

I thought you might say that
I use this same code in another workbook and yes its works great.
This workbook in question is quite large, do you have any suggestion where I might look

[HansV]

Sorry, no.

[ABabeNChrist]

Hi Hans
I was able to condense my workbook small enough for viewing

Sample1.xlsm

[HansV]

Does the problem still occur for you in the workbook that you attached? I can't reproduce the error.

[ABabeNChrist]

Yes it does

[HansV]

Does it persist if you quit and restart Excel?
Does it persist if you quit and restart your PC?

[ABabeNChrist]

When I first noticed the problem I then closed the excel workbook then reopened, the error was still there. I then restarted my PC, the error was still there. I then open a different workbook that is quite similar to this workbook that uses the type of coding, and everything worked great.
The workbook works great up until I use the print preview

[HansV]

It doesn't help if you withhold relevant information - you didn't tell us until just now that the problem occurs after a print preview! Next time, please provide all relevant information right at the start!

When I print preview, I get the error too, and then Excel crashes.

Your workbook appears to be corrupt. When I open it with the Open and Repair option, I get this report:

x86.png

But this doesn't solve the problem. It's probably best to recreate the workbook.

[ABabeNChrist]

Thank you Hans
I mentioned at the beginning of this thread when it occurs, I guess I needed to be a little more precise. It’s funny when you know when it occurs you sometimes think so does everyone else.
Now you mentioned it’s probably best to recreate the workbook. Does this mean each page piece by piece? Because that’s a scary thought, it’s pretty big.

[HansV]

Sorry, you did mention print preview - I overlooked that.

I tried converting the workboo to an Excel 97-2003 workbook (.xls) and back, and although some functionality was lost, the problem remained.

Since I can't determine the cause of the problem, I don't know of another way to get rid of it than by reconstructing the workbook from scratch...

[ABabeNChrist]

I was afraid of that.
I thank you Hans for your assistance.
Back to the drawing board.
Is there any tips you could give me to look out for, that could help prevent this from happening again

[HansV]

I don't know what causes workbooks to become corrupt; it just seems to happen occasionally.

[ABabeNChrist]

Hi Hans
Let me ask a hypothetical question. Since the error only seems to occur when using code to unhide a sheet, only after Print preview has been used. What if the sheets were not hidden at all and were left visible. Would this workbook still be a concern?
Just curious

[HansV]

It was easy to test that - I commented out the lines in your code that hide/unhide the Grounds worksheet. Even after print previewing, clicking the buttons didn't cause an error message to be displayed or Excel to crash. So it appears you can avoid the error by not hiding and unhiding the sheet; unfortunately that still doesn't give me a clue to the reason for the error.

[ABabeNChrist]

I’m not sure how the corruption came about, all I can think is I entered some incorrect coding (I do this quite frequently) and it caused and error that progressed to something more.

[HansV]

I don't think the VBA code is the problem - I ran Rob Bovey's code cleaner, and it didn't make any difference. There must be some kind of hidden corruption in the workbook itself.