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:
And the list is called "Kunden" and in "Tabelle4"
Please Login or Register to view this content.
Thank you for any help in advance
Regards
Jeremy
Last edited by JRidge; 07-01-2014 at 09:46 AM.
Try this.
Please Login or Register to view this content.
If posting code please use code tags, see here.
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??
Do you mean list the colours in a 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
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.
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.Please Login or Register to view this content.
Last edited by Norie; 06-26-2014 at 07:56 AM.
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?
See the edit I made to my previous post.
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?
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?
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??
This will be funPlease Login or Register to view this content.
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??
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?).
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
Oh, so the previous image isn't the sheet that needs filled in?
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks