Linking Combo Box to other sheets in workbook

[jrf001]

HI,
I have created a combo box with 10 store names. This is situated in A1 on each sheet.There is 10 worksheets containing data for each store
I want to be able to select say the third store in the list and it will take me to that stores worksheet (was called sheet3, but its now the store name).

I am sure its got something to do with linkedcell in the properites box but I am not sure.

Please help out a confusd Australian.

cheers Jason

[nisht]

Working with the combobox is real fun for me and that was the reason i tried a populate combobox with 8 different ways you can see how the combobox can be populated by downloading "Populating Combobox" file from my download section. File nos is 16.
Now for your problem .

You download a file "Changing Combobox" from my download section. File nos is 18

It shows how your list is change as you go on selecting your data.

Write back to me if you have any more queries.

http://www.pexcel.com/download.htm
nishith desai

[Aladin Akyurek]

On 2002-03-17 03:35, jrf001 wrote:
HI,
I have created a combo box with 10 store names. This is situated in A1 on each sheet.There is 10 worksheets containing data for each store
I want to be able to select say the third store in the list and it will take me to that stores worksheet (was called sheet3, but its now the store name).

I am sure its got something to do with linkedcell in the properites box but I am not sure.

Please help out a confusd Australian.

cheers Jason

Jason,

What follows might approximate your situation.

Create a list of the names of the stores in a worksheet named Admin (one that requires maintenance by the WB administrator)in, say, A3:B5. A2 houses the label "STORES".

Example:

{"STORES","";
"Store1","[aaHyperlinkSheets jrf001.xls]Store1!A1";
"Store2","[aaHyperlinkSheets jrf001.xls]Store2!A1";
"Store3","[aaHyperlinkSheets jrf001.xls]Store3!A1"}

Addresses in B are not clickable URL's within the file aaHyperlinkSheets jrf001.xls. Store1 to Store3 are the sheet names that correspond to the store names.

Select the A-cells of this list (that is, A3:A5) excluding the label SLIST, go to the Name Box, type SLIST, and hit enter.

Select A3:B5, go to the Name Box, type HLINKS, and hit enter.

Create a ComboBox using Toolbars|Forms (not ControlToolbox) in a target worksheet, set its Input range to SLIST and its Cell link to, say, B4. Take care the box covers at least B4 and B5 (We don't need to see the linked cell, do we?).

In say B8 enter:

=HYPERLINK(VLOOKUP(INDEX(SLIST,B4),HLINKS,2,0),INDEX(SLIST,B4))

which gives you a clickable hyperlink that moves you to the selected sheet name.

Aladin

PS. If interested in a WB that uses the above, just drop me a line.

[jrf001]

Thanks Aladin,
I am sort of a bit closer. Can I email you the file and you can see what I need help with. I followed your instructions, but I was not able to get it to work.

Please help

quote]
On 2002-03-17 06:48, Aladin Akyurek wrote:

On 2002-03-17 03:35, jrf001 wrote:
HI,
I have created a combo box with 10 store names. This is situated in A1 on each sheet.There is 10 worksheets containing data for each store
I want to be able to select say the third store in the list and it will take me to that stores worksheet (was called sheet3, but its now the store name).

I am sure its got something to do with linkedcell in the properites box but I am not sure.

Please help out a confusd Australian.

cheers Jason

Jason,

What follows might approximate your situation.

Create a list of the names of the stores in a worksheet named Admin (one that requires maintenance by the WB administrator)in, say, A3:B5. A2 houses the label "STORES".

Example:

{"STORES","";
"Store1","[aaHyperlinkSheets jrf001.xls]Store1!A1";
"Store2","[aaHyperlinkSheets jrf001.xls]Store2!A1";
"Store3","[aaHyperlinkSheets jrf001.xls]Store3!A1"}

Addresses in B are not clickable URL's within the file aaHyperlinkSheets jrf001.xls. Store1 to Store3 are the sheet names that correspond to the store names.

Select the A-cells of this list (that is, A3:A5) excluding the label SLIST, go to the Name Box, type SLIST, and hit enter.

Select A3:B5, go to the Name Box, type HLINKS, and hit enter.

Create a ComboBox using Toolbars|Forms (not ControlToolbox) in a target worksheet, set its Input range to SLIST and its Cell link to, say, B4. Take care the box covers at least B4 and B5 (We don't need to see the linked cell, do we?).

In say B8 enter:

=HYPERLINK(VLOOKUP(INDEX(SLIST,B4),HLINKS,2,0),INDEX(SLIST,B4))

which gives you a clickable hyperlink that moves you to the selected sheet name.

Aladin

PS. If interested in a WB that uses the above, just drop me a line.
[/quote]

[Dave Hawley]

Hi

As you have mentioned "LinkedCell" I assume you are uisng a Combobox From the "Control toolbar" (ActiveX control). If so just use:

Private Sub ComboBox1_Change()
Dim strSheet As String
If ComboBox1.ListIndex > -1 Then
strSheet = ComboBox1
Sheets(strSheet).Select
End If
End Sub

_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training


[ This Message was edited by: Dave Hawley on 2002-03-17 20:50 ]

[Dave Hawley]

I was just read the other method mentioned and it seems very Cumbersome for such a simple task. The ActiveX controls will allow far more flexiblity and even populate your with all Sheet names each time it is selected.

Private Sub ComboBox1_DropButt*******()
Dim wsSheet As Worksheet
On Error Resume Next
ComboBox1.Clear
On Error GoTo 0
For Each wsSheet In ActiveWorkbook.Worksheets
ComboBox1.AddItem wsSheet.Name
Next
End Sub


This way you dont have to bother with making combersome changes to numerous Worksheet functions every time the structure of the workbook changes.




_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training


[ This Message was edited by: Dave Hawley on 2002-03-17 20:57 ]

[jrf001]

I must be nearly there. Can I send you the file. Its only a small query

cheers


quote]
On 2002-03-17 20:47, Dave Hawley wrote:
Hi

As you have mentioned "LinkedCell" I assume you are uisng a Combobox From the "Control toolbar" (ActiveX control). If so just use:

Private Sub ComboBox1_Change()
Dim strSheet As String
If ComboBox1.ListIndex > -1 Then
strSheet = ComboBox1
Sheets(strSheet).Select
End If
End Sub

_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training


[ This Message was edited by: Dave Hawley on 2002-03-17 20:50 ]
[/quote]

[Dave Hawley]

Sure can!

davidh@ozgrid.com