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! :?
Thank you in advance for your assistance with this.
File and Code below.
File Attached:
forum.ozgrid.com/index.php?attachment/73888/
Code
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
[COLOR=#3E3E3E][B]
[/B][/COLOR]
Display More