How to use a list from Excel Sheet with Userform ComboBox

[JRidge]

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

[Norie]

Try this.

Please Login or Register  to view this content.

[JRidge]

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??

[Norie]

Do you mean list the colours in a combobox?

[JRidge]

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

[Norie]

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.

[JRidge]

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?

[Norie]

See the edit I made to my previous post.

[JRidge]

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?

[Norie]

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?

[JRidge]

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

[JRidge]

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??

[Norie]

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?).

[JRidge]

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

[Norie]

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

[JRidge]

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