Accessing Cell Causes Worksheet to Open?

[shelby55]

Hello

I am using Excel 2010 and I realize that hyperlinks do not work when the worksheet being linked to is hidden. I have a dashboard where the latest value for the metric is displaying in column E. The metric description is in column B and I would like to have it where the user can select the cell for the metric name that would then open up a worksheet that has more information about that metric.

So in cell B7 is "length of stay" and I would like where if the user enters that it opens up worksheet named "LOS". If the user enters the cursor into cell B8 it would open up worksheet named "readmits". There are 10 different metric though all titles will be in column B.

Also, if the user goes into the hidden worksheet, I would like it so that it is hidden again when the user goes back to the dashboard.

Thanks very much.

 

[SkipVought]

Hi,

Use the Worksheet_SelectionChange event to 1) unhide the hidden sheet and the activate the sheet and select the appropriate cell.

The use the Worksheet_Deactivate event when the user leaves the sheet to hide the sheet.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Hey Skip

Long time no hear, hope you're doing well!

I went ahead and tried to do this on my own and got the deactivate to work for re-hiding the worksheet but in using the selectionchange event it worked but it worked for whatever cell I selected in the worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Cells(7, "B").Select Then
Worksheets("M1A").Visible = True
Worksheets("M1A").Select
End If

End Sub

Also, how do I add the other criteria like if Cells(9,"B") and opening a different worksheet?

Thanks Skip!

 

[SkipVought]

Is there data in that cell that indicates anything regarding that sheet, or not?

Or You could simply do this

If not intersect(target, cells(7,2)) is nothing then
'Do stuff for this selection here
Enf if

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Hi Skip

No data is in the cell of B7, just a metric title that won't change. I want the user to enter that cell to trigger the other worksheet to be visible.

Thanks.

 

[shelby55]

Hey Skip

Don't worry about it, I figured out a way: use a transparent shape object the same size as the cell and then add the macro to the shape. That way I can have a shape/macro per metric.

Thanks for your assistance.