adding date from user form with calendar to next row
 

I have a spreadsheet with 2 tabs. On the input sheet that tab is
called Input, the sheet where the data goes is called PSHCP. I have a
button that on the input sheet opens a user form where I enter a
number of fields. (Name, Employee id (PRI), and 2 pick lists (dept &
level).

The next 2 fields are for the deduction date and coverage date. I
have a calendar in the user form that I pick the 2 dates. It is
working in that it will place the date in cell E2 and F2 of the PSHCP
sheet but will not add with the row with other data. not sure what I
am missing.

What I want is for all the data to add to the next row of the table on
the PSHCP tab as one row, not 2 rows.

Any help is very much appreciated.

Here is the code I have so far:

Private Sub CommandButton1_Click()
Unload Me
End Sub
---------------------------------------------------------------------
Private Sub cmdClear_Click()
' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub

---------------------------------------------------------------------
Private Sub OK_Click()
Dim RowCount As Long

If Me.txtName.Value = "" Then
MsgBox "Please enter Employee's name", vbExclamation,
"PSHCPNUMBERS"
Me.txtName.SetFocus
Exit Sub
End If
If Me.TXTPRI.Value = "" Then
MsgBox "Please enter Employee's PRI", vbExclamation,
"PSHCPNUMBERS"
Me.txtName.SetFocus
Exit Sub
End If
If Me.CBODEPARTMENT.Value = "" Then
MsgBox "Please Choose a Department", vbExclamation,
"PSHCPNUMBERS"
Me.txtName.SetFocus
Exit Sub
End If
If Me.cboPSHCPLEVEL.Value = "" Then
MsgBox "Please Choose PSHCP Level", vbExclamation,
"PSHCPNUMBERS"
Me.txtName.SetFocus
Exit Sub
End If
RowCount =
Worksheets("PSHCP").Range("A1").CurrentRegion.Rows .Count
With Worksheets("PSHCP").Range("A1")
.Offset(RowCount, 7).Value = Format(Now, "dd/mmm/yyyy
hh:nn:ss") & Application.UserName
.Offset(RowCount, 0).Value = Me.txtName.Value
.Offset(RowCount, 1).Value = Me.TXTPRI.Value
.Offset(RowCount, 2).Value = Me.CBODEPARTMENT.Value
.Offset(RowCount, 3).Value = Me.cboPSHCPLEVEL.Value
End With
Unload Me

End Sub
---------------------------------------------------------------------
Private Sub Calendar1_Click()
Worksheets("PSHCP").Range("e2") = Calendar1.Value
End Sub
---------------------------------------------------------------------
Private Sub Calendar2_Click()
Worksheets("PSHCP").Range("f2") = Calendar2.Value
End Sub

adding date from user form with calendar to next row
 

I'm not sure I understand your question. What data will not add with the
other data, the calendar's date values? If so, why are you handling them
separately? Why not remove the two calendar Click event procedures
altogether and process their calendar dates along with the other data when
the OK button is pressed. I would think you would need to add the following
two lines to accomplish this...

.Offset(RowCount, 4).Value = Calendar1.Value
.Offset(RowCount, 5).Value = Calendar2.Value

Rick


wrote in message
...
I have a spreadsheet with 2 tabs. On the input sheet that tab is
called Input, the sheet where the data goes is called PSHCP. I have a
button that on the input sheet opens a user form where I enter a
number of fields. (Name, Employee id (PRI), and 2 pick lists (dept &
level).

The next 2 fields are for the deduction date and coverage date. I
have a calendar in the user form that I pick the 2 dates. It is
working in that it will place the date in cell E2 and F2 of the PSHCP
sheet but will not add with the row with other data. not sure what I
am missing.

What I want is for all the data to add to the next row of the table on
the PSHCP tab as one row, not 2 rows.

Any help is very much appreciated.

Here is the code I have so far:

Private Sub CommandButton1_Click()
Unload Me
End Sub
---------------------------------------------------------------------
Private Sub cmdClear_Click()
' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub

---------------------------------------------------------------------
Private Sub OK_Click()
Dim RowCount As Long

If Me.txtName.Value = "" Then
MsgBox "Please enter Employee's name", vbExclamation,
"PSHCPNUMBERS"
Me.txtName.SetFocus
Exit Sub
End If
If Me.TXTPRI.Value = "" Then
MsgBox "Please enter Employee's PRI", vbExclamation,
"PSHCPNUMBERS"
Me.txtName.SetFocus
Exit Sub
End If
If Me.CBODEPARTMENT.Value = "" Then
MsgBox "Please Choose a Department", vbExclamation,
"PSHCPNUMBERS"
Me.txtName.SetFocus
Exit Sub
End If
If Me.cboPSHCPLEVEL.Value = "" Then
MsgBox "Please Choose PSHCP Level", vbExclamation,
"PSHCPNUMBERS"
Me.txtName.SetFocus
Exit Sub
End If
RowCount =
Worksheets("PSHCP").Range("A1").CurrentRegion.Rows .Count
With Worksheets("PSHCP").Range("A1")
.Offset(RowCount, 7).Value = Format(Now, "dd/mmm/yyyy
hh:nn:ss") & Application.UserName
.Offset(RowCount, 0).Value = Me.txtName.Value
.Offset(RowCount, 1).Value = Me.TXTPRI.Value
.Offset(RowCount, 2).Value = Me.CBODEPARTMENT.Value
.Offset(RowCount, 3).Value = Me.cboPSHCPLEVEL.Value
End With
Unload Me

End Sub
---------------------------------------------------------------------
Private Sub Calendar1_Click()
Worksheets("PSHCP").Range("e2") = Calendar1.Value
End Sub
---------------------------------------------------------------------
Private Sub Calendar2_Click()
Worksheets("PSHCP").Range("f2") = Calendar2.Value
End Sub