VBA userform

[luukos97]

Hello,

I am trying to build an Userform for my excel worksheet. Hopefully I can explain the case clear enough for your understandings.

The case (see excel worksheet attached):
I have currently 6 columns which two of them are already filled with parameters. The Userform should be serve as tool for other persons.
I want to have the column "Partname" (A) as listbox, the column "Quantity" (B) is a fixed number. Column C, D, E and F should be filled by the userform.

I have tried to write the code by searching on the Internet and using Youtube video's, but unfortunately it is not working (yet)... As well I tried to add a code for saying "Hallo" if the worksheet opens, but this one is also not working

Hopefully someone with more experience in Userforms can help me with this case.


Thanks.

[Logit]

.
Here is a small project for you to study and incorporate into yours :

Please Login or Register  to view this content.

See the attached workbook for the above.

Also, see your attached with the amended code for initiating the "Hello" msgbox upon opening.

[luukos97]

Option Explicit
Private Sub btnCancel_Click()
Unload Me
End Sub

Private Sub btnOK_Click()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim newRow As Long

newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1

'The next two lines can be expanded as many times as needed for all the entry fields in your project

ws.Cells(newRow, 1).Value = Me.txtFirstName.Value
ws.Cells(newRow, 2).Value = Me.txtSurname.Value

End Sub
Sub CommandButton1_Click()
Selection.EntireRow.Delete
End Sub

I do understand this code more or less. But I do not understand how to insert the Listbox option. In my case I have 5 already given partnames (Column A), this should be
processed into the listbox option.

Thanks for sending me the sample Logit, it gives a clear overview of the sample and how to use it for that Userform.

[Logit]

.

Please Login or Register  to view this content.

[luukos97]

.

Please Login or Register  to view this content.

This code is ending in an error at my excel worksheet. Besides, I do not prefer having a new row, it should fill the already cells in the rows where the partnames are located. That is the code I am looking for.

[Logit]

.
Where is the error ?

[luukos97]

.
Where is the error ?

It close the excel worksheet. I think because the VBA is want to add a row but the worksheet sees a table?

[Logit]

See if this does what you are seeking :

Please Login or Register  to view this content.

[luukos97]

See if this does what you are seeking :

Please Login or Register  to view this content.

Thank you Logit. I will test it tomorrow morning, I have just put my pc in sleep mode.

[luukos97]

See if this does what you are seeking :

Please Login or Register  to view this content.

It works Logit! Thank you. Now I will try to adjust it a little more as I did some new inspiration during my sleep :P. Maybe I will come up with some questions later on. I will keep this thread up to date.

[luukos97]

Update:

I have added to the code that if the value is filled and ENTER is pressed, the Textboxes will be cleared. Now I am trying to remove the Listbox Item if it is inserted into the worksheet (see attached).

But it is not working yet, can somebody help me what is wrong in the code?

Please Login or Register  to view this content.

[torachan]

I noticed in your original post you had entered your data into a table.
I therefore have to question why then not use table referencing instead of the cumbersome range referencing you are using.
With tables they are elastic, you do not constantly have to check last row etc.
Also once you have loaded the listbox there is no need to search the worksheet for matches as you already have the row reference within the listbox.
e.g. the listbox.listindex (note compensate for this been zero-based) makes add/delete/update code a lot simpler and direct.
Note the listbox is populated from the table array not the unstable rowsource.
torachan.

[luukos97]

I noticed in your original post you had entered your data into a table.
I therefore have to question why then not use table referencing instead of the cumbersome range referencing you are using.
With tables they are elastic, you do not constantly have to check last row etc.
Also once you have loaded the listbox there is no need to search the worksheet for matches as you already have the row reference within the listbox.
e.g. the listbox.listindex (note compensate for this been zero-based) makes add/delete/update code a lot simpler and direct.
Note the listbox is populated from the table array not the unstable rowsource.
torachan.

Who, this is cool Torachan, I am going to play and adjust a little bit. Thanks

[luukos97]

Okay, now I have problems with inserting the code to my own excel xlsm file :S

Is it possible to convert a sheet into another xlsm file?

Otherwise I think I will proceed with the solution of Logit, this one is almost working now... I think I was a little bit too enthusiatic, the code is with my knowlegde a bit complex haha

Let me know

[torachan]

Is it possible to convert a sheet into another xlsm file?

I am not sure what you want to do.
I only submitted the solution as I could not understand why bother with a table and not use table referencing.
Also the unnecessary search methods when the location of data is already known.
Rather than cause confusion it would be best to follow your present path albeit rather disjointed.
torachan.

[Logit]

.
Is this what you are seeking ?

Please Login or Register  to view this content.

[luukos97]

.
Is this what you are seeking ?

Please Login or Register  to view this content.

Hi Logit,

No, what I trying to add to the userform is vizualised in this video, See minute 9:00. :

For example in my case. When I have selected Partname "Rod" with the given times and press ENTER, the partname "Rod" should be cleared. But I tried to insert the code from the video but encouter the error that "x" was not defined...

Hopefully this elucidate the request.

Let me know

[Logit]

.
Sorry ... I am not certain how that would be accomplished.

Someone else might be able to assist.

[luukos97]

No problem. Thanks for your help in this thread

[torachan]

Re-run the youtube clip again, the listbox is initially loaded from a second sheet (Sheet2) not Sheet1, Sheet2 stays intact all the time for reloading.
Sheet1 has no initial content, this is loaded with the items been deleted from the listbox.
An altogether pointless exercise in the real world.
torachan.

[luukos97]

Re-run the youtube clip again, the listbox is initially loaded from a second sheet (Sheet2) not Sheet1, Sheet2 stays intact all the time for reloading.
Sheet1 has no initial content, this is loaded with the items been deleted from the listbox.
An altogether pointless exercise in the real world.
torachan.

So if I have understood you correct, this video isn't actual deleting the options from the listbox but deleting the options in sheet 2?

Then, is there a workaround for the demaning I have?

[torachan]

No, No.
Sheet2 remains intact all the time - it is the source of the data for the listbox.
The data in the listbox is removed from the listbox as it is transfered to Sheet1.
This can only be done until the listbox is empty - then the form will have to be reopened (initialised) then the listbox is repopulated.

[luukos97]

Please Login or Register  to view this content.

I am trying to add this to my VBA but getting the following error: "Compile error: Expected variable or procedure, not module".

The Listbox looks like this:

Please Login or Register  to view this content.

What am I missing?

[torachan]

Upload your workbook, without the surrounding context it is impossible to give an answer.

[luukos97]

Upload your workbook, without the surrounding context it is impossible to give an answer.

Sorry Torachan.

What I try now is to add information in table1 (at sheet2). This should be displayed in the Listbox at the userform.

See attached.

[torachan]

You had confused VBA by naming the module the same as the procedure (Sub) that was been looked for.
Also you need to populate the listbox via the UserForm initialization event.
When you increase the listbox column count also increase the number of column widths.
Even if they are set at zero the data will be present, a feature you will come to exploit as you gain experience.

[luukos97]

You had confused VBA by naming the module the same as the procedure (Sub) that was been looked for.
Also you need to populate the listbox via the UserForm initialization event.
When you increase the listbox column count also increase the number of column widths.
Even if they are set at zero the data will be present, a feature you will come to exploit as you gain experience.

This one works properly for me Thanks Torachan, I have set this thread to solved.