Showing Worksheets Problem

[Pacman52]

Hi all I'm completely lost with why I can't show some worksheets in a macro enabled workbook.

The right click tab options to show or hide worksheets is greyed out, using any sort VBA like Visable, xlVisable, -1 etc throws up the error object doesn't support this method nothing I'm doing seems to allow me to show the worksheets even though they are showing in the VBA project window tree as existing.

As far as I am aware I've blocked out any code references that I had in the modules that referred to the visibility of a sheet, I've removed both the worksheets and workbook protection and re-named the workbook using Save As (even though I know is a bit pointless)

Can anyone offer any solutions or things to check that I'm just not doing as its driving me nuts why something so simple is becoming so difficult and time consuming to the point I'm on the verge of just re-doing the whole workbook again from scratch (its not even an important workbook just something that was meant to make my life a bit easier)

Thanks for any replies offered.

Paul

 

[dmt32]

Hi,
is the workbook protected? If so, try unprotecting it & see if resolves your issue

Dave

 

[Pacman52]

Hi,
is the workbook protected? If so, try unprotecting it & see if resolves your issue

Dave

Hi Dave it was originally protected but I'd removed the protection on both the Workbook and the sheets but that didn't work.

I have literally just tried a brand new workbook with just 2 sheets, created a module with some simple code

Sheets("Sheet2").visable = False

and that didn't work either. I'm starting to think there may be an issue with the program possibly unless there are any other suggestions on thing to try?

Thanks Paul

 

[Pacman52]

Just an update I've done all the updates on Office and also run a repair as well but that hasn't made any difference. If I run some code to 'activate' a worksheet that works fine I just can't seem to hide or show sheets through code - it continually throws up "Object doesn't support this property or method' error

 

[sxhall]

It may not be, and it may be a regional thing, but 'visible' is spelled incorrectly in your code.

Try...

Sheets("Sheet2").Visible= False

 

[dmt32]

I have literally just tried a brand new workbook with just 2 sheets, created a module with some simple code

You have a Typo

Sheets("Sheet2").visable = False

Should be

Worksheets("Sheet2").Visible = False

Dave

 

[Pacman52]

Hi all thanks for the replies and apologies for the typo in my original post but just to confirm that the word Visible is spelt correctly in the actual code in the workbook although I did have to double check just in case I was being completely stupid

I've got a feeling the workbook is corrupted as I've looked through all the modules and code everywhere and simply can not find anything that is active and references the worksheets, I've also looked through all the Excel options but cant see anything here either.

What is leading me to believe the WB is corrupted is the fact the options to hide or show sheets is greyed out on the tabs and I cant event add a new tab by clicking the + button, I know the sheets exist as they are listed in the VBA project window.

I do have a backup of the WB although its not current so I could really do with at least being able to access the current 'Data' worksheet to be able to copy the most up to date stuff into a new workbook so if anyone know anyway to do this I'd be very grateful for a solution.

Thanks again for all the help and advice Paul

 

[Alex Blakenburg]

I know the sheets exist as they are listed in the VBA project window.

Do they by any chance show as having the same excel symbol as the ThisWorkbook node ?
That is indicative of a workbook being corrupt.

 

[Pacman52]

Do they by any chance show as having the same excel symbol as the ThisWorkbook node ?
That is indicative of a workbook being corrupt.

Hi Alex so sorry but I don't understand what you mean - could you explain a bit more please.

 

[Pacman52]

Ignore that sorry I've just worked it out. The symbol for ThisWorkbook is different than the sheets