Frank Freese
asked on
Adrressing the incorrect worksheet
Folks,
In the attached workbook the Menu tab has Categories, Topic, and Description. Everything was working fine until I added the Category "Dynamic Date Filters" which does list 3 Topics. The problem I'm having is that when I select the previous Category "Interactive Controls" and then select any topic under that Category I'm off one worksheet from where I should be. Everything prior to "Interactive Controls" works fine. I've checked the code and the Range Manager and made sure my sheets were in correct order. Up till then everything was OK. I'm not getting any errors yet I'm not going to the correct sheet. I've looked at it so long I just don't see the problem.
Excel-Dashboards-Rev.xlsm
In the attached workbook the Menu tab has Categories, Topic, and Description. Everything was working fine until I added the Category "Dynamic Date Filters" which does list 3 Topics. The problem I'm having is that when I select the previous Category "Interactive Controls" and then select any topic under that Category I'm off one worksheet from where I should be. Everything prior to "Interactive Controls" works fine. I've checked the code and the Range Manager and made sure my sheets were in correct order. Up till then everything was OK. I'm not getting any errors yet I'm not going to the correct sheet. I've looked at it so long I just don't see the problem.
Excel-Dashboards-Rev.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're welcome. BTW I don't know if it happens to you, but if I double-click a Topic that doesn't yet have an associated sheet in SheetNames, Excel crashes. To prevent that add line 23 in MainMenu's code.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim strSheetName As String
Dim lngLastRow As Long
On Error GoTo ErrorRoutine
lngLastRow = Range("D65536").End(xlUp).Row
If Not Intersect(ActiveCell, Range("D3:D" & lngLastRow)) Is Nothing Then
If ActiveCell.Value = "" Then
Range("D3").Select
Exit Sub
End If
strSheetName = Worksheets("SheetNames").Range("ShowSheet")(intCatFactor + Target.Row + Sheets("ScrollbarData").Range("B2") - 3)
Sheets(strSheetName).Activate
Sheets(strSheetName).Select
Sheets(strSheetName).Activate
Sheets(strSheetName).Range("A1").Select
End If
Exit Sub
ErrorRoutine:
Cancel = True
MsgBox "The 'Go To Selection' sheet name for your selection needs to be added to sheet 'SheetNames'"
End Sub
ASKER
My crashes also and I forgot to bring this up.
Thanks
Thanks
Another thing you might want to change in that sub is the error message which I just realized still refers to the "Go To Selection" button we used to have. I would change it to.
MsgBox "No worksheet for '" & Target & "' was found in worksheet 'SheetNames'", vbOKOnly + vbExclamation, "Missing Worksheet"
ASKER
Thanks - great idea
ASKER