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

 

[Fluff]

Your welcome

 

[K1600]

Once again, that worked great Fluff, thanks.

Apologies for being a pain here but with your help I have moved along greatly but I am stuck again now. There may be no way around this but thought it worth asking.

I have now put my UserForm into a separate workbook and have it loading automatically when the workbook is opened, when it comes to save the data from the UserForm it does so in my original workbook before saving the master data workbook and closing excel.

The issue I am having is that I need multiple users to be able to input data into the workbook. So far I have made my UserForm workbook 'read only' so that it doesn't throw an error up when another user tries to open it (they don't need to save this workbook), I have also 'shared' the workbook where end/master data is being stored. So long as two people don't press the 'Submit' button at the same time it works fine however, if 'Submit' is pressed by multiple users at the same time, obviously, only one users data gets accepted and the other gets an error saying that a workbook with that name already exists and do they want to overwrite. What I was hoping for was the option of a 'Your data was not saved' and maybe a 'Retry' button if this instance was to happen. Whilst it is unlikely that they would be pressing the button at the exact same time, its not worth my risk as there could potentially be a lot of people using the form all be it, not very often.

Hope this makes sense. Any help would be great.

Thanks in advance.

 

[Fluff]

As I avoid shared workbooks like the plague, I'm not sure I can help.
a possible option would be to give each user their own workbook & then create a macro that will pull the data from those workbooks, into yours.

 

[K1600]

I must admin, I'm not really a fan myself. I'll have another play to see if I can come up with another work around but if not I'll post back in the main forum.

The individual workbook option is a bit of a non-starter as there will likely be around a thousand users but with only a low level of input.

Thanks again.

 

[Fluff]

The individual workbook option is a bit of a non-starter as there will likely be around a thousand users but with only a low level of input.

Point taken