+ Reply to Thread
Results 1 to 16 of 16

How to use a list from Excel Sheet with Userform ComboBox

  1. #1
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Question How to use a list from Excel Sheet with Userform ComboBox

    Hi All,

    Quick Question I hope

    I have given some cells in my spreadsheet a list name and would like my Combo Box in the Userform to show this list.

    How would i go about pointing the "Row Source" or VBA Script to this List?

    This is the Code for the ComoBox:

    Please Login or Register  to view this content.
    And the list is called "Kunden" and in "Tabelle4"

    Thank you for any help in advance

    Regards

    Jeremy
    Last edited by JRidge; 07-01-2014 at 09:46 AM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,212

    Re: How to use a list from Excel Sheet with Userform ComboBox

    Try this.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: How to use a list from Excel Sheet with Userform ComboBox

    Hey Norie,

    Looks good

    I also have a combo box on the user form that the user can select a colour, Is there away to point to the colour Palette so it shows the colours in the drop down??

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,212

    Re: How to use a list from Excel Sheet with Userform ComboBox

    Do you mean list the colours in a combobox?

  5. #5
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: How to use a list from Excel Sheet with Userform ComboBox

    Or show them, so the user can select a colour while filling the user form, then when he clicks "save" the data will be imputed into the spreedsheet and the back ground of the cells filled will e changed to this colour?

    Is that at all possible

    Or am i dreaming

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,212

    Re: How to use a list from Excel Sheet with Userform ComboBox

    You can't show multiple colours in a combobox - if you do apply a colour it'll apply to all the items in the list.

    I have seen code that will display the colour palette but I'm not sure how that would work with what you have.

    Actually I came up with this.
    Please Login or Register  to view this content.
    What that'll do is put the selected value from the combobox in the next empty cell in column A and pop up a dialog to allow the user to select the fill format.
    Last edited by Norie; 06-26-2014 at 07:56 AM.

  7. #7
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: How to use a list from Excel Sheet with Userform ComboBox

    So when the user fills out the form, is there no way he can select a colour so when he saves the form, all data imputed into the spreedsheet will have the same colour background in the filled cells?

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,212

    Re: How to use a list from Excel Sheet with Userform ComboBox

    See the edit I made to my previous post.

  9. #9
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: How to use a list from Excel Sheet with Userform ComboBox

    My form looks like this:

    Unbenannt.PNG

    The first 7 fields will be filled out by user, i would like him to select a Farbe (Colour) then when he clicks save, the data will be transfered into the relevent cells in the worksheet and get the relevent back ground colour

    With what you have done above would all cells be filled with the colour he selects or just one?

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,212

    Re: How to use a list from Excel Sheet with Userform ComboBox

    The data would be saved, then the user would pick the colour/pattern - I couldn't figure out how to do it the other way round.

    However it should be straightforward to apply the formatting to the 7 fields.

    What code are you currently using to put the data on the worksheet?

  11. #11
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: How to use a list from Excel Sheet with Userform ComboBox

    Ha Ha Norie,

    I havn't got that far yet. That is very complicated for me.........

    Unbenannt2.PNG

    From the picture:

    when Save is pressed the VBA needs to check Column A for Kunde and Mitarbeiter(s) so it knows what rows to update,
    Then it needs to find the start and end date.....

    Then it will fill in between start and end date every cell the Tatigkeit, in this case "IB" so the row containing the "Kunde" and "Mitarbeiters". i.e. every day will have "IB" in and also change these cells background.....

    Thats my next problem, i presume i will be using some sort of lookup code??

    Please Login or Register  to view this content.
    This will be fun

  12. #12
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: How to use a list from Excel Sheet with Userform ComboBox

    Wait i have simplified it a bit...

    Tabelle2

    Has the years and Kunden/Mitarbeiter in

    Column A Starting at A2 has the Days in
    Row 1 starting at C1 has the Kunden and Mitarbeiter

    So it need to search column A for Dates and Row one for Kunden and Mitarbeiters and where they cros in the table put the Tatikeit in....


    Ie:

    detering is in D1 and the Mitarbeiters Z1, AA1, AB1

    so would put the Tatigkeit in all cells in Z, AA, AB where the dates appears in column A

    Does that make sense??

  13. #13
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,212

    Re: How to use a list from Excel Sheet with Userform ComboBox

    Is this for a schedule?

    If it is then I think the first thing you need to do is locate the selected customer, if you the Kunden list is a contiguous range then that should be straightforward using the ListIndex of the combobox.

    Then you would need to find the columns for the start/end dates in the row of the select customers, again that might be straightforward depending on how the sheet is structured.

    Next you would, fill in the relevant columns in the selected customer's row with the IB or whatever is scheduled.

    Don't know what you are doing with the rest (workers?).

  14. #14
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: How to use a list from Excel Sheet with Userform ComboBox

    Tabelle2 attached:

    Column A are the dates
    Row 1 are the Kunden and Mitarbeiters(Workers)

    Basically when i click save it will check Row 1 for Kunden and Mitarbeiters.
    Check Column 1 for dates (Start Date to End Date) and put the Tatikeit (IB) in the cells where they all meet

    So under Detering for the selected dates would be IB and under all mitarbeiters selected would be IB for thos dates.

    Then my formule will copy them auto matically to the from sheet
    Attached Files Attached Files

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,212

    Re: How to use a list from Excel Sheet with Userform ComboBox

    Oh, so the previous image isn't the sheet that needs filled in?

  16. #16
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: How to use a list from Excel Sheet with Userform ComboBox

    Thought i would do it like this as the previous sheet is the master with lots of formule on, But the Tabelle2 is a straight forward table and would be easier to search and fill in

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] userform combobox to look up list in sheet
    By H_Kennedy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-21-2013, 02:30 AM
  2. [SOLVED] Make combobox list options dependant on selection in another combobox within a userform
    By Vladimir_Dobvchenko in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-23-2013, 05:30 AM
  3. [SOLVED] Userform ComboBox.List Location and Active Sheet Issues
    By AlvaroSiza in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-24-2013, 09:55 PM
  4. [SOLVED] Excel Userform: Populate other controls (i.e. textbox & combobox) based on combobox select
    By MileHigh_PhD in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-22-2013, 04:50 PM
  5. [SOLVED] How do I make a ComboBox use a list from another sheet in Excel?
    By ndm berry in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-05-2005, 11:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1