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

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

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

"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

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

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

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