pominoz
05-16-2013, 04:03 PM
Hi There,
I have marked my previous thread as solved and am posting the ongoing queries here.
I have a form (script below) that is completed by the user to populate a worksheet - 'Raw data'
The form autoloads when the spreadsheet is opened, I would like to know how to create a button that could be pressed to open the form after it has been closed.
I have additional columns that need to calculate sums based on the data populated from the form, for example, column Q would calculate the difference between perm target and perm sales (cloumn P and column L) I have no idea as to how to incorporate these additional column calculations for each row populated.
Private Sub cmdCancel_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 = ""
End If
Next ctl
End Sub
Private Sub cmdOK_Click()
Dim RowCount As Long
Dim ctl As Control
' Check user input
If Me.cboFinYear.Value = "" Then
MsgBox "Please select a Financial Year for this entry", vbExclamation, "Data Input"
Me.cboFinYear.SetFocus
Exit Sub
End If
If Me.cboMonth.Value = "" Then
MsgBox "Please select a month for this entry", vbExclamation, "Data Input"
Me.cboMonth.SetFocus
Exit Sub
End If
If Me.cboConsultant.Value = "" Then
MsgBox "Please select a Consultant for this entry", vbExclamation, "Data Input"
Me.cboConsultant.SetFocus
Exit Sub
End If
' Write data to worksheet
RowCount = Worksheets("Raw data").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Raw data").Range("A1")
.Offset(RowCount, 0).Value = Me.cboFinYear.Value
.Offset(RowCount, 1).Value = Me.cboMonth.Value
.Offset(RowCount, 3).Value = Me.cboConsultant.Value
.Offset(RowCount, 4).Value = Me.txtContractSaleValue.Value
.Offset(RowCount, 5).Value = Me.txtContractRunner.Value
.Offset(RowCount, 6).Value = Me.txtContractClient.Value
.Offset(RowCount, 7).Value = Me.txtContractSalesDate.Value
.Offset(RowCount, 8).Value = Me.txtContractStartDate.Value
.Offset(RowCount, 9).Value = Me.txtContractRunnertarget.Value
.Offset(RowCount, 11).Value = Me.txtPermSaleValue.Value
.Offset(RowCount, 12).Value = Me.txtPermClient.Value
.Offset(RowCount, 13).Value = Me.txtPermSaleDate.Value
.Offset(RowCount, 14).Value = Me.txtPermStartDate.Value
.Offset(RowCount, 15).Value = Me.txtPermTarget.Value
End With
' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
End If
Next ctl
End Sub
Thanks a lot
I have marked my previous thread as solved and am posting the ongoing queries here.
I have a form (script below) that is completed by the user to populate a worksheet - 'Raw data'
The form autoloads when the spreadsheet is opened, I would like to know how to create a button that could be pressed to open the form after it has been closed.
I have additional columns that need to calculate sums based on the data populated from the form, for example, column Q would calculate the difference between perm target and perm sales (cloumn P and column L) I have no idea as to how to incorporate these additional column calculations for each row populated.
Private Sub cmdCancel_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 = ""
End If
Next ctl
End Sub
Private Sub cmdOK_Click()
Dim RowCount As Long
Dim ctl As Control
' Check user input
If Me.cboFinYear.Value = "" Then
MsgBox "Please select a Financial Year for this entry", vbExclamation, "Data Input"
Me.cboFinYear.SetFocus
Exit Sub
End If
If Me.cboMonth.Value = "" Then
MsgBox "Please select a month for this entry", vbExclamation, "Data Input"
Me.cboMonth.SetFocus
Exit Sub
End If
If Me.cboConsultant.Value = "" Then
MsgBox "Please select a Consultant for this entry", vbExclamation, "Data Input"
Me.cboConsultant.SetFocus
Exit Sub
End If
' Write data to worksheet
RowCount = Worksheets("Raw data").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Raw data").Range("A1")
.Offset(RowCount, 0).Value = Me.cboFinYear.Value
.Offset(RowCount, 1).Value = Me.cboMonth.Value
.Offset(RowCount, 3).Value = Me.cboConsultant.Value
.Offset(RowCount, 4).Value = Me.txtContractSaleValue.Value
.Offset(RowCount, 5).Value = Me.txtContractRunner.Value
.Offset(RowCount, 6).Value = Me.txtContractClient.Value
.Offset(RowCount, 7).Value = Me.txtContractSalesDate.Value
.Offset(RowCount, 8).Value = Me.txtContractStartDate.Value
.Offset(RowCount, 9).Value = Me.txtContractRunnertarget.Value
.Offset(RowCount, 11).Value = Me.txtPermSaleValue.Value
.Offset(RowCount, 12).Value = Me.txtPermClient.Value
.Offset(RowCount, 13).Value = Me.txtPermSaleDate.Value
.Offset(RowCount, 14).Value = Me.txtPermStartDate.Value
.Offset(RowCount, 15).Value = Me.txtPermTarget.Value
End With
' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
End If
Next ctl
End Sub
Thanks a lot