Hide whatever the active Sheet tab is?

RWayneH
RWayneH used Ask the Experts™
on
Trying to hidden whatever the active Sheet tab Excel is on.  Why does this not work?
ActiveWorkbook.Worksheets().Visible = False

Open in new window


Please advise and thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
ActiveWorkbook.Worksheets(Activesheet.Name).Visible = False
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Since you'll get a runtime error if you try to hide the last visible worksheet in a workbook, I suggest surrounding your statement with error handling.
On Error Resume Next
ActiveSheet.Visible = False
On Error GoTo 0

Open in new window

Author

Commented:
Thanks, so would the same thing work with .Delete?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
If you delete the last worksheet in a workbook, the workbook closes automatically. No error is raised.

If you delete the last visible worksheet in a workbook, in other words leave only hidden worksheets, then a runtime error will be raised.

The rule is that there must be at least one visible worksheet in an open workbook. The only exception to that rule is add-ins, where all the worksheets are hidden by default, though you can make one or more sheets visible such as for data entry into the add-in.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Thanks, so would the same thing work with .Delete?
This will delete the sheet. If you remove the first and last line of code it will warn you that there may be data on the sheet.
Sub Del()
Application.DisplayAlerts = False
ActiveWorkbook.Worksheets(ActiveSheet.Name).Delete
Application.DisplayAlerts = True
End Sub

Open in new window

Author

Commented:
thanks for the help.  I opened a different question regarding the delete process I am using.  I populate a ListBox and user selects the sheet tab they want to delete, now it is preventing those sheet tabs from getting into the ListBox.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you including a new one concerning your Experts Exchange rank.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
              Experts Exchange Top Expert VBA 2018
              Experts Exchange Distinguished Expert in Excel 2018

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial