Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicate Comboboxes (2000)

    Hi,
    Is there a quick way to duplicate comboboxes?
    I have a simple rota to do, where I have a combobox which lists names. Problem is I need to do about 45 comboboxes and do not relish the thought of changing all the cell references.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate Comboboxes (2000)

    Have you tried copy & paste?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Comboboxes (2000)

    Yep <img src=/S/smile.gif border=0 alt=smile width=15 height=15> and the Linked cell always refers to the original combobox linked cell.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate Comboboxes (2000)

    So what would you want instead?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Comboboxes (2000)

    I need to duplicate the contents of each combobox but have different Linked cells.
    I'm pretty sure that somewhere I saw an excel spreadsheet where this had been done but with hundreds of cells. I solely wondered if there was a quicker way of doing it than re-referencing all the linked cells.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate Comboboxes (2000)

    You might be able to use Data | Validation with the List option. This will display a dropdown list in the cell, and no code is needed.
    - Select the range of cells you want to display a dropdown list.
    - Select Data | Validation...
    - Set Allow to List.
    - Specify the source (you can type the address or point to the source range)
    - Activate the Error Alert tab.
    - Set the Style to Stop and specify an error message.
    - Click OK.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Comboboxes (2000)

    That's what I'm after. Thanks for your advice!
    I also have an issue with the size of the listbox(?) text. The text in the cells is 14pt, the view on the form is currently 70% so that the person can view the whole form which will print on A4 landscape paper. The listbox text is tiny compared to the other text. Is there any way around this?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate Comboboxes (2000)

    No, increasing the zoom percentage is the only way to increase the text size.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Comboboxes (2000)

    Check out this demo!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Duplicate Comboboxes (2000)

    Here is a demofile that uses 1 combobox and worksheet selection change

    Steve

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Duplicate Comboboxes (2000)

    Jan,
    Great minds...
    You seem to have used the same approach as I did.

    I let the combobox appear over the cell selected and defined a set range of input...

    Steve

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Comboboxes (2000)

    Hi Steve,

    Yes, looks familiar.

    I didn't put it on top of the cell, as I figured the combobox would be bigger than the cell anyway, to keep it's contents readable at 70 % zooom factor.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Comboboxes (2000)

    Jan,

    I love it! How do you change the range of names? I typed a name after the three names in your demo but it is not picked up and I could not see a range name. The technique is great! I have tried to figure out how to do this before but gave up.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate Comboboxes (2000)

    Display the Control Toolbox toolbar.
    Turn on Design Mode (1st button on the Toolbox)
    Right-click the combo box and select Properties.
    Set the ListFillRange property of the combo box.

    Note: you can set the ListFillRange to a dynamic named range, but the list will not update itself automatically; you can use the Worksheet_Change event for this.
    Example: use Insert | Name | Define to create a name List that refers to the formula

    =OFFSET(Sheet1!$H$1,0,0,COUNTA(Sheet1!$H:$H),1)

    Put the following code in the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Me.Range("H:H"), Target) Is Nothing Then
    ' Refresh the ListFillRange
    Me.ComboBox1.ListFillRange = "List"
    End If
    End Sub

  15. #15
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Comboboxes (2000)

    Hans,

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I should have known that! Must be brain dead from being off for 4 days.

    Thanks,
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

Page 1 of 2 12 LastLast

Posting Permissions

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