Make worksheet invisible at end of macro (97)

Topic

New Reply

Hi there

In my attached file I want to make the worksheet called Template visible at the beginning and invisible at the final step of the createsheets Macro.

Alternatively it could be moved to the end of all worksheets as the final step of the macro.

I need access to the Template for setting up, but need it hidden from users.
Either way I would like it to be away from obvious view to discourage tampering.

Help / advise would be greatly appreciated.

Regards

Kerry

Reply | Quote

Replies

Add this line where you want to make it visible:

    Application.ScreenUpdating = False

and this line where you want to hide it:

    Application.ScreenUpdating = False

Reply | Quote

Can you give me a little more to go on?

I already have that line in the macro and I am not sure how to include it to acheive the desired effect.

Thanks

Reply | Quote

Sorry, I copied the wrong line to paste into the message. Those two lines should have read:

    Application.Visible = False
and
    Application.Visible = True

It was too late last night.

Reply | Quote

Hi Legare,
The line:

Application.ScreenUpdating = False

doesn’t make the worksheet invisible – it just stops Excel from updating the screen.

What I think is needed in this case is to hide the Template worksheet at the end of the process, and you do that with the worksheet’s Visible Property. To hide the sheet, set it to xlSheetVeryHidden. The sheet can’t even then be found via the Format/Sheet/Unhide command. To make the sheet visible again, you undo the hiding by setting the Visible Property to xlSheetVisible.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Yep that worked.

What I have done is to slip in Sheets(“Template”).Visible = False at the the end of the routine. I can then get it back when I want to by using the format sheet unhide method.

This is exactly what I wanted to do.

Thanks team!

Reply | Quote

Hi

You could relpace the line in your code that says

Worksheets("Template").Protect Password:="MyPassword"

with the following:

With Worksheets("Template")
    .Protect Password:="MyPassword"
    .Visible = xlVeryHidden
End With

In this way the “Template” worksheet is hidden at all times.

Leigh

Reply | Quote