Linking Combo Box to other sheets in workbook
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Linking Combo Box to other sheets in workbook

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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.

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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]

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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]

  8. #8
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Sure can!

    davidh@ozgrid.com

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com