Configure Code of a big Project Userform

[officeexcel2003]

<hr style="color:#ebebeb; background-color:#ebebeb" size="1"> Hello Everybody,
could you help me to configure this userform for Excel2003, please?

I want to use this userform but I don't know VBA.
Download here!

Thank you very much.

I've tried to get help at an other forum but there couldn't be helped to me.

I would be very happy if you would paste your code at the same file or an other excel 2003 file.

 

[officeexcel2003]

I don't understand the code and I don't think that it has been written professionally by my colleague.
So I would be very lucky if you as professional would write the code of the userform as you think it is right.

 

[officeexcel2003]

Hello everybody,
Norie has programmed this userform new and perfectly.

Thank you very much for this great job, Norie
​

But there are still some things that need to be supplemented.
Can someone help me who knows VBA programming?

This are the things what has to be supplemented:

o With the button delete happens nothing

o With search happens nothing

o Stammnummer (ID):

• If this number exists: msg for me: this number exists already, do you want to replace?

If I klick yes: the number should be replaced and the already existing data with this number should be the newest number.
If I klick no: This enter should be the newest number.​

• It shouldn't be possible to enter new data without Stammnummer, or letters or Zero or less then Zero.

o PLZ (postal code):
Every Ort (City) has its own (PLZ) postal code.
But a City can belong more then one postal codes.
One PLZ(postal code) belongs to one Ort (City).

• The manual entering of postal code and city should be possible.

• Is this possible on the userform?

- If postal code is chosen, the city of this postal code should appear on the userform automatically.
​

- If city is chosen, the postal code of this city should appear on the userform automatically.
​

o It shouldn't be possible to klick on empty lines on the listbox:
If I klick on a empty I can change this, it shouldn't be possible to klick the empty lines or to change them:

 

[Norie]

That's quite a lot to ask for.

I don't really have time to go through the whole list of requirements but I can tell you how to fix the problem of empty items in the listbox.

Change the loop in the PopulateLB sub to this:

    For I = 0 To noA - 5

This takes into account that on the worksheet the data starts in row 5.

Some of the other things you mention are left over from the original userform/code.

The Ort/PLZ thing is totally different.

As far as I could tell that sort of functionality wasn't available in the original and in the workbook the only relationship between Ort and PLZ was one-to-one.

That means one Ort, one PLZ and vice versa.

There was nothing there that indicated differently.

Perhaps someone who knows VBA programming can help you further.

 

[officeexcel2003]

Hello Norie,

thank you very much for your answer.
I am aware that you know VBA programming and can help me But you're right, this are a lot to ask for and for this reason I was ashamed to ask you alone for help.
You've done so much for me. I'm sorry if you feel hurt I feel bad therefore.

Thank you very much for the correction of the empty items in the listbox.

The Ort/PLZ thing is not important and it just occured to me during the work with the userform

 

[Norie]

No problem.

I think I can see what you mean about the Ort/PLZ thing but I don't quite see how it works.

 

[officeexcel2003]

No problem.

I think I can see what you mean about the Ort/PLZ thing but I don't quite see how it works.

Hello Norie,
I can't thank you enough
You are so awesome with all your help.
This is not important and it just was an idea

 

[officeexcel2003]

I have found here a code for the delete button. It works on the raw, which is activated but i can't select on the listbox what i want to delete.
What is happening with this code and why it is not working with the listbox?

Dim c          As Range

Private Sub cmdDelete_Click()
    Dim msgResponse As String    'confirm delete
    Application.ScreenUpdating = False
    'get user confirmation
    msgResponse = MsgBox("This will delete the selected record. Continue?", _
                         vbCritical + vbYesNo, "Delete Entry")
    Select Case msgResponse    'action dependent on response
        Case vbYes
            'c has been selected by Find button
            Set c = ActiveCell
            c.EntireRow.Delete    'remove entry by deleting row
            'restore form settings
        Case vbNo
            Exit Sub    'cancelled
    End Select
    Application.ScreenUpdating = True
End Sub

 

[Norie]

Sorry I don't know what you mean.

What's the 'raw'?

In fact what do you want the delete button (and search button) to do?

 

[officeexcel2003]

Hi Norie

I'm sorry, it's not the raw (like unboiled ) I mean the row/line.
I want to delete the hole data of a entered person (as a sample: Line4).

With the search button I want to search (filter) for the data "Vorname", "Nachname" or "Behörde/Firma".
If I write "Eva" the Listbox would show the "Vorname" "Eva".

Best greetings..

 

[Norie]

I'll take a look at it.

By the way I've had to take down the file - only a limited no of downloads are allowed or something.