Drop Down List For Sheet Navigation

Split off from
http://www.ozgrid.com/forum/showthread.php?t=90171

Quote from Dave Hawley

Try it now. You hadn't named the cells that the Validation lists are Dependant on. Not bad for a "moron", hey?

:oops: Sorry about that. Things get pretty stressed around here, especially when your commander depends on you as much as mine, and just between the two of us, it is twice as hard when your commander is younger than you. Let me try it real quick.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Ok, that works great, now I have to go back and add all the real units and soldiers. Fun, fun, fun. BTW, you wouldn't happen to know how to add a first worksheet that would have buttons to take you to the different sections? I know how to do the hyperlinks, but I think I read something on here about comboboxes?? If I need to post this somewhere else, then just push me in the right direction. I am use to pushing in the Army, it's our life!!

I thank you Dave, and the Army thanks you as well. Thanks for making life here a little easier.

Re: Dependent/Linked Drop Downs In Cells

Quote from Dave Hawley

Don't worry about it, I can be a moron at times. You guys are doing a great job over there and doing so for a good cause.

To select sheets via a ComboBox. Add one from the Control Toolbox toolbar and place this code in the Sheet Module housing the ComboBox.

Code

Private Sub ComboBox1_Change()
    If ComboBox1.ListIndex > -1 Then Sheets(ComboBox1.Text).Select
End Sub


Private Sub ComboBox1_GotFocus()
Dim objSheet As Object
    On Error Resume Next
        ComboBox1.Clear


        For Each objSheet In ThisWorkbook.Sheets
            ComboBox1.AddItem objSheet.Name
        Next objSheet
End Sub

Display More

Also, this may be better
Create a Sheet Index of Excel Worksheets

I will give it a shot, still having problems with the first one, but I will try to get back to it in a few minutes. We have a VTC with the Division, and some of us are required to go, so I will check back later. Thanks again Dave!!

Ok I tried it and the button works, but where in the code do I tell it that if I hit that button to take me to the other sheet. For example, in the included document, how do I get the button to take me to Sheet1?

Re: Dependent/Linked Drop Downs In Cells

Ok I tried it and the button works, but where in the code do I tell it that if I hit that button to take me to the other sheet. For example, in the included document, how do I get the button to take me to Sheet1? Thanks again Dave for all your help.

- J.D.

Re: Drop Down List For Sheet Navigation

Quote from Dave Hawley

What button? The code uses a ComboBox Control and takes you to the sheet name chosen.

Good Morning Dave!

Ok, on the Menu sheet, there is the box with the word Issue on it. When I click that box, or button, then I should be moved to Sheet1. It really doesn't where on sheet1 just that I move there. Sooner or later, there will be several boxes on Menu worksheet, and each one will take you to a different section within the workbook. I may have more questions later, but I will double check the search section before I make a mistake and get myself banned.

- J.D.

Re: Dependent/Linked Drop Downs In Cells

Quote from Dave Hawley

Don't worry about it, I can be a moron at times. You guys are doing a great job over there and doing so for a good cause.

To select sheets via a ComboBox. Add one from the Control Toolbox toolbar and place this code in the Sheet Module housing the ComboBox.

Code

Private Sub ComboBox1_Change()
'Ensure Chosen Item Is Part Of List. If So, Select the Chosen Sheet
    If ComboBox1.ListIndex > -1 Then Sheets(ComboBox1.Text).Select
End Sub


Private Sub ComboBox1_GotFocus()
Dim objSheet As Object
    On Error Resume Next
'Clear All Prior Sheet Nmaes
        ComboBox1.Clear


'Loop Through Each Sheet and Add Its Name To the ComboBox List
        For Each objSheet In ThisWorkbook.Sheets
            ComboBox1.AddItem objSheet.Name
        Next objSheet
End Sub

Display More

Also, this may be better
Create a Sheet Index of Excel Worksheets

Ok, I see that the instructions have been added to this VBA code, but I am sorry, Dave, I am still new to the code thing. You mention about making sure the item is added to the list. Not really sure what that means. I am not exactly clear on what list I am supposed to add to. I opened the example I sent, and just pasted the code in, and the box clicks like it is supposed to, but the view remains on Menu page. I guess, what I am looking at is when I click on it, it hyperlinks me to Sheet1.

When I click on the combobox on the worksheet named "Menu", then it should take me to the worksheet named "Sheet1", and then I should be able to add multiple comboboxes later for the other operations the commander wants. It just doesn't seem to be working like it is supposed to.

- J.D.

Re: Drop Down List For Sheet Navigation

Quote from Dave Hawley

No, not "intructions". My comments are code comments that state what the code does. My post and code has nothing to do with buttons ONLY a single ComboBox placed on the Worksheet from the Control Toolbox Toolbar. Add one from the Control Toolbox toolbar and place the code in the Sheet Module housing the ComboBox.

If you want multiple buttons, add drawing objects from the Drawing Toolbar and hyperlink them to a Named Range on each Worksheet.

Or, as stated on the page I linked to, simply right click on the sheet scroll tab and get a built in pop-up menu for sheet navigation.

Good Morning Dave!!

Thanks for your help. I got it to work. I was looking around yesterday on the forum, and pieced together what I thought I needed, and it worked. Here is the code that I used:

Private Sub ComboBox1_Change()
    If ComboBox1.ListIndex > -1 Then Sheets(ComboBox1.Text).Select
End Sub
 
Private Sub ComboBox1_GotFocus()
    Dim objSheet As Object
    On Error Resume Next
    ComboBox1.Clear
     
    For Each objSheet In ThisWorkbook.Sheets
        ComboBox1.AddItem objSheet.Name
    Next objSheet
End Sub


Private Sub CommandButton1_Click()
Sheet60.Cells(1, 1).Value = ""
Sheet60.Activate
End Sub


Private Sub ComboBox2_Change()
    If ComboBox2.ListIndex > -1 Then Sheets(ComboBox2.Text).Select
End Sub
 
Private Sub ComboBox2_GotFocus()
    Dim objSheet As Object
    On Error Resume Next
    ComboBox1.Clear
     
    For Each objSheet In ThisWorkbook.Sheets
        ComboBox2.AddItem objSheet.Name
    Next objSheet
End Sub


Private Sub CommandButton2_Click()
Sheet2.Cells(1, 1).Value = ""
Sheet2.Activate
End Sub

I hope I added the code in here correctly this time. I have added the file below. I do have a few more questions, but I will check the forum first before I post anything. I also was wondering if you could give me the link on naming, and posting thread titles. I saw someone on here get the link for it, but I don't remember exactly where. Thanks again Dave!!

- J.D.

Re: Drop Down List For Sheet Navigation

Quote from Dave Hawley

J.D, see [fa]*[/fa]

This is default text.

Thanks Dave!! I have read through the links you sent me, and I hope the next time I need a question answered (which should be soon with this officer corps we have here), I will accurately post a new thread, if needed. I will definately check the search area first though. Thanks again Dave for all your help, time, understanding, and patience. Have a great day!!

- J.D.