Hi
Nothing fancy here, so see how it goes.
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim BlankOne As Boolean
Set ws = Worksheets("AuditLog")
Application.ScreenUpdating = False
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for a Input
BlankOne = False
For i = 0 To Me.Controls.Count - 1
Select Case TypeName(Me.Controls(i))
Case "TextBox", "ComboBox"
If Len(Trim(Me.Controls(i))) = 0 Then
BlankOne = True
End If
Case Else
End Select
Next i
If BlankOne Then
MsgBox "Please Enter All Fields"
Exit Sub
End If
ws.Unprotect Password:="000"
'copy the data to the database
ws.Cells(iRow, "A").Value = Me.TextBox1.Value
ws.Cells(iRow, "B").Value = Me.TextBox2.Value
ws.Cells(iRow, "C").Value = Me.ComboBox1.Value
ws.Cells(iRow, "D").Value = Me.TextBox3.Value
ws.Cells(iRow, "E").Value = Me.TextBox4.Value
ws.Cells(iRow, "F").Value = Me.TextBox5.Value
ws.Cells(iRow, "G").Value = Me.ComboBox2.Value
ws.Cells(iRow, "H").Value = Me.ComboBox3.Value
ws.Protect Password:="000"
Set ws = Worksheets(ComboBox1.Value)
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
Worksheets(ComboBox1.Value).Unprotect Password:="000"
'copy the data to the active sheet
ws.Cells(iRow, "A").Value = Me.TextBox1.Value
ws.Cells(iRow, "B").Value = Me.TextBox2.Value
ws.Cells(iRow, "C").Value = Me.ComboBox1.Value
ws.Cells(iRow, "D").Value = Me.TextBox3.Value
ws.Cells(iRow, "E").Value = Me.TextBox4.Value
ws.Cells(iRow, "F").Value = Me.TextBox5.Value
ws.Cells(iRow, "G").Value = Me.ComboBox2.Value
ws.Cells(iRow, "H").Value = Me.ComboBox3.Value
ws.Protect Password:="000"
'clear the data
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.ComboBox1.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.ComboBox2.Value = ""
Me.ComboBox3.Value = ""
Me.TextBox1.SetFocus
Application.ScreenUpdating = True
End Sub
rylo
Bookmarks