Hyperlink to Hidden/Unhidden Sheets with IF Condition

Shelby21

New Member
Joined
Nov 21, 2017
Messages
30
Need help with adding an IF condition for using hyperlink to view hidden or unhidden sheets.

In the image below, there are 3 tabs visible.
By default, the Instructions tab is always visible.
All other tabs becomes hidden or unhidden depending on if the active x checkbox is checked or not.

slip1.PNG


Below is the code I used for the active x checkboxes shown above to make each tab hidden or unhidden

slip2.PNG



In Wafer_Slip_DCP tab, column H has hand icons next to cells that are hyperlinked to other worksheets for spec reference.

For example, if you click on cell H11, this will display IR_Setup tab and take you to the row that references IR - Collapsible Hand Speed

slip3.PNG


This is the code I used for Wafer_Slip_Dcp tab to allow hyperlink to hidden or unhidden sheet:

VBA Code:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim ShtName As String
    ShtName = Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1)
    Sheets(ShtName).Visible = xlSheetVisible
    Sheets(ShtName).Select
End Sub


Here is what I would like to do:

After clicking on cell H11 in Wafer_Slip_DCP tab this will unhide and take me to IR_Setup tab as long as IR_Setup is not already checked in Instructions tab.
If it is already checked in the Instructions tab checkbox, than the tab is not hidden and the hyperlink just takes me to that worksheet.
This part is already done and works fine.

I want to be able to select the Wafer_Slip_DCP tab and have IR_Setup tab hide itself automatically, but only if it is unchecked in Instructions tab active x checkbox.

If IR_Setup active x checkbox is checked already in the Instructions tab, then I want to be able to switch between Wafer_Slip_DCP tab and IR_Setup tab without the tab becoming hidden everytime I switch tabs.


I assume I need some sort of IF statement in the IR_Setup vba code, but am not sure how to write it.
Any help with this would be appreciated.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,606
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub Worksheet_Deactivate()
   If Sheets("Instructions").IR_setup.Value = True Then Exit Sub
   Me.Visible = xlSheetHidden
End Sub
 
Solution

Shelby21

New Member
Joined
Nov 21, 2017
Messages
30
How about
VBA Code:
Private Sub Worksheet_Deactivate()
   If Sheets("Instructions").IR_setup.Value = True Then Exit Sub
   Me.Visible = xlSheetHidden
End Sub
Good morning Fluff,

Thank you for providing that code. It worked perfectly and was exactly what I was wanting to do :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,606
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,141,777
Messages
5,708,461
Members
421,571
Latest member
ChaosPup

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top