We help IT Professionals succeed at work.

VBA to unhide a chart tab then switch to it

kittycanine
kittycanine used Ask the Experts™
on
My previous question was fully answered and is located here:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27229100.html

However I have encountered two problems which were not part of my initial question so here they are:

1)  How does the code get modified to unhide the chart tab?  In the attached example for number 7, the chart is hidden, but an error occurs when you click on the appropriate cell.

2)  When a range is selected (click and drag), an error occurs and prompts for debugging.

What needs to be changed in the attached VBA code  Multiple-Chart-Link-Example.xlsm
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  On Error GoTo errMsg
  
  If Not Intersect(Target, Range("B31:K31")) Is Nothing Then
    If InStr(1, Target.Value, "Sign") Then
        Sheets(Target.Value).Select
    End If
  End If

Exit Sub

errMsg:
  strMsg = "There is no chart named '" & Target.Value & "'."
  intMsg = MsgBox(strMsg, vbExclamation, "Chart Not Found")

End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I suppose it should be noted that Sign 1 Sketch  through Sign 10 Sketch will always exist.  Its just a matter of they may not always be visible.
Commented:
I think this does it.  It does not hide the chart when they're done looking at it, however.

sdwalker
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  On Error GoTo errMsg
  
  If Not Intersect(Target, Range("B31:K31")) Is Nothing and target.cells.count = 1 Then
	If InStr(1, Target.Value, "Sign") Then
		sheets(Target.Value).Visible = true
		Sheets(Target.Value).Select
	End If
  End If

Exit Sub

errMsg:
  strMsg = "There is no chart named '" & Target.Value & "'."
  intMsg = MsgBox(strMsg, vbExclamation, "Chart Not Found")

End Sub

Open in new window

Author

Commented:
back from my appointment, will test his out shortly.  Had a problem with cells not calculating while I was at my appointment despite autocalc being on.  Had to edit the cell, then hit enter for it to up date.  Real pain in the butt, but that is an issue for another day.  One problem at a time.

Author

Commented:
Sweetness!  Solution to part B solved!