Linking Combo Box to other sheets in workbook

jrf001

New Member
Joined
Mar 16, 2002
Messages
19
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
ni****h desai
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
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
OzGrid.BusApp.170x45.gif

This message was edited by Dave Hawley on 2002-03-17 20:50
 
Upvote 0
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_DropButtonClick()
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
OzGrid.BusApp.170x45.gif

This message was edited by Dave Hawley on 2002-03-17 20:57
 
Upvote 0
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
OzGrid.BusApp.170x45.gif

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

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top