Combobox Help

[hfuwifnvjk]

Hello

I'm using Excel 2003. I'm a VBA beginner..
I want to create a userform and I need support. Google sent me to you

I have 2 worksheets. One of them is for the "Database" and the other one is for "EnteredData".

On a userform I want to select or write in a combobox the german postal code and the other combobox should automatically show the city.

When I click the button "Enter", the selected zip code should be entered in column F and the city should be entered in coulumn G.

If there are several places on this postal code, just this cities should be displayed on the opened combobox.
If there are not one of the entered postal code, the newly registered postal code and newly registered place should be added on the sheet "database" and sorted by the postal code.

For your help I would be very grateful.

As Annex I add the userform (with code) with the addition of the 2 Sheets.
Download here: help.xls

[MarvinP]

hi hfuwifnvjk and welcome to the forum,

Have you looked at using a Data Validation dropdown and a VLookup for the cities? This would not need a userform or ComboBox and might be just what you want.

[hfuwifnvjk]

Hello Marvin,

thank you very much

I can do this with data validation, but its for an other userform, where I need this Comboboxes.

If you write manually in column A, you get that, what I want on the sheet (like data validation, which is programmed on this sheet).

I don't know,
1. How to do Private Sub Worksheet_Change(ByVal Target As Range) on the userform?
2. How can I sort the PLZ (postal code) in the sheet PLZ?
3. How can I avoid double entries?

[hfuwifnvjk]

Please just open the file and you will understand what I want to do..
My explanation isn't very well here.

[MarvinP]

Hi,

I'd do some vba code on the PLZ sheet to sort and filter your list separate from the UserForm code.

See the attached where I've named a range of data on the PLZ sheet named "PostalRange" and used this as the RowSource of the ComboBox1. I think this is the part you are missing. I removed all your code in the attached so you could see how the RowSource Property set correctly works.

I'd do a little code using the On_Change of ComboBox1 to do a VLookup to fill in the State/Ort associated with the Zip.

I hope this leads you to a better solution.

[hfuwifnvjk]

Hi,

sorry but this isn't the part I'm missing.
The RowSource is working at this Userform too, but what isn't working is to write just once time the new entered data in the database sheet PLZ and sort there.
Try now again, please: help.xls

[MarvinP]

OK -
I've recorded a macro to do an advanced unique filter to columns I and J and then sorted the filtered data and pasted it back to columna A and B.

I added a single variable so it would work for any length of data in column A of LastRow.
I then added a button to call the macro. It can be a lot cleaner but just wanted to show how I'd do your request.

[hfuwifnvjk]

Thank you Marvin,

do you know how I can write in the database sheet plz without double entries?

[MarvinP]

HEY -
The code I gave you in the last answer should filter the data so it is unique!!!

Run that again. Do you want to have unique codes or city names or both?

[hfuwifnvjk]

I want to have unique codes and city- both..

but I want to fill the combobox from the database sheet plz and write there the new one.

Now It works, but the combobox fills it from the wrong sheet..

[MarvinP]

Hi,

Fillls it from the wrong sheet?

I believe the Advanced Filter using the Unique check box does give Codes and City - Both!!

When a Userform is open, it must be closed to continue on. You might be able to trick this by doing an Application.ScreenUpdate = False, then close the userform and open it again then screenupdate = true.

You might also look at the Refresh command/property/method.

[hfuwifnvjk]

Very nice of you

I mean the Combobox should be filled with the data of the sheet plz, and not tabelle1.
when you delete the cells of the sheet plz, it doesn't work, because it is not filled from the sheet plz

the sheet tabelle1 should be empty, but then the comboboxes don't get filled
I hope you understand me

[MarvinP]

Look at the RowSource Property of the ComboBox1 on the userform. It takes it data from a NAMED RANGE that I've created on the PLZ sheet.

Does that work for you?

[hfuwifnvjk]

Thank you for all your help Marvin