Sheet Selection using a Ranged index

[Guest]

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

 

[Guest]

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

 

[Guest]

Thanks Tom.

Ive used this and works well. But it works in the order of the sheets in the
workbook. How do I get it to select the relevant sheet dependant on an index,

eg.

Cell Link = 5 (Say Cell B1)

This list is in an excel sheet:-

Column A Column B

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

So depending on the cell link, B1, (5) I would like it to select sheet 'ABC5'

Cheers
Matt

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Thank you Tom. Excellent.

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