A
andrew.gomez
only started to learn / use VBA code.
working on a userform for a data entry project. the data from the
userform gets updated onto a database contained within the same
worksheet (though hidden).
here is my code:
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for Triage Time In
If Trim(Me.TextBox21.Value) = "" Then
Me.TextBox21.SetFocus
MsgBox "Please Enter Information into Triage Time In"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox21.Value
ws.Cells(iRow, 2).Value = Me.ComboBox15.Value
ws.Cells(iRow, 5).Value = Me.ComboBox12.Value
ws.Cells(iRow, 6).Value = Me.TextBox36.Value
ws.Cells(iRow, 7).Value = Me.TextBox35.Value
ws.Cells(iRow, 8).Value = Me.TextBox34.Value
ws.Cells(iRow, 9).Value = Me.TextBox33.Value
ws.Cells(iRow, 10).Value = Me.ComboBox13.Value
ws.Cells(iRow, 11).Value = Me.ComboBox11.Value
ws.Cells(iRow, 12).Value = Me.ComboBox23.Value
ws.Cells(iRow, 13).Value = Me.ComboBox21.Value
ws.Cells(iRow, 14).Value = Me.ComboBox14.Value
ws.Cells(iRow, 15).Value = Me.TextBox20.Value
ws.Cells(iRow, 16).Value = Me.ComboBox19.Value
ws.Cells(iRow, 17).Value = Me.ComboBox22.Value
ws.Cells(iRow, 18).Value = Me.ComboBox16.Value
ws.Cells(iRow, 19).Value = Me.TextBox18.Value
ws.Cells(iRow, 20).Value = Me.ComboBox17.Value
ws.Cells(iRow, 21).Value = Me.ComboBox24.Value
ws.Cells(iRow, 22).Value = Me.ComboBox25.Value
ws.Cells(iRow, 23).Value = Me.TextBox17.Value
ws.Cells(iRow, 24).Value = Me.TextBox39.Value
ws.Cells(iRow, 25).Value = Me.TextBox19.Value
ws.Cells(iRow, 26).Value = Me.ComboBox18.Value
ws.Cells(iRow, 3).Value = Me.ComboBox20.Value
ws.Cells(iRow, 27).Value = Me.TextBox15.Value
ws.Cells(iRow, 28).Value = Me.ComboBox10.Value
ws.Cells(iRow, 29).Value = Me.TextBox26.Value
ws.Cells(iRow, 30).Value = Me.TextBox28.Value
ws.Cells(iRow, 49).Value = Me.TextBox40.Value
'clear the data
Me.ComboBox14.Value = ""
Me.TextBox21.Value = ""
Me.ComboBox15.Value = ""
Me.ComboBox12.Value = ""
Me.TextBox36.Value = ""
Me.TextBox35.Value = ""
Me.TextBox34.Value = ""
Me.TextBox33.Value = ""
Me.ComboBox13.Value = ""
Me.ComboBox11.Value = ""
Me.ComboBox23.Value = ""
Me.ComboBox21.Value = ""
Me.TextBox20.Value = ""
Me.ComboBox19.Value = ""
Me.ComboBox22.Value = ""
Me.ComboBox16.Value = ""
Me.TextBox18.Value = ""
Me.ComboBox17.Value = ""
Me.ComboBox24.Value = ""
Me.ComboBox25.Value = ""
Me.TextBox17.Value = ""
Me.TextBox39.Value = ""
Me.TextBox19.Value = ""
Me.ComboBox18.Value = ""
Me.ComboBox20.Value = ""
Me.TextBox15.Value = ""
Me.ComboBox10.Value = ""
Me.TextBox26.Value = ""
Me.TextBox28.Value = ""
Me.TextBox40.Value = ""
Me.ComboBox14.SetFocus
End Sub
Private Sub cmdClear_Click_Click()
Me.ComboBox14.Value = Null
Me.TextBox21.Value = Null
Me.ComboBox15.Value = Null
Me.ComboBox12.Value = Null
Me.TextBox36.Value = Null
Me.TextBox35.Value = Null
Me.TextBox34.Value = Null
Me.TextBox33.Value = Null
Me.ComboBox13.Value = Null
Me.ComboBox11.Value = Null
Me.ComboBox23.Value = Null
Me.ComboBox21.Value = Null
Me.TextBox20.Value = Null
Me.ComboBox19.Value = Null
Me.ComboBox22.Value = Null
Me.ComboBox16.Value = Null
Me.TextBox18.Value = Null
Me.ComboBox17.Value = Null
Me.ComboBox24.Value = Null
Me.ComboBox25.Value = Null
Me.TextBox17.Value = Null
Me.TextBox39.Value = Null
Me.TextBox19.Value = Null
Me.ComboBox18.Value = Null
Me.ComboBox20.Value = Null
Me.TextBox15.Value = Null
Me.ComboBox10.Value = Null
Me.TextBox26.Value = Null
Me.TextBox28.Value = Null
Me.TextBox40.Value = Null
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "To ensure you want to terminate this Program Please use
CLOSE tab located below"
End If
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Code:
--------------------------
very messing but does what i need it to at the moment.
i have 3 command tabs (update / close / clear all)
i want to add another command tab to search the (hidden) database then
populate the textbox's / combobox's if possible.
then the user can back track through what has already been enter then
make changes as and where needed.
can anyone point me in the right direction or save me from myself
any help will be greatly appreciated.
thanks
Andrew
working on a userform for a data entry project. the data from the
userform gets updated onto a database contained within the same
worksheet (though hidden).
here is my code:
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for Triage Time In
If Trim(Me.TextBox21.Value) = "" Then
Me.TextBox21.SetFocus
MsgBox "Please Enter Information into Triage Time In"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox21.Value
ws.Cells(iRow, 2).Value = Me.ComboBox15.Value
ws.Cells(iRow, 5).Value = Me.ComboBox12.Value
ws.Cells(iRow, 6).Value = Me.TextBox36.Value
ws.Cells(iRow, 7).Value = Me.TextBox35.Value
ws.Cells(iRow, 8).Value = Me.TextBox34.Value
ws.Cells(iRow, 9).Value = Me.TextBox33.Value
ws.Cells(iRow, 10).Value = Me.ComboBox13.Value
ws.Cells(iRow, 11).Value = Me.ComboBox11.Value
ws.Cells(iRow, 12).Value = Me.ComboBox23.Value
ws.Cells(iRow, 13).Value = Me.ComboBox21.Value
ws.Cells(iRow, 14).Value = Me.ComboBox14.Value
ws.Cells(iRow, 15).Value = Me.TextBox20.Value
ws.Cells(iRow, 16).Value = Me.ComboBox19.Value
ws.Cells(iRow, 17).Value = Me.ComboBox22.Value
ws.Cells(iRow, 18).Value = Me.ComboBox16.Value
ws.Cells(iRow, 19).Value = Me.TextBox18.Value
ws.Cells(iRow, 20).Value = Me.ComboBox17.Value
ws.Cells(iRow, 21).Value = Me.ComboBox24.Value
ws.Cells(iRow, 22).Value = Me.ComboBox25.Value
ws.Cells(iRow, 23).Value = Me.TextBox17.Value
ws.Cells(iRow, 24).Value = Me.TextBox39.Value
ws.Cells(iRow, 25).Value = Me.TextBox19.Value
ws.Cells(iRow, 26).Value = Me.ComboBox18.Value
ws.Cells(iRow, 3).Value = Me.ComboBox20.Value
ws.Cells(iRow, 27).Value = Me.TextBox15.Value
ws.Cells(iRow, 28).Value = Me.ComboBox10.Value
ws.Cells(iRow, 29).Value = Me.TextBox26.Value
ws.Cells(iRow, 30).Value = Me.TextBox28.Value
ws.Cells(iRow, 49).Value = Me.TextBox40.Value
'clear the data
Me.ComboBox14.Value = ""
Me.TextBox21.Value = ""
Me.ComboBox15.Value = ""
Me.ComboBox12.Value = ""
Me.TextBox36.Value = ""
Me.TextBox35.Value = ""
Me.TextBox34.Value = ""
Me.TextBox33.Value = ""
Me.ComboBox13.Value = ""
Me.ComboBox11.Value = ""
Me.ComboBox23.Value = ""
Me.ComboBox21.Value = ""
Me.TextBox20.Value = ""
Me.ComboBox19.Value = ""
Me.ComboBox22.Value = ""
Me.ComboBox16.Value = ""
Me.TextBox18.Value = ""
Me.ComboBox17.Value = ""
Me.ComboBox24.Value = ""
Me.ComboBox25.Value = ""
Me.TextBox17.Value = ""
Me.TextBox39.Value = ""
Me.TextBox19.Value = ""
Me.ComboBox18.Value = ""
Me.ComboBox20.Value = ""
Me.TextBox15.Value = ""
Me.ComboBox10.Value = ""
Me.TextBox26.Value = ""
Me.TextBox28.Value = ""
Me.TextBox40.Value = ""
Me.ComboBox14.SetFocus
End Sub
Private Sub cmdClear_Click_Click()
Me.ComboBox14.Value = Null
Me.TextBox21.Value = Null
Me.ComboBox15.Value = Null
Me.ComboBox12.Value = Null
Me.TextBox36.Value = Null
Me.TextBox35.Value = Null
Me.TextBox34.Value = Null
Me.TextBox33.Value = Null
Me.ComboBox13.Value = Null
Me.ComboBox11.Value = Null
Me.ComboBox23.Value = Null
Me.ComboBox21.Value = Null
Me.TextBox20.Value = Null
Me.ComboBox19.Value = Null
Me.ComboBox22.Value = Null
Me.ComboBox16.Value = Null
Me.TextBox18.Value = Null
Me.ComboBox17.Value = Null
Me.ComboBox24.Value = Null
Me.ComboBox25.Value = Null
Me.TextBox17.Value = Null
Me.TextBox39.Value = Null
Me.TextBox19.Value = Null
Me.ComboBox18.Value = Null
Me.ComboBox20.Value = Null
Me.TextBox15.Value = Null
Me.ComboBox10.Value = Null
Me.TextBox26.Value = Null
Me.TextBox28.Value = Null
Me.TextBox40.Value = Null
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "To ensure you want to terminate this Program Please use
CLOSE tab located below"
End If
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Code:
--------------------------
very messing but does what i need it to at the moment.
i have 3 command tabs (update / close / clear all)
i want to add another command tab to search the (hidden) database then
populate the textbox's / combobox's if possible.
then the user can back track through what has already been enter then
make changes as and where needed.
can anyone point me in the right direction or save me from myself
any help will be greatly appreciated.
thanks
Andrew