VBA Help: Check cells are completed before submitting data to workbook

[K1600]

Hi,

I'm new to the VBA world but I am getting there slowly. I have created a UserForm with a number of text and combo boxes. I am currently stuck whilst trying to sort out the command button.

What I want to happen is that when the 'Submit' button is pressed, it checks that all the text and combo boxes (except one text box) have data in them before then entering the data into the spreadsheet. If there is a box left empty then I want a MsgBox to pop up to say this, then allow the user to return to the UserForm and amend their entry before again pressing the 'submit' button. Once sent, the user receives a MsgBox to confirm their entry.

I have tried various code but I am currently using the one below. The issues I am having is that: a) it will warn that a field is blank but still submits the data to the spreadsheet, b) after displaying the MsgBox and returning to the UserForm, all data entered has been cleared and the user must start again.

In order to not disclose personal info in the code I have replaced some of the wording but to show where I have done this I have replaced it with things such as X1 and so it stands out I have also made the text bold.

As I said, I am very new to this so I would be most grateful of any help. Most of the coding has been found through searching forums and then trying to piece together what I need from what I have found.

Private Sub cmdTest_Click()

'Test command button


'Checks for empty fields in UserForm (except Remarks)
If DDX1.Text = "" Then
    cancel = 1
    MsgBox "X1 not entered"
    DDX1.SetFocus
ElseIf DDTxtDate.Text = "" Then
    cancel = 1
    MsgBox "Date Not Entered"
    DDTxtDate.SetFocus
ElseIf DDcboTime1.Text = "" Then
    cancel = 1
    MsgBox "Time (Hours) Not Entered"
    DDcboTime1.SetFocus
ElseIf DDcboTime2.Text = "" Then
    cancel = 1
    MsgBox "Time(Minutes) Not Entered"
    DDcboTime2.SetFocus
ElseIf DDcboX4.Text = "" Then
    cancel = 1
    MsgBox "Result for X4 Not Selected"
    DDcboX4.SetFocus
ElseIf DDcboX5.Text = "" Then
    cancel = 1
    MsgBox "Result for X5 Not Selected"
    DDcboX5.SetFocus
ElseIf DDTxtAge.Text = "" Then
    cencel = 1
    MsgBox "Age Not Entered"
    DDTxtAge.SetFocus
ElseIf DDcboGender = "" Then
    cancel = 1
    MsgBox "Gender Not Selected"
    DDcboGender.SetFocus
ElseIf DDcboAction = "" Then
    cancel = 1
    MsgBox "Outcome Not Selected"
    DDcboAction.SetFocus
End If




'Move data from UserForm to worksheet


Dim rw As Long 'Gets next available row


With Sheets("XL Sheet") 'Selects sheet


rw = .Range("A" & Rows.Count).End(xlUp).Row + 1 'Gets next available row in sheet


'Put the UserForm entries in the found blank row
.Range("A" & rw).Value = DDX1.Value
.Range("B" & rw).Value = DDX2.Value
.Range("C" & rw).Value = DDX3.Value
.Range("D" & rw).Value = DDTxtDate.Value
.Range("E" & rw).Value = DDcboTime1.Value & ":" & DDcboTime2.Value  'Concatenates the two time entry fields
.Range("F" & rw).Value = DDTxtAge.Value
.Range("G" & rw).Value = DDcboGender.Value
.Range("H" & rw).Value = DDcboX4.Value
.Range("I" & rw).Value = DDcboX5.Value
.Range("J" & rw).Value = DDcboActions.Value
.Range("K" & rw).Value = DDTxtRemarks.Value
.Range("L" & rw).Value = Date + Time 'Adds a date/time stamp for the entry




'Clear UserForm entries


DDX1.Value = ""
DDX2.Value = ""
DDX3.Value = ""
DDTxtDate.Value = ""
DDcboTime1.Value = ""
DDcboTime2.Value = ""
DDcboX4.Value = ""
DDcboX5.Value = ""
DDTxtAge.Value = ""
DDcboGender.Value = ""
DDcboActions.Value = ""
DDTxtRemarks.Value = ""


MsgBox "Your entry has been submitted."




End With


End Sub

Thanks in advance,

Glynn

 

[jacobrcotton]

What it looks like is....

a) the problem of the data still mapping to the spreadsheet. This is happening because the "Mapping" code isn't contained within some IF statement. If you added 1 more ElseIF after the checks but before the End If, it would only map if it had passed all tests. However, there are other solutions, just the first that popped in my head.

b) the problem of clearing all the data. This is happenin for the same reason as (a). The "Clear Values" code isn't contained. If using the approach in (a), you just add in your "Clear Values" code after the "Mapping" code within that last Else If statement. But again, there are other solutions.

Thanks! Let me know if it works or assists.

 

[jacobrcotton]

I"m sorry, to clarify my response...


What it looks like is....

a) the problem of the data still mapping to the spreadsheet. This is happening because the "Mapping" code isn't contained within some IF statement. If you added 1 more ElseIF after the checks but before the End If, and added your "Mapping" code within this new ElseIf it would only map if it had passed all tests. However, there are other solutions, just the first that popped in my head.

b) the problem of clearing all the data. This is happenin for the same reason as (a). The "Clear Values" code isn't contained. If using the approach in (a), you just add in your "Clear Values" code after the "Mapping" code within that last Else If statement. But again, there are other solutions.

Thanks! Let me know if it works or assists.

 

[K1600]

Thanks for the reply and sorry for the delay, I have been in work all day.

Please accept my apologise if I am being thick here but I'm struggling to make your suggestion work even though it kind of made sense to me. I removed my 'End If' after the checks code and tried to add 'Else If' before 'Dim rw As Long' however I get a compile error of 'Expected: expression'. I think I need to add something other than the 'ElseIf' but I'm not sure what.

I'm guessing that once I have figured that one out I just need to do the same for the clearing data bit?

Thanks again.

 

[Fluff]

Hi & welcome to the board.
Try this (modifications in blue)

Private Sub cmdTest_Click()

'Test command button

[COLOR=#0000ff]Cancel = 0[/COLOR]
'Checks for empty fields in UserForm (except Remarks)
If DDX1.Text = "" Then
    Cancel = 1
    MsgBox "X1 not entered"
    DDX1.SetFocus
ElseIf DDTxtDate.Text = "" Then
    Cancel = 1
    MsgBox "Date Not Entered"
    DDTxtDate.SetFocus
ElseIf DDcboTime1.Text = "" Then
    Cancel = 1
    MsgBox "Time (Hours) Not Entered"
    DDcboTime1.SetFocus
ElseIf DDcboTime2.Text = "" Then
    Cancel = 1
    MsgBox "Time(Minutes) Not Entered"
    DDcboTime2.SetFocus
ElseIf DDcboX4.Text = "" Then
    Cancel = 1
    MsgBox "Result for X4 Not Selected"
    DDcboX4.SetFocus
ElseIf DDcboX5.Text = "" Then
    Cancel = 1
    MsgBox "Result for X5 Not Selected"
    DDcboX5.SetFocus
ElseIf DDTxtAge.Text = "" Then
    cencel = 1
    MsgBox "Age Not Entered"
    DDTxtAge.SetFocus
ElseIf DDcboGender = "" Then
    Cancel = 1
    MsgBox "Gender Not Selected"
    DDcboGender.SetFocus
ElseIf DDcboAction = "" Then
    Cancel = 1
    MsgBox "Outcome Not Selected"
    DDcboAction.SetFocus
End If

[COLOR=#0000ff]If Cancel = 1 Then
    MsgBox "not all values have been entered"
    Exit Sub
End If
[/COLOR]
'Rest of your code here

 

[K1600]

Hi Fluff,

Thanks for your help. That's worked a treat.

Thanks all.

 

[Fluff]

Glad to help & thanks for the feedback

 

[K1600]

On a slightly separate note, if I wanted the data from my UserForm to be put into another workbook (rather than the one the form is open in), how would I need to reference this to make it point to the correct excel file?

Thanks.

 

[Fluff]

Replace

With Sheets("XL Sheet") 'Selects sheet

With

    Dim Wbk As Workbook
    
    Set Wbk = Workbooks.Open("[COLOR=#ff0000]C:\Users\Fluff\Documents\Excel files\Book1.xls[/COLOR]")
    With Wbk.Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")

Replacing the values in red to match your filepath, filename & sheet name

 

[K1600]

That's great, thanks again for your help.

I will give that a whirl when I get home later.


Cheers.