CODEWITHSUNDEEP
excelvba
Ingrid Fischer
Ingrid Fischer

Reputation: 57

Hide sheet on click?

I'm a VERY new learner to VBA trying to decypher functions and build an interactive file. With help from one user here (who will certainly know who he is ;)), I could learn quite a few things.

Now I'm stuck at one ridiculous piece of code : I want to 1/unhide a sheet, 2/go to that sheet and 3/re-hide the sheet when the user selects the "back to the start page-button". So I made this code : 

Sub FRtoEN()
'
' FRtoEN Macro
' Emmène au Glossaire FR ==> EN
'
    Sheets("synthèse_FR&EN").Visible = True
    Sheets("Synthèse_FR&EN").Select

End Sub

and it works well. But I cannot find out how to tell excel in VBA-language that I want it to re-hide the tab once the user is done and clicks the exit button.

Could you help me?

Upvotes: 2

Views: 1463

Answers (2)

Wizhi
Wizhi

Reputation: 6549

Ferndiando's answer is brilliant when you want to have one button where you first show the hidden sheet and next time you click on the same button it hide the same sheet.

Making one button to show a sheet and another button to hide the same sheet, do the following;

In the first button you will make the code visible:

Sub FRtoEN()
'
' FRtoEN Macro
' Emmène au Glossaire FR ==> EN
'
    Sheets("synthèse_FR&EN").Visible = True
    Sheets("Synthèse_FR&EN").Activate

End Sub

In the second button that which will take the user back to the "Main Page" you can add this code: 

Sub StartPage()

Sheets("Start Page").Activate 'First go to Start page
Sheets("synthèse_FR&EN").Visible = False 'Then hide the sheet they currently visited, that makes the experience a little bit more "working in background"

End Sub

If I assume you use this "back to the start page-button" for several sheets, you could hide other sheets too every time someone goes to start page.

Sub StartPage()

Sheets("Start Page").Activate 'First go to Start page
Sheets("synthèse_FR&EN").Visible = False
Sheets("synthèse_FR&DE").Visible = False 'Example 1 - No matter which sheet you visit, it will hide this sheets.
Sheets("synthèse_FR&SP").Visible = False 'Example 2 - No matter which sheet you visit, it will hide this sheets.

End Sub

If you want the code to perform things on hidden sheets, while they still are hidden for the user (for example background filtering/calculations/copy data etc..) this will give the user a smooth experience:

Sub StartPage()

Application.ScreenUpdating = False 'Turn of all visual updates the macro does. Macro works in background without showing every step visually in Excel.
Sheets("synthèse_FR&EN").Visible = True 'Unhide the sheet you want to work at.

'Do some filtering stuff // copy stuff 

Sheets("synthèse_FR&EN").Visible = False 'Re-hide the sheet again.
Application.ScreenUpdating = False 'Turn ON all visual updates the macro does. Macro now works and shows every step visually in Excel.

End Sub

:)

Upvotes: 3

Ferdinando
Ferdinando

Reputation: 964

If i understood your question you can use this code:

Sub myButton()
'Hide and Show Sheet2 with same button
' you can change the sheet name as you prefer

If (Sheets("Sheet2").Visible) Then ' control if the sheet is visible
    Sheets("Sheet2").Visible = False ' hide sheet2 because before was showed
    Sheets("Sheet1").Select          ' select sheet1
Else
    Sheets("Sheet2").Visible = True  ' show sheet2 because before was hidden
    Sheets("Sheet2").Select          'select sheet2
End If

End Sub

I hope this help you.

Upvotes: 2

Related Questions