Select Combo Box Item and Jump to named range? 2

[LTillner]

Hi all,

I'm nearing the end of my current project to automate some redundant data entry in Excel. To wrap things up, I want to create a "Help" section on my one form that is needed to set up the data variables by the user.

To save space, I'd like to have a drop down list of Help Topics and when a topic is selected, then the user would click on a Go or help icon (next to the drop down of course) and go to that particular named range with the text info for that topic. (probably a help workbook with a separate sheet for each topic)

Anyway, I can create the drop down, create the basic macro to go to the named range (a variable)

My question, how do I pass the named range variable from the drop down list to the variable in the icon macro?
and How do I store the named range variable name with the User Friendly text name in the drop down without the user seeing it?

Thanks!

Lynette

Have I told you lately I LOVE TEK-TIPS? You guys are SUPER DUPER!

 

[Zathras]

Unless you have dozens and dozens of topics, why not just put worksheet links in cells? Then all the user needs to do is click on a link.

Assuming the user wants help (or else why is he looking at this page?) would it not be more helpful if all of the available topics were visible at once (assuming you have some logical groupings), rather than being required to scroll thru a list?

 

[LTillner]

There is an available space issue on the form and I'm trying to limit keystrokes. There are a limited number of help topics.

I just want to simply select an item from the short list and jumb to that named cell. If this is not possible or very difficult, I will do it the other way, but it's more clicks for the user that is the issue at this point.

Thanks

 

[fumei]

Frankly, I would not bother with a Go button (icon). Use an ActiveX dropdown (using the Controls toolbar) and then use the _Change event. The user selects an item from the dropdown, and it automatically goes to the appropriate Help location.

Make an array of those locations.
With the _Change event match the combobox.ListIndex number to the array, and just go there. Something like:

Sub ComboBox1_Change()
Dim HelpLocation(2) As String
HelpLocation(0) = "A1:D10"
HelpLocation(1) = "B3:C5"
HelpLocation(2) = "D3:F7"

Worksheets("HelpSheet").Range(HelpLocation(ComboBox1.ListIndex)).Select
End Sub

I am not an Excel person, so my syntax for going to a location is perobably poor, but hopefully you get the idea. That way, you don't need a Go button - unless of course you want one. In which case, move the code to the Go button_Click event.



Gerry

 

[SkipVought]

if you're on a different sheet than HelpSheet, then the last line of Gerry's code should be...

with Worksheets("HelpSheet")
  .Activate 
  .Range(HelpLocation(ComboBox1.ListIndex)).Select
end with

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[fumei]

Thanks Skip...it just shows how totally ignorant I am of Excel. Of which I freely achknowledge. Thanks for clearing it up. In retrospect, it seems rather obvious.....doh.

Gerry

 

[Zathras]

Glad you got it sorted, but I'm confused.

I'm trying to limit keystrokes

So with a combo box, you have:

Click (drop down the list)
Click (scroll to where the item wanted is shown)
Click (select the item wanted)

How does that limit "keystrokes" when compared to:

Click (go to the place wanted)

BTW, you will eventualy find that the Change event is fired every time the user uses a cursor key to move up and down thru the list. Be sure to include the specific instructions to use only the mouse when selecting a help topic. (User friendly -- not.) I guess that's one way of reducing keystrokes.

 

[LTillner]

Thanks guys, I've gotten to the same point here, the dropdown is working, the cell link is in and I'm ready to write the code to send the user to the text I want them to see.

Zathras, I think it does limit keystrokes in the user's mind if not in actuality, the list is short, 7 items, so they click the dropdown and select the item they want and Voila! they are there -- 2 keystrokes. If they click on the Help Icon instead (ignoring the dropdown) they will just get the list again so they've added keystrokes to choose what they want to see info on.

But the main thing for this user, is it looks slick. very little real estate used in their minds.

Thanks guys! As always, you're GREA-A-A-A-A-A-A-A-A-A-T!

I'll post if I have any problems with the case coding. But it should be simple enough for even my lame brain to handle!

Lynette

 

[fumei]

Zathras, you were absolutely correct....it should not have been the _Change event. AND correct in that cell links would, in some ways, be better.

Better yet maybe, would be putting a HELP! menu item on a toolbar, with the Help items listed there. Click HELP!, dropdown list. Click an item...go there. Two clicks, and no real estate on the sheet at all.

Anyway, it seems something is working for the better.

Gerry

 

[LTillner]

Gerry, Good Idea! I think I will move it to the toolbar once I get it all working...

Thanks and have another star!

I've assigned a Macro to the combobox so that is where the code is going -- in the macro.