Consulting

Results 1 to 8 of 8

Thread: Solved: VBA to to unhide sheet

  1. #1
    VBAX Regular
    Joined
    Sep 2004
    Posts
    61
    Location

    Solved: VBA to to unhide sheet

    Can someone please assist.

    I have a workbook with 8 sheets. (Main, Monday,Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday)

    All sheets except Main are hidden. In cell B2 of Main is a date. I want to open a sheet that corresponds to the day of the date in cell B2, and hide all the rest, and then paste the date into G1 of the visible worksheet.

    To clarify, if Main B2 was Saturday 4 September 2010, Main would get hidden and Saturday would be made visible and the date from B2 would then be pasted into cell G1 of the visible (Saturday) worksheet.



    Thanks in advance
    Koala

  2. #2
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    [vba]Sub TransferDate()
    With Worksheets(Format(Worksheets("Main").Range("B2").Value, "dddd"))
    .Visible = xlSheetVisible
    .Range("G1") = Worksheets("Main").Range("B2").Value
    .Select
    End With

    Worksheets("Main").Visible = xlSheetHidden
    End Sub[/vba]
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  3. #3
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    You might want to use this in your thisworkbook module so that you only display the Main sheet on open.

    [VBA]Private Sub Workbook_Open()
    Dim Sh As Worksheet
    Sheets("Main").Visible = True
    For Each Sh In Sheets
    If Sh.Name <> "Main" Then
    Sh.Visible = xlVeryHidden
    End If
    Next Sh
    End Sub[/VBA]
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  4. #4
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    LOL! I probably shouldn't have, but I took it for granted that he'd already got that far. Thanks for picking up after me here, Simon.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  5. #5
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Quote Originally Posted by Ken Puls
    LOL! I probably shouldn't have, but I took it for granted that he'd already got that far. Thanks for picking up after me here, Simon.
    To be fair the OP doesn't state whether they want to be able to go back to the Main sheet at anytime or whether only that days worksheet should be visible
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  6. #6
    VBAX Regular
    Joined
    Sep 2004
    Posts
    61
    Location
    Thanks Ken and Simon,

    I already had the open workbook event and each worksheet has a button to return to main page hiding the others.

    Ken, your code does exactly as I asked, however it has created another problem for me. Previously I had a validation list in G1 on the day sheets to change dates manually, and this then triggerred a Worksheet Change event on that sheet that calls other subs to do an number of things including colour certain cells when conditions are met. For some reason, when using your code this has stopped on the day sheet, but occurred on the Main sheet (which doesnt have a Worksheet Change routine).

    It would appear that once the day sheet becomes visible, the Worksheet Change event triggers, however that Main sheet must still be the active sheet and this line hasnt yet executed.

    .Range("G1") = Worksheets("Main").Range("B2").Value

    (I dont understand why the routine is firing on the Main Sheet even though the code is on the day sheet)

    I thought of placing the Worksheet Change routine at the end of your routine, however each day sheet has a different routine so this wouldnt work.

    Any thoughts?

    Koala

  7. #7
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Ken's code cannot and does not change anything in your workbook except the activesheets G2, if you make a sheet visible it automatically becomes the active sheet, from what i understand G2 is changing and there for triggering the event.

    Can you supply a sample workbook thats displaying this behaviour?
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  8. #8
    VBAX Regular
    Joined
    Sep 2004
    Posts
    61
    Location
    Thanks Simon,

    The day sheet, even though visible is not active.

    When the code runs, the day sheet does not become active until the Main sheet is hidden.

    To overcome this I added

    .Activate

    before

    .Range("G1") = Worksheets("Main").Range("B2").Value

    and the Worksheet Change event now works fine.

    Thanks for your help

    cheers
    Koala

Posting Permissions

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