retrieve and edit data from a combobox

[mscola]

I've created a combobox and filled the data with values from a named range, called "Kunden_mit_Adresse". Here is my code:

'Set reference to the range of data to be filled     
Set rngSourceKunde = Worksheets("Kunden").Range("Kunden_mit_Adresse") 

'Fill the listbox     
Set lbtarget = Me.lstKundenListe 

With lbtarget         
'Determine number of columns         
.ColumnCount = 6         
'Set column widths         
.ColumnWidths = "0;130;110;20;0;20"         

'Insert the range of data supplied         
.List = rngSourceKunde.Cells.Value              
End With

As you can see, I've created several textboxes. When a customer is selected from the combobox, I wish the data to be displayed in the textfields and be able to change the values (in the spreadsheet). See attached spreadsheet example.

My goal is that a customer's data can be changed with an Excel userform. Do I also need to know the row number? Have been on this for a long time.

The worksheet can be downloaded from http://ul.to/gyy0pi01

Thank you for your help

Massimo

 

[bertie]

Hi and welcome to the forum.

I was unable to download your file, so this is just some general advice. I have assumed the names of your text boxes.

You can set a Click event to your ListBox which populates the TextBoxes.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] lstKundenListe_Click()
   txtName.Value = Me.lstKundenListe.Column(0)
   txtStreet.Value = Me.lstKundenListe.Column(1)
   txtNumber.Value = Me.lstKundenListe.Column(2)
[COLOR=SeaGreen]'
   'etc
   '[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

The code for the Change command button will use the ListIndex property to determine which row to update on the spreadsheet.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cmdChange_Click()
   [COLOR=darkblue]Dim[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   
   [COLOR=green]'get the row from the list index[/COLOR]
   [COLOR=green]'list boxes have base zero so add 1[/COLOR]
   [COLOR=green]'add 1 for row1= header row[/COLOR]
   rw = Me.lstKundenListe[COLOR=black].[/COLOR][COLOR=black]ListIndex [/COLOR][COLOR=Red]+ 2[/COLOR]   [COLOR=green]'base 0 + 1 + HeaderRow = 2[/COLOR]
   
   [COLOR=green]'update the spreadsheet[/COLOR]
   [COLOR=darkblue]With[/COLOR] Sheets("Kunden")
      .Range("[COLOR=Red]A[/COLOR]" & rw).Value = txtName.Value
      .Range("[COLOR=Red]B[/COLOR]" & rw).Value = txtStreet.Value
      .Range("[COLOR=Red]C[/COLOR]" & rw).Value = txtNumber.Value
      [COLOR=green]'[/COLOR]
      'etc
      '
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
   
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps,
Bertie

 

[mscola]

Hello Bertie

Thanks for your reply and your code. I have no problems displaying the selected data in the forms but I cannot write the new value back to the sheet - maybe because I do not quite understand your code

As you can see on the picture, the headers are the first three rows of the spreadsheet. I've ammended the code, hoping that I may work but .. it doesn't:

   'get the row from the list index
   'list boxes have base zero so add 1
   'add 1 for row1= header row
   rw = Me.lstKundenListe.ListIndex + 3   'base 0 + 3 + HeaderRow = 4 ?
   
   'update the spreadsheet
   With Sheets("Kunden")
      .Range("B" & rw).Value = cmbAnrede.Value
      .Range("C" & rw).Value = txtKundenName.Value
      .Range("D" & rw).Value = txtAdresse.Value
      .Range("E" & rw).Value = txtStrassenNummer.Value
      .Range("F" & rw).Value = txtPLZ.Value
      .Range("G" & rw).Value = txtOrt.Value
      .Range("H" & rw).Value = txtTelefonPrivat.Value
      .Range("I" & rw).Value = txtTelefonGeschäft.Value
   End With

Unfortunately, I cannot upload the spreadsheet to this forum, hence the external link.

Thanks for your help

Massimo

 

[bertie]

Because your output starts on row 4 we need to take this into account.

Edit the caode as highlighted below:

   rw = Me.lstKundenListe.ListIndex + [COLOR=red]4[/COLOR]
   
 [COLOR=seagreen]  'update the spreadsheet
[/COLOR]   With Sheets("Kunden")