ExcelBanter - View Single Post

Matthew Balch[_2_]

Thank you Tom. Excellent.

"Tom Ogilvy" wrote:

assume your table (as shown in your post) is on Sheet3, starting in A1 with
no headers.

=vlookup(B5,Sheet3!A1:b5,2,False)

will return the sheet name. In code you can use this like

Dim res as Variant
res = Application.Vlookup(Activesheet.Range("B5"), _
Worksheets("Sheet3").Range("A1:B5"),2,False)
if not iserror(res) then
worksheets(res).Select
Else
msgbox "Match not made"
End if

--
Regards,
Tom Ogilvy

"Matthew Balch" wrote:

Used this fine. But would like the sheet to be selected from a list in a
sheet without it being predetermined by the order of the actual sheets.

Perhaps tackle the problem another way?

I have a list box, displaying all my titles. For each of those titles I have
a seperate sheet within the workbook, in no order. How do I select that
worksheet which has been selected in the list?

This is where Ive been trying to use an index within a sheet to guide the
navigation of this. The cell link being to key.

E.g. Cell link = B5 which in turn equals number 5 in the list

In my index on my sheet I have

Column A Column B

Cell Link Ref Sheet Name
1 ABC1
2 ABC2
3 ABC3
4 ABC4
5 ABC5

So in this case I would like it to select Sheet ABC5

How do I do this?

I know this is probably a bad way of doing it but I dont know anyother ways
other than through pure VB (i.e. using the coded name of the sheets as
opposed to the pyhsical name) which I know very little of and would struggle
hugely.

Cheers
Matt

"Tom Ogilvy" wrote:

worksheets(Activesheet.Range("B1")).Select

--
Regards,
Tom Ogilvy

"Matthew Balch" wrote:

Hi,

How do I write code to perform a simple sheet selection based upon a listbox
box selection?

My listbox equals a range in an excel sheet.

Once the user has selected the relevant item in the list the cell link the
sheet produces the relevant index number.

How do I then use this index number to go to a predefined sheet based upon
the cell link reference.

Index =
A B C
D
1
Title Code Tile Name Start Year
1 IJCLAW International Journal of Constitional Law 2003
2 JFINEC Journal of Financial Econometrics 2003
3 JICJUS Journal of International Criminal Justice 2003

The cell link at the moment = 1, ie B1. How do I get it to then select the
sheet which using the B1 value is equal to corresponding Title Code? In this
case IJCLAW?

Thanks
Matt