ExcelBanter - create button that can hide/unhide sheets and hyperlink...

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - create button that can hide/unhide sheets and hyperlink... (https://www.excelbanter.com/excel-programming/345706-create-button-can-hide-unhide-sheets-hyperlink.html)

create button that can hide/unhide sheets and hyperlink...

Hi, I'm trying to figure out two things;

create a button that can hide/unhide sheets

create a button that can takes the user to another place in the work book
(even if that sheet is hidden) - maybe this is a hyperlink of sorts?

(I know how to create the buttons, just need help big time(!) with the code)

Thanks!

Helen

create button that can hide/unhide sheets and hyperlink...

Helen,

Put the following code behind your button.

With Worksheets("Sheet1")
.Visible = Not .Visible
End With

It will toggle Sheet1 between hidden and visible.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Helen" wrote in message
...
Hi, I'm trying to figure out two things;

create a button that can hide/unhide sheets

create a button that can takes the user to another place in the
work book (even if that sheet is hidden) - maybe this is a
hyperlink of sorts?

(I know how to create the buttons, just need help big time(!)
with the code)

Thanks!

Helen

create button that can hide/unhide sheets and hyperlink...

Hi Helen,

create a button that can hide/unhide sheets

Try:

'============
Private Sub CommandButton1_Click()
Dim SH As Worksheet

Set SH = ActiveWorkbook.Sheets("Sheet3") '<<=== CHANGE
SH.Visible = Not SH.Visible
End Sub
'<<============

create a button that can takes the user to another place in the work book
(even if that sheet is hidden)

Try:

'============
Sub Tester2()
Dim SH As Worksheet
Dim rng As Range

Set SH = ActiveWorkbook.Sheets("Sheet2") '<<=== CHANGE
Set rng = SH.Range("A10") '<<=== CHANGE

SH.Visible = True
Application.Goto rng

End Sub
'<<============

---
Regards,
Norman

"Helen" wrote in message
...
Hi, I'm trying to figure out two things;

create a button that can hide/unhide sheets

create a button that can takes the user to another place in the work book
(even if that sheet is hidden) - maybe this is a hyperlink of sorts?

(I know how to create the buttons, just need help big time(!) with the
code)

Thanks!

Helen

create button that can hide/unhide sheets and hyperlink...

And if I want to do more than one sheet, do I do some kind of array?

Thanks,

Helen

"Chip Pearson" wrote in message
...
Helen,

Put the following code behind your button.

With Worksheets("Sheet1")
.Visible = Not .Visible
End With

It will toggle Sheet1 between hidden and visible.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Helen" wrote in message
...
Hi, I'm trying to figure out two things;

create a button that can hide/unhide sheets

create a button that can takes the user to another place in the work book
(even if that sheet is hidden) - maybe this is a hyperlink of sorts?

(I know how to create the buttons, just need help big time(!) with the
code)

Thanks!

Helen

create button that can hide/unhide sheets and hyperlink...

Hi Norman,

You're helping me a lot today!!

If I want to unhide/hide more than one sheet, how do I go about doing that?
Some kind of array as well...?

Thanks,

Helen

"Norman Jones" wrote in message
...
Hi Helen,

create a button that can hide/unhide sheets

Try:

'============
Private Sub CommandButton1_Click()
Dim SH As Worksheet

Set SH = ActiveWorkbook.Sheets("Sheet3") '<<=== CHANGE
SH.Visible = Not SH.Visible
End Sub
'<<============

create a button that can takes the user to another place in the work book
(even if that sheet is hidden)

Try:

'============
Sub Tester2()
Dim SH As Worksheet
Dim rng As Range

Set SH = ActiveWorkbook.Sheets("Sheet2") '<<=== CHANGE
Set rng = SH.Range("A10") '<<=== CHANGE

SH.Visible = True
Application.Goto rng

End Sub
'<<============

---
Regards,
Norman

"Helen" wrote in message
...
Hi, I'm trying to figure out two things;

create a button that can hide/unhide sheets

create a button that can takes the user to another place in the work book
(even if that sheet is hidden) - maybe this is a hyperlink of sorts?

(I know how to create the buttons, just need help big time(!) with the
code)

Thanks!

Helen

create button that can hide/unhide sheets and hyperlink...

Hi Helen,

If I want to unhide/hide more than one sheet, how do I go about doing
that? Some kind of array as well...?

Try:

'==============
Private Sub CommandButton1_Click()
Dim SH As Worksheet

For Each SH In Sheets(Array("Sheet2", "Sheet3")) '<<=== CHANGE
SH.Visible = Not SH.Visible
Next SH

End Sub
'<<==============

---
Regards,
Norman

"Helen" wrote in message
...
Hi Norman,

You're helping me a lot today!!

If I want to unhide/hide more than one sheet, how do I go about doing
that? Some kind of array as well...?

Thanks,

Helen

"Norman Jones" wrote in message
...
Hi Helen,

create a button that can hide/unhide sheets

Try:

'============
Private Sub CommandButton1_Click()
Dim SH As Worksheet

Set SH = ActiveWorkbook.Sheets("Sheet3") '<<=== CHANGE
SH.Visible = Not SH.Visible
End Sub
'<<============

create a button that can takes the user to another place in the work
book (even if that sheet is hidden)

Try:

'============
Sub Tester2()
Dim SH As Worksheet
Dim rng As Range

Set SH = ActiveWorkbook.Sheets("Sheet2") '<<=== CHANGE
Set rng = SH.Range("A10") '<<=== CHANGE

SH.Visible = True
Application.Goto rng

End Sub
'<<============

---
Regards,
Norman

"Helen" wrote in message
...
Hi, I'm trying to figure out two things;

create a button that can hide/unhide sheets

create a button that can takes the user to another place in the work
book (even if that sheet is hidden) - maybe this is a hyperlink of
sorts?

(I know how to create the buttons, just need help big time(!) with the
code)

Thanks!

Helen

create button that can hide/unhide sheets and hyperlink...

Thanks! Works great.

I noticed if my sheets are hidden then my preview button will not work. I'm
assuming I should add some code that checks if the sheets are hidden, and if
they are, then unhide them before performing the Preview command... Obviulsy
I have NO idea how to do that either!

Thanks!

Helen

"Norman Jones" wrote in message
...
Hi Helen,

If I want to unhide/hide more than one sheet, how do I go about doing
that? Some kind of array as well...?

Try:

'==============
Private Sub CommandButton1_Click()
Dim SH As Worksheet

For Each SH In Sheets(Array("Sheet2", "Sheet3")) '<<=== CHANGE
SH.Visible = Not SH.Visible
Next SH

End Sub
'<<==============

---
Regards,
Norman

"Helen" wrote in message
...
Hi Norman,

You're helping me a lot today!!

If I want to unhide/hide more than one sheet, how do I go about doing
that? Some kind of array as well...?

Thanks,

Helen

"Norman Jones" wrote in message
...
Hi Helen,

create a button that can hide/unhide sheets

Try:

'============
Private Sub CommandButton1_Click()
Dim SH As Worksheet

Set SH = ActiveWorkbook.Sheets("Sheet3") '<<=== CHANGE
SH.Visible = Not SH.Visible
End Sub
'<<============

create a button that can takes the user to another place in the work
book (even if that sheet is hidden)

Try:

'============
Sub Tester2()
Dim SH As Worksheet
Dim rng As Range

Set SH = ActiveWorkbook.Sheets("Sheet2") '<<=== CHANGE
Set rng = SH.Range("A10") '<<=== CHANGE

SH.Visible = True
Application.Goto rng

End Sub
'<<============

---
Regards,
Norman

"Helen" wrote in message
...
Hi, I'm trying to figure out two things;

create a button that can hide/unhide sheets

create a button that can takes the user to another place in the work
book (even if that sheet is hidden) - maybe this is a hyperlink of
sorts?

(I know how to create the buttons, just need help big time(!) with the
code)

Thanks!

Helen

create button that can hide/unhide sheets and hyperlink...

Hi Helen,

Try:

'==============
Private Sub CommandButton2_Click()
Dim SH As Worksheet
Dim arr As Variant

arr = Array("Sheet2", "Sheet3") '<<=== CHANGE

For Each SH In Sheets(arr)
SH.Visible = True
Next

Sheets(arr).PrintPreview

End Sub
'<<==============

---
Regards,
Norman

"Helen" wrote in message
...
Thanks! Works great.

I noticed if my sheets are hidden then my preview button will not work.
I'm assuming I should add some code that checks if the sheets are hidden,
and if they are, then unhide them before performing the Preview command...
Obviulsy I have NO idea how to do that either!

Thanks!

Helen

"Norman Jones" wrote in message
...
Hi Helen,

If I want to unhide/hide more than one sheet, how do I go about doing
that? Some kind of array as well...?

Try:

'==============
Private Sub CommandButton1_Click()
Dim SH As Worksheet

For Each SH In Sheets(Array("Sheet2", "Sheet3")) '<<=== CHANGE
SH.Visible = Not SH.Visible
Next SH

End Sub
'<<==============

---
Regards,
Norman

"Helen" wrote in message
...
Hi Norman,

You're helping me a lot today!!

If I want to unhide/hide more than one sheet, how do I go about doing
that? Some kind of array as well...?

Thanks,

Helen

"Norman Jones" wrote in message
...
Hi Helen,

create a button that can hide/unhide sheets

Try:

'============
Private Sub CommandButton1_Click()
Dim SH As Worksheet

Set SH = ActiveWorkbook.Sheets("Sheet3") '<<=== CHANGE
SH.Visible = Not SH.Visible
End Sub
'<<============

create a button that can takes the user to another place in the work
book (even if that sheet is hidden)

Try:

'============
Sub Tester2()
Dim SH As Worksheet
Dim rng As Range

Set SH = ActiveWorkbook.Sheets("Sheet2") '<<=== CHANGE
Set rng = SH.Range("A10") '<<=== CHANGE

SH.Visible = True
Application.Goto rng

End Sub
'<<============

---
Regards,
Norman

"Helen" wrote in message
...
Hi, I'm trying to figure out two things;

create a button that can hide/unhide sheets

create a button that can takes the user to another place in the work
book (even if that sheet is hidden) - maybe this is a hyperlink of
sorts?

(I know how to create the buttons, just need help big time(!) with the
code)

Thanks!

Helen