Something strange with listbox

[dotchieJack]

Hello dear helpers,
When i click in the listbox then the textboxes and comboboxes should be filled with the right data to change but in this included example when i click on "speler8 it gives me the data of player 2 or when I click on player 3 it gives me the header
And i can not find out why
Any help welcome

 

[Nebu]

Hi:

Can you put some dummy data in there. If run the macro now the list box is completely blank. It would be easier to test the code with some data in.

Thanks

 

[dotchieJack]

Hi,
With some data in it, when you click in the listbox , the form is filled with wrong data and i cannot find out why?

 

[Nebu]

Hi:

Use the following code.

Private Sub LoadRow()
    TextBox1.Text = CDate(ListBox1.List(lCurrentRow, 0))
    ComboBox1.Text = ListBox1.List(lCurrentRow, 1)
    ComboBox2.Text = ListBox1.List(lCurrentRow, 2)
    ComboBox3.Text = ListBox1.List(lCurrentRow, 3)
    ComboBox4.Text = ListBox1.List(lCurrentRow, 4)
    TextBox2.Text = ListBox1.List(lCurrentRow, 5)
    End Sub

Thanks

 

[dotchieJack]

Hi Nebu
Works great, thank you for the solution and fast reply.
Problem solved

 

[dotchieJack]

Hi Nebu
Can you have another look at the file pls
When I want to edit an entry and I hit the ingave aanpassen I get errorsinthis piece of code

Private Sub SaveRow()
  Cells(lCurrentRow, 1).Value = TextBox1.Text
  Cells(lCurrentRow, 2).Value = ComboBox1.Text
  Cells(lCurrentRow, 3).Value = ComboBox2.Text
  Cells(lCurrentRow, 4).Value = ComboBox3.Text
  Cells(lCurrentRow, 5).Value = ComboBox4.Text
  Cells(lCurrentRow, 6).Value = TextBox2.Text
End Sub

Thanks

 

[Nebu]

Hi:

The issue is with your lCurrentRow, the lCurrentRow is the list count and you are linking it with the row count on your spreadsheet, hence the mismatch. If you try to run the code step-by-step and add watch to your lCurrentRow you can see why the rows are off. If you are unable to sort this out at your end let me know. I will help you out.

Thanks

 

[dotchieJack]

Hi Nebu
Thank you for your reply.
I have been trying al kind of thinks, but nothing works, so yes every suggestion is welcome

Thanks

 

[Nebu]

Hi:

I am afraid you will be able to update the database this way since the row source of the list box is already defined. whenever you load data into the user form using the list box it is predefined and even if you try to force new values in to the excel sheet it will reset to the old values. In short, you will have to update the list box to get the excel sheet updated not just the text or combo boxes on the user form, and since you have already defined the row source for the list box it is not possible.

Thanks

 

[Marc L]

Hi !

It's easier to not use an UserForm but the inner dataform, an EXCEL basics …
(see ShowDataForm method in VBA help)
​

 

[dotchieJack]

Hello Nebu and Marc,
Thank you for the reply's.
@Marc, I know about the dataform method, but this is not not what I am looking for right now, but thanks
@ Nebu, I am reworking the whole thing and I come back to this tread with a solution or other problems
Thanks again

 

[dotchieJack]

Hello dear helpers,
I reworked the file, everything is working fine execpt I dont find the solution to delete the entire row afther choosing an entry in the listbox
buttonname is cmdDelete
Can somebody have a look at it please.
File included

 

[Nebu]

Hi:

One way of doing this is assigning a unique id to your row items and use the find method in vba based on this unique ID and delete that row.

Find the attached.

Thanks

 

[dotchieJack]

Hoi Nebu,
Everything works great now, thank you so much for your help
Have a nice day

 

[dotchieJack]

Hi Nebu,
I need some advice again. I changed the Unique Id colomn from G to A. When i click the listbox now the 2 buttons don't become enabled. Can you please help me out again?

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[dotchieJack]

Hello Nebu,
The new adjustments are working great, thanks for that.
Now that the buttons work i discovered another problem
When I try to adjust something (cmdUpdate) buttonname [Ingave aanpassen] I only get the messagebox and I can not change/update an entry.May I ask you to have a look at this last problem. I know you already have a lot of patience with me, but when this is solved the file is ready to go.
Thank you so much
(file is the same as post#16, nothing is changed)

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[dotchieJack]

Hoi Nebu,
Thank you so much for your patience and solutions.
It is working great and the problem is solved.
No it is up to me to compare the codes so and learn from it