CODEWITHSUNDEEP
excelvba
traveler84
traveler84

Reputation: 474

How do I hide a sheet using VBA?

Microsoft Excel 2010 has Sheet1, Sheet2, Sheet3.

On Sheet 1, the page is locked. It contains fields that reference Sheet3.

For example, Sheet1 on A2 is titled "Name" and B2 has =Sheet3!B2.

On Sheet3, A2 is titled "Name" and B2 is blank unless the user fills it in. Of course Sheet3 Cell B2 auto populates into Sheet1.

On Sheet 3, I have more similar fields that are editable by users such as address and phone number. However, I would like to hide Sheet3 by creating a button that hides Sheet3 once clicked. I don't want people that are emailed this file to see this Sheet3 with all the information on it. It will confuse automated systems.

I've researched all that I could and found some codes that hides a sheet after information is typed into a certain field but I was not sure how to apply that code to a button. The purpose for this button is not all my users are tech savvy so this is a quick way to allow them to hide the sheet without the fuss. The button would go on Sheet3 as well.

I found a code that could help if someone knows some VBA to convert it to the use of a button. This code says B6 and B7, hide the sheet. I am really lost how to use button click command with this almost close formula.

Sub ShowHideWorksheets()
Dim Cell As Range
For Each Cell In Range("B6:B7")
ActiveWorkbook.Worksheets(Cell.Value).Visible = Not
ActiveWorkbook.Worksheets(Cell.Value).Visible
Next Cell
End Sub

Upvotes: 1

Views: 255

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

almost, try:

ActiveWorkbook.Worksheets(Cell.Value).Visible = xlSheetHidden

or if you want to hide the sheet, and not allow the user to see it in the hidden sheet collection:

ActiveWorkbook.Worksheets(Cell.Value).Visible = xlSheetVeryHidden

Upvotes: 2

Related Questions