Click on a cell and go to specific worksheet

Folks,
In the attached workbook, the user selects a Category from lstCategory and a Topic from lstTopic from worksheet "Menu"
From here this take the user to a location on the "Menu" worksheet and enables to "Go To Selection" command button that allows the user to go to that specific worksheet. The Topics are located in column B beginning at row 10 and an addition description labeled "Purpose" is adjacent.
I would like the user to have an additional way to go to the desired worksheet by clicking on the Topic or Purpose in addition to the Go To Selection command button. You will see a yellow "=====>" to indicated where the user is. Only when the "=====>" is visible can the Topic and Purpose can be clicked on.
Function-and-Formulas-for-Excel.xlsm
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Frank FreeseAuthor Commented:
Folks,
If I use a Hyperlink I can get to the desired worksheet. If there is a better way it would avoid problems I have with hyperlinks:
1. The hyperlink changes the size of the font and the color making it difficult to see.
2. The hyperlink looks to be always enabled.
0
Frank FreeseAuthor Commented:
Folks,
I've solved the color and font size. I'm down to enabling the hyperlinks when the topic has been selected
0
JPIT DirectorCommented:
Rather than use a hyperlink, I would use a command button to get the value from your topic list and use a select case statement to evaluate it and activate the appropriate sheet. Something like this should work.

Sub goto_sheet()
select case 1sttopic.selection
         Case "Convert Currency"
                 sheets("Convert Currency").activate
         Case "Quick Calendar"
                 sheets("Quick Calendar").activate
end select
End sub
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Frank FreeseAuthor Commented:
In a previous design I had command buttons but the worksheet looked so busy. However, it maybe the ONLY option. I'm still working on hyperlinking though.
0
JPIT DirectorCommented:
You would only need one command button, then the code will go to the selected sheet.
0
Frank FreeseAuthor Commented:
I've got that now with the "Go to Selection" command button. I was wanting another way for the user to move to a worksheet if the selected a Topic or a Purpose (not from the lst boxes). I'm interested in enabling the hyperlink but only if the "Go To Selection" command buuton has been enabled, else the hyperlink would not be enabled.
0
Martin LissOlder than dirtCommented:
Here's an updated workbook that now contains this code in the Menu sheet.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'******* Q6 Start *******
If "'" & Cells(Target.Row, 1).Value = SEL_ARROW Then
    GoToSelection = True
End If
'******* Q6 End *********
End Sub
Q-28246495.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
awesome
simply fantastic
great job my good man
0
Martin LissOlder than dirtCommented:
You're welcome kind sir.
0
Frank FreeseAuthor Commented:
martin,
your code makes it look so easy - I spent so much time chancing hyperlink. I am impressed
0
Frank FreeseAuthor Commented:
I have a question.
You've been with me pretty much from the start and you've done a good job moving me through this project. You might have noticed I added a Print command button to each worksheet. My son really liked that.
I'm running out of additional features except to protect the user from changing the workbook. Can you think of a feature(s) to consider?
0
Martin LissOlder than dirtCommented:
As far as new features go let me say this. You can have too many features but I don't think you've reached that point yet. Also while this isn't directly answering the question, you had me add the ability to send the user to the sheet if the ====> indicator is present. That's good, but why not allow the user to go to the detail sheet regardless of the presence of ====> or not? I don't see that hurting anything while it increases the "features". If you remember I suggested that it could be done by double-clicking a cell.

I didn't notice the print buttons but what do they do for the user that Ctrl+P doesn't do? And in your print routines you send the user back to the Menu sheet. I might be annoyed by that if I wasn't yet done looking at the sheet. So if it were my wb I would remove the print buttons.
0
Frank FreeseAuthor Commented:
Good comments. Some of the features came from my son such as the =====> and a command button to print even though Ctrl+P is second nature to us.

I was think of modifying the command print to do a print preview (?) first then electing to print or not. I do see your point that once the worksheet has been printed NOT to return the user to the Menu sheet. Only the Main Menu button should take the user back to the menu. Thoughts please
0
Frank FreeseAuthor Commented:
I thought of something like this
Sub cmdPrintProduction()
  ans = MsgBox(Prompt:="Print..", Buttons:=vbYesNoCancel, Title:="Print")
  If ans = vbCancel Then Exit Sub
    If ans = vbYes Then
      Selection.PrintOut
    Else
      Selection.PrintOut Preview:=True
    End If
End Sub 

Open in new window

0
Martin LissOlder than dirtCommented:
IMO this is a better way to do that.
Sub cmdPrintProduction()
    ans = MsgBox(Prompt:="Print..", Buttons:=vbYesNoCancel, Title:="Print")
    Select Case ans
        Case vbCancel
            Exit Sub
        Case vbYes
           Selection.PrintOut
        Case Else
           Selection.PrintOut Preview:=True
    End Select

End sub

Open in new window


And rather than repeating that code in every sheet you could move lines 2 to 10 to a macro that you might call Sub PrintSheet and then do this

Sub cmdPrintProduction()
    PrintSheet
End Sub

Open in new window


and similarly in all the other cmdPrint… Subs.
0
Frank FreeseAuthor Commented:
Martin,
I need to post a question that I know only you can answer for me. Are you where you can take my question?
0
Martin LissOlder than dirtCommented:
Sure.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.