Toggle VeryHidden Sheet

[Greg Rivet]

Can someone help with code to toggle a sheet from visible to VeryHidden and
then back to Visible. TIA

Greg

 

[John Wilson]

Greg,

Worksheets("yoursheetname").Visible = xlVeryHidden
Worksheets("yoursheetname").Visible = True

John

 

[Don Guillett]

try this. If you have a shape and name it togleit the commented text will
change too

Sub hide_unhidesheet()
If Sheets("sheet2").Visible = xlVeryHidden Then
Sheets("sheet2").Visible = True
'ActiveSheet.Shapes("toggleit").Select
'Selection.Characters.Text = "HIDE G" & Chr(10) & "" & Chr(10) & ""
'Range("c3").Select
Else
Sheets("sheet2").Visible = xlVeryHidden
'ActiveSheet.Shapes("Toggleit").Select
'Selection.Characters.Text = "SHOW G" & Chr(10) & "" & Chr(10) & ""
End If
End Sub

 

[Ken Wright]

Sub ToggleSheetView()

If Worksheets("Sheet1").Visible = xlVeryHidden Then
Worksheets("Sheet1").Visible = True
Else: Worksheets("Sheet1").Visible = xlVeryHidden
End If

End Sub

 

[Greg Rivet]

John, Ken & Don, thank you very much............

Greg

 

[Ken Wright]

Don't use it from the Control Toolbox. Use the button from the Forms Toolbar.
Before you create your button though, do the following:-

Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane you
need to search for
your workbook's name, and when you find it you may need to click on the + to
expand it. Within
that you should see the following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook

If you have named your sheets then those names will appear in the brackets above
as opposed to
what you see at the moment in my note.

Right click on the VBAProject bit and select insert module. Your screen should
now look something like this:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook
Modules
Module1

Double click on module 1 and then just paste the code into the white space that
appears, and then Then hit File / Close and return to Microsoft Excel and save
the file.

Now create your button from the FORMS toolbar and when you do so it will prompt
you for a macro to assign it to and will give you a list to choose from. Just
pick the one you have just pasted in to the module.

If your sheet name is any different then change the code to

Sub ToggleSheetView()

If Worksheets("Name of my sheet").Visible = xlVeryHidden Then
Worksheets("Name of my sheet").Visible = True
Else: Worksheets("Name of my sheet").Visible = xlVeryHidden
End If

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Forgive my ignorance, but can someone tell me where to place this code and how

do I associate it to the toggle button I created from the Control Toolbox?

 

[Guest]

Thank you so much Ken for the detailed instructions. This is exactly what I had hoped for

Have a splendid day....

 

[Ken Wright]

You are most welcome, and many thanks for letting me know it worked.

 

[Guest]

One more perhaps "newbie" question:

What is the difference between the FORMS toolbar and the Control Toolbox? Are there certain instances that I would use one over the other and why?

As in this case, you advised me to use the one from the Forms toolbar; why, exactly?

Again, thanks in advance for your advice and PATIENCE!

 

[Ken Wright]

No problem Marcy - See the same question posted previously and the comprehensive
answers to it:-

Hi,

what is the difference between the "button" from the Forms toolbar and the
"commandbutton" from the control toolbox?

For the commandbutton, double-clicking it will view the code associated with
it but how to access/view the code associated with the button for the Forms?
Thanks!

rgds, meow......Message 2 in thread
From: Edwin Tam ([email protected])
Subject: Re: what is the difference?????


View this article only
Newsgroups: microsoft.public.excel.programming
Date: 2000-11-21 01:14:54 PST


The "Button" object in the Form toolbar and the "commandbutton" object in
the control toolbox are very different internally.

They have different names. They are different objects.

Commandbutton is, in the Microsoft terms, an ActiveX control. The "Button"
object is an old product in the age of OLE objects. Although ActiveX and OLE
are actually the same thing, they represent the Microsoft technologies in
different periods of time. (ActiveX is new, OLE is old.)

To refer to a normal CommandButton, like all other ActiveX control, you just
call its name. For example, for a commandbutton called "cmb1", you use:

cmb1.Visible = True

to set its visibility to TRUE.

For a "Button" on your worksheet with the same name "cmb1", you use:

Worksheets(1).Buttons("cmb1").Visible = True


Therefore, you can see the main difference is, in the old days, to call an
OLE control, you need to know :
1) The name of the object in Excel's object library ("Button");
2) The name of that particular control ("cmb1")

On the contrary, for a new ActiveX control, what you need is only:
1) The name of that particular control ("cmb1")


This makes programming much easier and saves time.


Of course, there are drawbacks. (This is for advanced users only.) The main
drawback is, in the old days, you can refer to a large amount of OLE
controls using Arrays. For example, you got 3 Buttons "cmb1", "cmb2",
"cmb3". You could set the visibility in 1 senetence:

Worksheets(1).Buttons(Array("cmb1","cmb2","cmb3")).visible = True

This is not possible in the new ActiveX controls on UserForms. (e.g. the
Commandbar control). To do the same thing, you need 3 sentences:

cmb1.Visible = True
cmb2.Visible = True
cmb3.Visible = True


Finally, you want to know how to edit a macro assigned to a Button.
Just right-click a Button, choose "Assign Macro". There, you can assign
macros to the button or edit a particular macro, by clicking the "Edit"
button.


Regards,
Edwin Tam
http://users.vol.net/edwintam/EPT.HTM

Hi,

what is the difference between the "button" from the Forms toolbar and the
"commandbutton" from the control toolbox?

For the commandbutton, double-clicking it will view the code associated with
it but how to access/view the code associated with the button for the Forms?
Thanks!

rgds, meow......

Message 3 in thread
From: Tom Ogilvy ([email protected])
Subject: Re: what is the difference?????


View this article only
Newsgroups: microsoft.public.excel.programming
Date: 2000-11-21 05:13:39 PST


Here is some additional information:

For Controls from the Controls Toolbox Toolbar:
http://support.microsoft.com/support/kb/articles/Q168/0/67.ASP
XL97: WE1163: "Visual Basic Examples for Controlling UserForms"

For Controls from teh Controls Toolbox Toolbar on a Worksheet
http://www.microsoft.com/ExcelDev/Articles/activexl.htm
Using ActiveX Controls on a Microsoft Excel Worksheet

http://www.microsoft.com/ExcelDev/Articles/exsxsc10.htm
Using Dialog Box Controls on a Worksheet


For Controls from the Forms Toolbar:
http://support.microsoft.com/support/kb/articles/Q130/3/72.asp
XL: Sample Code for Controlling Dialog Boxes (WE1162)


Regards,
Tom Ogilvy

Hi,

what is the difference between the "button" from the Forms toolbar and the
"commandbutton" from the control toolbox?

For the commandbutton, double-clicking it will view the code associated with
it but how to access/view the code associated with the button for the Forms?
Thanks!

rgds, meow......

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

One more perhaps "newbie" question:

What is the difference between the FORMS toolbar and the Control Toolbox? Are

there certain instances that I would use one over the other and why?

 

[Ken Wright]

Where'd that attachment come from???????

Apologies folks, must have been an image in the post I copied from Google.
Thought Plain text settings stripped everything out - Guess not :-(

 

[Guest]

Thanks again, Ken. You excel experts are GREAT!!