Userform textbox populated with listbox data how to edit

[pure vito]

Hi all,

I have a userform with textboxes populated by a list box,

What I am hoping to achieve is to be able to edit any textbox and update the change to the data sheet "People" using a button,

Secondly I would like to populate the text boxes with new data and add to the same sheet the next available column,

If anyone can help it would be greatly appreciated here is all the code a currently have in use,

Many Thanks,

Private Sub UserForm_Activate()

Set sh = ThisWorkbook.Worksheets("People")
Dim iRow As Long
iRow = sh.Range("A" & Rows.Count).End(xlUp).Row
'to populate listbox  from Database sheet
   With Me.ListBox1
       .ColumnCount = 32
       .ColumnHeads = True
       .ColumnWidths = "120,100,60,80,100,60,100,120,50,80,80,80,80,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50"
       .RowSource = "People!A2:AF" & iRow
   End With


End Sub


Private Sub ListBox1_Click()
    Dim Col         As Long, SelectedRow As Long
    
    SelectedRow = Me.ListBox1.ListIndex + 2
    
    'Loads textboxes with selected range values
    For Col = 1 To 32
        Me.Controls("TextBox" & Col).Value = sh.Cells(SelectedRow, Col).Text
    Next Col
End Sub

Private Sub Clear_Click()
    Dim ctrl        As Control        ' CREATE A CONTROL OBJECT.
    ' LOOP THROUGH EACH CONTROL, CHECK IF THE CONTROL IS A TEXTBOX.
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Then
            ctrl.Value = ""        'CLEAR THE VALUE.
        End If
    Next ctrl
    Dim X           As Control
    For Each X In Me.Controls
        If TypeOf X Is MSForms.CheckBox Then X.Value = False
    Next
End Sub

 

[DanteAmor]

Hi @pure vito

A couple of notes.
1. I assume you have the sh object declared as a global variable.
2. The column width separator of the listbox in my case should be a semicolon.

Replace all your code with the following:

Option Explicit
  Dim sh As Worksheet     'At the beginning of all the code

Private Sub UserForm_Activate()
  Set sh = ThisWorkbook.Worksheets("People")
  Dim iRow As Long
  iRow = sh.Range("A" & Rows.Count).End(xlUp).Row
  'to populate listbox  from Database sheet
   With Me.ListBox1
       .ColumnCount = 32
       .ColumnHeads = True
       .ColumnWidths = "120;100;60;80;100;60;100;120;50;80;80;80;80;50;50;50;50;50;50;50;50;50;50;50;50;50;50;50;50;50;50;50"
       .RowSource = "People!A2:AF" & iRow
   End With
End Sub

Private Sub ListBox1_Click()
    Dim col         As Long, SelectedRow As Long
  
    SelectedRow = Me.ListBox1.ListIndex + 2
  
    'Loads textboxes with selected range values
    For col = 1 To 32
        Me.Controls("TextBox" & col).Value = sh.Cells(SelectedRow, col).Text
    Next col
End Sub

Private Sub Clear_Click()
    Dim ctrl        As Control        ' CREATE A CONTROL OBJECT.
    ' LOOP THROUGH EACH CONTROL, CHECK IF THE CONTROL IS A TEXTBOX.
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Then
            ctrl.Value = ""        'CLEAR THE VALUE.
        End If
    Next ctrl
    Dim X           As Control
    For Each X In Me.Controls
        If TypeOf X Is MSForms.CheckBox Then X.Value = False
    Next
End Sub

Add the following code to update the sheet. Adjust the name of the command button

Private Sub CommandButton1_Click()
  'UPDATE SHEET
  Dim iRow As Long, col As Long
 
  iRow = Me.ListBox1.ListIndex + 2
 
  'Loop through each Textbox
  For col = 1 To 32
    sh.Cells(iRow, col).Value = Me.Controls("TextBox" & col).Value
  Next col
End Sub

Secondly I would like to populate the text boxes with new data and add to the same sheet the next available column,

I did not understand that requirement. Could you explain it in more detail? I did not understand if it is a new column or a new record in a new row of the sheet.


I hope to hear from you very soon.
Cordially
Dante Amor

 

[pure vito]

Hi Dante,

Thank you very much for taking the time out to look at this for me, It seems to be working great editing the first column A works flawlessly but it doesn't seem to be working when trying to update other textboxes if that makes sense, I have added link to the document, to bring up the userform it's on sheet "Home" press the add employee 2 button,

yes sorry the second thing I wanted to add is the ability to add new employees by filling the text boxes manually with new data and then having the data transfer the the people tab on a new row,

Launch Holiday Tracker 2024 - Example - f.xlsm

 

[DanteAmor]

It seems to be working great editing the first column A works flawlessly but it doesn't seem to be working when trying to update other textboxes if that makes sense

It works for all columns. First you must select an item from the listbox.


To add a new record, add the following code.

Private Sub CommandButton2_Click()
'ADD NEW RECORD
  Dim lr As Long, col As Long
  
  If TextBox1.Value = "" Then
    MsgBox "Enter name", vbCritical
    TextBox1.SetFocus
    Exit Sub
  End If
    
  lr = sh.Range("A" & Rows.Count).End(3).Row + 1
  For col = 1 To 32
    sh.Cells(lr, col).Value = Me.Controls("TextBox" & col).Value
  Next col
  
  MsgBox "Record added"
End Sub

 

[pure vito]

Thank you Dante that works great when adding new records,

I am still having issues with the update sheet code, I'm selecting the the row I want to edit but it only seems to be changing the first textbox I've tried multiple times I can't get it to update any other textbox I'm not sure what I'm missing but again I'm very grateful that you have got me this far

 

[DanteAmor]

I am still having issues with the update sheet code

I added the global variable "updating" to control when you are updating the sheet.

Use the following code:

Option Explicit
Option Base 1

Dim Lastrow As Long
Dim sh As Worksheet
Dim updating As Boolean

Private Sub Update_Click()
'UPDATE SHEET
  Dim iRow As Long, col As Long
 
  updating = True
  
  iRow = Me.ListBox1.ListIndex + 2
  
  'Loop through each Textbox
  For col = 1 To 32
    sh.Cells(iRow, col).Value = Me.Controls("TextBox" & col).Value
  Next col
  
  updating = False
End Sub

Private Sub CommandButton2_Click()
'ADD NEW RECORD
  Dim lr As Long, col As Long
  
  If TextBox1.Value = "" Then
    MsgBox "Enter name", vbCritical
    TextBox1.SetFocus
    Exit Sub
  End If
    
  lr = sh.Range("A" & Rows.Count).End(3).Row + 1
  For col = 1 To 32
    sh.Cells(lr, col).Value = Me.Controls("TextBox" & col).Value
  Next col
  
  MsgBox "Record added"
End Sub

Private Sub ListBox1_Click()
  Dim col         As Long, SelectedRow As Long
  
  If updating = True Then Exit Sub
  
  SelectedRow = Me.ListBox1.ListIndex + 2

  'Loads textboxes with selected range values
  For col = 1 To 32
    Me.Controls("TextBox" & col).Value = sh.Cells(SelectedRow, col).Text
  Next col
End Sub

Private Sub Clear_Click()
  Dim ctrl        As Control        ' CREATE A CONTROL OBJECT.
  ' LOOP THROUGH EACH CONTROL, CHECK IF THE CONTROL IS A TEXTBOX.
  For Each ctrl In Me.Controls
      If TypeName(ctrl) = "TextBox" Then
          ctrl.Value = ""        'CLEAR THE VALUE.
      End If
  Next ctrl
  Dim X           As Control
  For Each X In Me.Controls
      If TypeOf X Is MSForms.CheckBox Then X.Value = False
  Next
End Sub

Private Sub UserForm_Activate()
  Set sh = ThisWorkbook.Worksheets("People")
  Dim iRow As Long
  iRow = sh.Range("A" & Rows.Count).End(xlUp).Row
  'to populate listbox  from Database sheet
   With Me.ListBox1
       .ColumnCount = 32
       .ColumnHeads = True
       .ColumnWidths = "120,100,60,80,100,60,100,120,50,80,80,80,80,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50"
       .RowSource = "People!A2:AF" & iRow
   End With
End Sub

Notas:
1. For the update, the sheet must not be filtered.

2. You should review this code, since when you modify cell B4 of the sheet it is executed and then the result of the sheet update may be unexpected.

'Private Sub Worksheet_Change(ByVal Target As Range)
'Application.ScreenUpdating = False
'  If Target.Address = "$B$4" Then
'    If ActiveSheet.FilterMode Then ShowAllData
'    If Len(Target.Value) > 0 Then Range("C1").CurrentRegion.AutoFilter Field:=1, Criteria1:="*" & Target.Value & "*"
'    Application.ScreenUpdating = True
'  End If
'End Sub

I attach the file with the code in userform "Nameinput" working.
file

 

[pure vito]

Like a dream Dante, that works perfectly, I'm always envious of the knowledge here, thank you very much for taking the time to do this it's very much appreciated, onto the next one