Hi All-
I am working on Userform to enter data to multiple worksheets and I was able to get that work. But, I want to take it further by being able to update previous entries through the Userform.
I have a Listbox that I am trying to Populate from all the worksheet and then select the entry and have the Textbox and combobox fill in by selecting an entry in the list. And be able to update and save new information.
After looking around I am able to populate the Listbox from a Single sheet but not from multiple sheets!
This could be impossible but, wanted to see someone had a solution!
Attached file and Code below:
Log-01.xlsm
Private Sub Commandbutton1_Click() Dim cNum As Integer Dim X As Integer Dim nextrow As Range Dim sht As String 'set the variable for the sheet sht = ComboBox1.Value 'check for values If Me.ComboBox1.Value = "" Then MsgBox "Select a sheet from the combobox and add the date" Exit Sub End If 'change the number for the number of controls on the userform cNum = 4 'add the data to the selected worksheet Set nextrow = Sheets(sht).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) For X = 1 To cNum nextrow = Me.Controls("Reg" & X).Value Set nextrow = nextrow.Offset(0, 1) Next 'clear the values in the userform For X = 1 To cNum Me.Controls("Reg" & X).Value = "" Next 'communicate the results MsgBox "The values have been sent to the " & sht & " sheet" End Sub Private Sub commandbutton2_Click() 'close the userform Unload Me End Sub Private Sub Label8_Click() End Sub Private Sub ListBox1_Click() Dim ws As Worksheet Dim say As Long, A As Byte For A = 0 To 11 Controls("textbox" & A + 1) = ListBox1.Column(A) Next Sheets("div01").Range("A:A").Find(ListBox1.Text).Activate say = ActiveCell.Row Sheets("div01").Range("A" & say & ":L" & say).Select 'TextBox15 = ListBox1.ListIndex + 1 End Sub Private Sub UserForm_Initialize() 'dim the variables Dim ws As Worksheet 'loop through worksheets For Each ws In Worksheets 'use the code name in case sheet name changes Select Case ws.CodeName 'exclude these sheets by code name Case "Sheet1" 'Add the rest Case Else Me.ComboBox1.AddItem ws.Name End Select Next ws ListBox1.ColumnWidths = "100;85;85;80" 'COLUMN WITH OF LISTBOX ListBox1.ColumnCount = 4 'COLUMN NUMBER OF LISTBOX ListBox1.List = Sheets("div01").Range("A3:l" & [a65536].End(3).Row).Value End Sub