Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add calendar to VBA User Form | Excel Discussion (Misc queries) | |||
Adding a new text box to user form | Excel Discussion (Misc queries) | |||
Adding a control to a User Form | Excel Programming | |||
Loading a calendar (user form) from another project (personal.xls) | Excel Programming | |||
Adding a counter to a User Form in Excel | Excel Programming |