Help with form

[pominoz]

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

[mdmackillop]

Add a Forms or ActiveX Button. You can open the form using
UserForm1.Show False '(non-modal)
UserForm1.Show True '(modal)
where UserForm1 is the Form name.
Re Part 2, can you post sample workbook? Maybe you need a slightly different form to call.

[pominoz]

Add a Forms or ActiveX Button. You can open the form using
UserForm1.Show False '(non-modal)
UserForm1.Show True '(modal)
where UserForm1 is the Form name.
Re Part 2, can you post sample workbook? Maybe you need a slightly different form to call.

Hi there, thanks for this I will try to incorporate it.

I have attached a zip version of the spreadsheet and code.

The calculations that I am trying to include are as follows:

Column Q - =IF(OR(L2="",P2="")," ",(+P2-L2))

Column R - =IF(AND(E2="",L2="")," ",(E2+L2))

Column S - =IF(ISTEXT(G2),"Contract",IF(ISTEXT(M2),"Perm"," "))

Column T - =IF(AND(I2="",O2="")," ",IF(ISNUMBER(I2),TEXT(I2,"MMM"),IF(ISNUMBER(O2),TEXT(O2,"MMM"))))

I do not know how to write these in VBA, also how to make them active only for rows that contain data??

In my ideal world I would have a form that asked for Targets by month and consultant, and these values would simply be stored to be used as part of ongoing calculations. For example , to calculate the difference between target and actual in any given time period.

In the spreadsheet that I have uploaded there is a reporting worksheet which is a pivot table. I would like to have a button (drop down list) of report options such as Month to date (calculates all activity by consultant - contract sales, contract runners against target, perm sales against target etc), Quarter to date report as previous but for the quarter, Year to date. Or perhaps there is a way that a worksheets for each month, quarter, year could be created automatically and updated whenever new data is input.

I hope some of this makes sense and that you are able to help.

Thanks very much, pominoz