VBA to unhide sheet then re hide

[thedeadzeds]

Hi Guys,

Is it possible to use a use a macro to take you to a specific worksheet and then once you have click off this worksheet it rehides it?

Thanks

 

[Ruddles]

To hide a sheet when you click off it, add this to its code module:-

Private Sub Worksheet_Deactivate()
[I]Sheet1[/I].Visible = xlSheetHidden
End Sub

This is for Sheet1: change the name if your sheet is called something else or use its name, e.g. Sheets("MainSheet").

The tricky bit is how to select the sheet you want the macro to take you to in the first place. Do you need a menu of sheets to choose from, a pop-up box asking you for a sheet name, or do you want to be taken there automatically when 'something happens'?

 

[DavidCon]

The following macro will unhide Sheet2, activate it and select cell A1.

Sub UseSheetThenHideIt()
    With Sheets("Sheet2")    [COLOR=#b22222]'change name to suit[/COLOR]
        .Visible = xlSheetVisible
        .Activate
        .Range("A1").select
    End With
End Sub

The macro could be attached to a button placed on the referring sheet or a button on the ribbon or a shortcut key sequence, etc.

Then if the following code is inserted into the 'Worksheet' (as opposed to 'General') section of the Sheet2 MS Excel Object (get there from the Project Explorer)

Private Sub Worksheet_Deactivate()
    If Me.Name <> "Sheet2" Then Me.Name = "Sheet2"  [COLOR=#b22222]'this ensures the macro does not break if someone tries to alter the sheet name[/COLOR]
    Me.Visible = xlSheetHidden    [COLOR=#b22222]'or xlSheetVeryHidden if you really don't want folk to know its there[/COLOR]
End Sub

Note the comment about xlSheetVeryHidden. Using that means Sheet2 will not be visible when right clicking another tab and selecting unhide. Clearly, change sheet names to suit your spreadsheet.

This works until the sheet name is changed by one of your users (hence the xlSheetVeryHidden tip which also needs to be supplemented by some protection - already in the code above) or you (no special advice on that one ).