Pick from a list and goto a range. (Like Selecting Pages)

[mechInferno]

I have been trying to rack my brain about how to do this. I have a list which is populated by a range of data. Just a validation list. This list is for Page # and descriptions. I want to select a Page# and description and go to that page. I'm sure there is a way to do this, its just that I'm not quite getting it. Any help would be great.

Thanks

mech

 

[fumei]

What application is this for? May help.

Gerry

 

[mechInferno]

I have been using Excel 2000.

 

[Bong]

So, in Excel, what does "page number" mean?

_________________
Bob Rashkin

 

[mechInferno]

I have a list which goes:
Page.1
Page.2
Page.3 ...
Page.10

On my worksheet I want to click on the arrow for the list and select one of the pages in the list (like above). From there I want to go to that page. What I mean by page is different sections of one worksheet. So the first page may be A1 then the second page will be J1 and so on.

So my question again (revised) is how do I select an item in that list to go to a section of a worksheet. Sorry for making this confusing as you can see how confused I am.

Thanks

mech

 

[fumei]

The section must be defined by you. I suggest you do not use terms that are confusing. Page is not really an Excel term.

So say YOU define a section as starting at A1 (whatever). And another starting at J1 (agian, whatever). Make your list in the box:

A1
J1
M1
etc.

Remember, the list is simply text. A1, J1 in the listbox is text. The specific item selected from a list is the ListIndex. It is 0 based - the first item = ListIndex 0

So say J1 is selected. Then Listbox1.Listindex = 1. You can use the Listbox1_Change event if you like. That means whenever it changes the Sub Listbox1_Change() fires. Something like:

Sub Listbox1_Change()
Select Case Listbox1.ListIndex
   Case 0
     Range("A1").Select
   Case 1
     Range("J1").Select
   Case 2
     Range("M1").Select
   ....etc etc
End Select

This is highly simplified, as you do not state that you want to select the range. You say "go to" but what exactly do you want to happen? Does matter really. You asked how you can take a selected item and DO SOMETHING. This is how.

You can terms like Page.1 if you like, again it doesn't matter. YOU have to determine the logic.

You could use the text itself, as in:

Sub Listbox1_Change()
Select Case Listbox1.Text
   Case "Page.1"
     Range("A1").Select
   Case "Page.2"
     Range("J1").Select
...etc...
End Select

Finally, the best and easiest way.

Sub Listbox1_Change()
Range(Listbox1.Text).Select
End Sub

This way, WHENEVER the listbox is changed, the selected item (cell) is selected. Note however, the above can NOT be used with text like Page.1, Page.2 - as Excel does not know what to do with that. It would only work if the text of the item is a cell address.

Hope this helps.

Gerry

 

[mechInferno]

Actually this does help. You see my listbox contains data such as:
Page.1 - Details
Page.2 - Materials
and so on.
The listbox is not a placed control but a list of values elsewhere on the worksheet.
These Page.1 and Page.2 are just assigned values on the worksheet such as: A1 and J1. I know that in VB.net you can add a event handler to controls and take in the text within. I haven't worked much with VBA and I just needed to know how to handle different selections in the listbox.

But anyways thank you for your help,

Kevin (mechInferno)
**confused programmer**

 

[fumei]

Uh

The listbox is not a placed control but a list of values elsewhere on the worksheet.

then it is not a listbox, is it? Why did you call it that? What a waste of time.

So. What you really have is a bunch of cells with text in them, like:

Page.1 - Details
Page.2 - Materials
and so on.

and

I just needed to know how to handle different selections in the listbox.

is meaningless - because there IS NO listbox. There IS no selection of items in a listbox.

Could you please start again, and actually describe what is going on?

It SOUNDS like you have some cells, with the Page.1 - Details Page.2 - Materials and so on text in them, and you want to go to someplace else when the user selects that cell.

Again, if that is the case (and I am not sure it is), then it is not a listbox - it is a bunch of cells with text in them.



Gerry

 

[Bong]

Excel has a function that does exactly what you want: hyperlink. You can add hyperlinks:

Adds a hyperlink to the specified range or shape. Returns a Hyperlink object.

expression.Add(Anchor, Address, SubAddress, ScreenTip, TextToDisplay)
expression Required. An expression that returns a Hyperlinks object.

Anchor Required Object. The anchor for the hyperlink. Can be either a Range or Shape object.

Address Required String. The address of the hyperlink.

SubAddress Optional Variant. The subaddress of the hyperlink.

ScreenTip Optional Variant. The screen tip to be displayed when the mouse pointer is paused over the hyperlink.

TextToDisplay Optional Variant. The text to be displayed for the hyperlink.

as in the following example:

'This example adds a hyperlink to cell A5.

With Worksheets(1)
    .Hyperlinks.Add Anchor:=.Range("a5"), _
        Address:="", SubAddress:="Sheet1!D123",_
        TextToDisplay:="Page n:   whatever"
End With

_________________
Bob Rashkin

 

[fumei]

And that is that...I think. Use hyperlinks.

Gerry

 

[mechInferno]

Thanks guys. Sorry for the confusion.

Kevin