Power Query
Power Pivot
May 26, 2022
Hi all - can anyone help me work out what I still need to do to stop my MoveToData running if CheckFields finds any blank fields? Currently, I get the MsgBox but it immediately moves the data from the form to the data sheet, so you can't fill in the gaps it has highlighted. I'm new to VBA, so please be kind!
I've attached this code to the button on the form:
Sub Button1_Click()
Call CheckFields 'Macro1
Call MoveToData 'Macro2
End Sub
And these are the macros it points to:
Sub CheckFields()
If Range("C5").Value = "" Then MsgBox ("Please fill in the Family Surname")
If Range("C7").Value = "" Then MsgBox ("Please fill in the first part of the Family's postcode")
If Range("C9").Value = "" Then MsgBox ("Please fill in the EHM Number")
If Range("C11").Value = "" Then MsgBox ("Please fill in the FULL email address to send the voucher to")
If Range("E11").Value = "" Then MsgBox ("Please state whether or not the family needs a physical rather than a digital voucher")
If Range("C13").Value = "" Then MsgBox ("Please fill in the District in which the Family live")
If Range("C15").Value = "" Then MsgBox ("Please fill in the name of the worker completing the form")
If Range("C17").Value = "" Then MsgBox ("Please fill in the Job Title of the worker completing the form")
If Range("C19").Value = "" Then MsgBox ("Please fill in the Date you have completed this form")
If Range("C22").Value = "" Then MsgBox ("Please fill in the the total number of £30 vouchers you are requesting")
If Range("C24").Value = "" Then MsgBox ("Please fill in the total number of children supported")
End Sub
Sub MoveToData()
ws_output = "Data"
next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Sheets(ws_output).Cells(next_row, 1).Value = Range("Family_Surname").Value
Sheets(ws_output).Cells(next_row, 2).Value = Range("postcode").Value
Sheets(ws_output).Cells(next_row, 3).Value = Range("EHM_Check").Value
Sheets(ws_output).Cells(next_row, 4).Value = Range("Email_Address").Value
Sheets(ws_output).Cells(next_row, 5).Value = Range("District").Value
Sheets(ws_output).Cells(next_row, 6).Value = Range("Name").Value
Sheets(ws_output).Cells(next_row, 7).Value = Range("Job_Title").Value
Sheets(ws_output).Cells(next_row, 8).Value = Range("Date").Value
Sheets(ws_output).Cells(next_row, 9).Value = Range("Q_Vouchers").Value
Sheets(ws_output).Cells(next_row, 10).Value = Range("C_Vouchers").Value
Sheets(ws_output).Cells(next_row, 11).Value = Range("No.Children").Value
Sheets(ws_output).Cells(next_row, 12).Value = Range("No.Disabled").Value
Sheets(ws_output).Cells(next_row, 13).Value = Range("Reduce_Stress").Value
Sheets(ws_output).Cells(next_row, 14).Value = Range("Improve_Wellbeing").Value
Sheets(ws_output).Cells(next_row, 15).Value = Range("Improve_Mental_Health").Value
Sheets(ws_output).Cells(next_row, 16).Value = Range("Free_Up_Money").Value
Sheets(ws_output).Cells(next_row, 17).Value = Range("Physical_Voucher").Value
Range("Family_Surname").Value = ""
Range("postcode").Value = ""
Range("EHM_Check").Value = ""
Range("Email_Address").Value = ""
Range("District").Value = ""
Range("Name").Value = ""
Range("Job_Title").Value = ""
Range("Date").Value = ""
Range("Q_Vouchers").Value = ""
Range("No.Children").Value = ""
Range("No.Disabled").Value = ""
Range("Reduce_Stress").Value = ""
Range("Improve_Wellbeing").Value = ""
Range("Improve_Mental_Health").Value = ""
Range("Free_Up_Money").Value = ""
Range("Physical_Voucher").Value = ""
OutPut = MsgBox("Thank you for your submission", vbOKOnly, "Submission complete")
End Sub
Trusted Members
October 17, 2018
Hi Tracey,
First of all to mention the Excel version you're using, I suggest you place it in your user profile so it's visible when you post.
Since you're using named ranges I don't expect that you want us to re create a file with all the named ranges you haev there.
You say you're new at VBA so this code you posted has come from somewhere and you expect a working solution.
Please attach your file including this macro and and we'll see what someone here can do for you.
I like challenges and many here do to help but recreating a file without the idea of what the OP wants is not an option
Trusted Members
Moderators
November 1, 2018
Try turning the first sub into a function that only returns True if all fields are complete:
Sub Button1_Click()
If FieldsAreComplete Then Call MoveToData 'Macro2
End Sub
Function FieldsAreComplete() As Boolean
FieldsAreComplete = True
Dim msg As String
If Range("C5").Value = "" Then
msg = "Please fill in the Family Surname"
ElseIf Range("C7").Value = "" Then
msg = "Please fill in the first part of the Family's postcode"
ElseIf Range("C9").Value = "" Then
msg = "Please fill in the EHM Number"
ElseIf Range("C11").Value = "" Then
msg = "Please fill in the FULL email address to send the voucher to"
ElseIf Range("E11").Value = "" Then
msg = "Please state whether or not the family needs a physical rather than a digital voucher"
ElseIf Range("C13").Value = "" Then
msg = "Please fill in the District in which the Family live"
ElseIf Range("C15").Value = "" Then
msg = "Please fill in the name of the worker completing the form"
ElseIf Range("C17").Value = "" Then
msg = "Please fill in the Job Title of the worker completing the form"
ElseIf Range("C19").Value = "" Then
msg = "Please fill in the Date you have completed this form"
ElseIf Range("C22").Value = "" Then
msg = "Please fill in the the total number of £30 vouchers you are requesting"
ElseIf Range("C24").Value = "" Then
msg = "Please fill in the total number of children supported"
End If
If Len(msg) <> 0 Then
FieldsAreComplete = False
MsgBox msg
End If
End Function
Sub MoveToData()
ws_output = "Data"
next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Sheets(ws_output).Cells(next_row, 1).Value = Range("Family_Surname").Value
Sheets(ws_output).Cells(next_row, 2).Value = Range("postcode").Value
Sheets(ws_output).Cells(next_row, 3).Value = Range("EHM_Check").Value
Sheets(ws_output).Cells(next_row, 4).Value = Range("Email_Address").Value
Sheets(ws_output).Cells(next_row, 5).Value = Range("District").Value
Sheets(ws_output).Cells(next_row, 6).Value = Range("Name").Value
Sheets(ws_output).Cells(next_row, 7).Value = Range("Job_Title").Value
Sheets(ws_output).Cells(next_row, 8).Value = Range("Date").Value
Sheets(ws_output).Cells(next_row, 9).Value = Range("Q_Vouchers").Value
Sheets(ws_output).Cells(next_row, 10).Value = Range("C_Vouchers").Value
Sheets(ws_output).Cells(next_row, 11).Value = Range("No.Children").Value
Sheets(ws_output).Cells(next_row, 12).Value = Range("No.Disabled").Value
Sheets(ws_output).Cells(next_row, 13).Value = Range("Reduce_Stress").Value
Sheets(ws_output).Cells(next_row, 14).Value = Range("Improve_Wellbeing").Value
Sheets(ws_output).Cells(next_row, 15).Value = Range("Improve_Mental_Health").Value
Sheets(ws_output).Cells(next_row, 16).Value = Range("Free_Up_Money").Value
Sheets(ws_output).Cells(next_row, 17).Value = Range("Physical_Voucher").Value
Range("Family_Surname").Value = ""
Range("postcode").Value = ""
Range("EHM_Check").Value = ""
Range("Email_Address").Value = ""
Range("District").Value = ""
Range("Name").Value = ""
Range("Job_Title").Value = ""
Range("Date").Value = ""
Range("Q_Vouchers").Value = ""
Range("No.Children").Value = ""
Range("No.Disabled").Value = ""
Range("Reduce_Stress").Value = ""
Range("Improve_Wellbeing").Value = ""
Range("Improve_Mental_Health").Value = ""
Range("Free_Up_Money").Value = ""
Range("Physical_Voucher").Value = ""
OutPut = MsgBox("Thank you for your submission", vbOKOnly, "Submission complete")
End Sub
Power Query
Power Pivot
May 26, 2022
Hi - many thanks for getting back to me!
Thank you for your comments @Hans - please see file attached. The VBA was partly written by someone at County, but I have made some changes. I'm running 365, if that helps.
I changed the code to match @Velouria (although I had to remove one of the End If statements as I was getting an error) - sadly, it still doesn't work, in that it copies the form data into the data sheet even if there are gaps. I need it to highlight where there are gaps and not allow someone to submit the data to the data sheet until those gaps have been filled.
Trusted Members
Moderators
November 1, 2018
1 Guest(s)