Solved: Moving data from form to worksheet

[pominoz]

Hi All,

I have created a spreadsheet which I would like to have populated through a form (script below) I have used a vba tutorial as the basis of the script.

There are a number of problems that I can't seem to fix:

1. Once the form is completed and the 'OK' button is pressed the data does not populate the first available row in the worksheet 'Raw data'. It doesn't seem to do anything at all!

2. The form automatically loads when the macro enabled version of the spreadsheet is opened, but I need the user to be able to call the form up multiple times to complete. I would ideally like a button that can be pressed to call the form.

3. If a user enters data which then populates a row in the 'Raw data' worksheet but later need to remove that row how can this easily be done?

This is currently a very rudimentary attempt to see if I can get to grips with forms and populating data into worksheets.

Any help would be very welcome.

Thanks

[VBA]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 = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
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("A2").CurrentRegion.Rows.Count
With Worksheets("Raw data").Range("A2")
.Offset(RowCount, 0).Value = Me.cboFinYear.Value
.Offset(RowCount, 1).Value = Me.cboMonth.Value
.Offset(RowCount, 2).Value = Me.cboConsultant.Value
.Offset(RowCount, 3).Value = Me.txtContractSaleValue.Value
.Offset(RowCount, 4).Value = Me.txtContractRunner.Value
.Offset(RowCount, 5).Value = Me.txtContractClient.Value
.Offset(RowCount, 6).Value = Me.txtContractSalesDate.Value
.Offset(RowCount, 7).Value = Me.txtContractStartDate.Value
.Offset(RowCount, 8).Value = Me.txtContractRunnertarget.Value
.Offset(RowCount, 9).Value = Me.txtPermSaleValue.Value
.Offset(RowCount, 10).Value = Me.txtPermClient.Value
.Offset(RowCount, 11).Value = Me.txtPermSaleDate.Value
.Offset(RowCount, 12).Value = Me.txtPermStartDate.Value
.Offset(RowCount, 13).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[/VBA]

[pominoz]

Well I feel a little foolish...

Having looked again at the spreadsheet and the script with fresh eyes this morning, I realised that I had a formula in columns Q, R, S, T & U of my spreadsheet and the formula was copied down to row 250...

So the data from the form was populating from row 251 onwards and I couldn't see it!!!

I will post the other follow on questions in a new thread though.

Thanks

[SamT]