Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Make worksheet invisible at end of macro (97)

    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

    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make worksheet invisible at end of macro (97)

    Add this line where you want to make it visible:

    <pre> Application.ScreenUpdating = False
    </pre>


    and this line where you want to hide it:

    <pre> Application.ScreenUpdating = False
    </pre>

    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Make worksheet invisible at end of macro (97)

    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

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Make worksheet invisible at end of macro (97)

    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
    [MS MVP - Word]

  5. #5
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Make worksheet invisible at end of macro (97)

    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!

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make worksheet invisible at end of macro (97)

    Hi

    You could relpace the line in your code that says

    <pre>Worksheets("Template").Protect Password:="MyPassword"</pre>

    with the following:
    <pre>With Worksheets("Template")
    .Protect Password:="MyPassword"
    .Visible = xlVeryHidden
    End With</pre>

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

    Leigh

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make worksheet invisible at end of macro (97)

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

    <pre> Application.Visible = False
    and
    Application.Visible = True
    </pre>


    It was too late last night.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •