Where do I add this to my code?

[kidwispa]

Hi All,

What I currently have is a userform (userform3) that people on my team complete, and once they have filled out all relevant fields, they click a command button which populates the first blank row on a sheet called "Data". If the sheet is incomplete it returns an error message with words to that affect. If the sheet is completed correctly it opens a messagebox stating the claim has been updated.

I now wish to add a further condition that if one of the comboboxes (combobox8) on userform 3 returns the result "ABC" then after updating the "Data" sheet , it will open up userform 4, bypassing the messagebox mentioned above. and will close userform 3. If the combobox doesn't return "ABC" it will carry on as before.

Here is the code I currently have:

Private Sub CommandButton1_Click()
Dim lastrow As Object
Dim flag As Boolean
Worksheets("Data").Unprotect Password:="Password"
flag = False
If TextBox1.Text = "" Then
flag = True
End If
If ComboBox7.Text = "" Then
flag = True
End If
If ComboBox8.Text = "" Then
flag = True
End If
If TextBox7.Text = "" Then
flag = True
End If
If ComboBox3.Text = "" Then
flag = True
End If
If TextBox25.Text = "" Then
flag = True
End If
If TextBox28.Text = "" Then
flag = True
End If
If TextBox27.Text = "" Then
flag = True
End If
If TextBox53.Text = "" Then
flag = True
End If
If TextBox26.Text = "" Then
flag = True
End If
If TextBox2.Text = "" Then
flag = True
End If
If TextBox3.Text = "" Then
flag = True
End If
If TextBox8.Text = "" Then
flag = True
End If
If ComboBox9.Text = "" Then
flag = True
End If
If TextBox10.Text = "" Then
flag = True
End If
If TextBox12.Text = "" Then
flag = True
End If
If TextBox14.Text = "" Then
flag = True
End If
If ComboBox4.Text = "" Then
flag = True
End If
If TextBox11.Text = "" Then
flag = True
End If
If TextBox52.Text = "" Then
flag = True
End If
If TextBox42.Text = "" Then
flag = True
End If
If TextBox29.Text = "" Then
flag = True
End If
If flag = False Then
 
Set lastrow = Sheet5.Range("a65536").End(xlUp)
lastrow.Offset(1, 0).Value = CDate(TextBox1.Value)
lastrow.Offset(1, 1).Value = ComboBox7.Text
lastrow.Offset(1, 4).Value = ComboBox8.Text
lastrow.Offset(1, 5).Value = TextBox7.Text
lastrow.Offset(1, 6).Value = ComboBox3.Text
lastrow.Offset(1, 7).Value = TextBox25.Text
lastrow.Offset(1, 9).Value = TextBox28.Text
lastrow.Offset(1, 10).Value = TextBox27.Text
lastrow.Offset(1, 11).Value = TextBox53.Text
lastrow.Offset(1, 8).Value = TextBox26.Text
lastrow.Offset(1, 12).Value = TextBox30.Text
lastrow.Offset(1, 14).Value = TextBox2.Text
lastrow.Offset(1, 15).Value = TextBox3.Text
lastrow.Offset(1, 16).Value = TextBox29.Text
lastrow.Offset(1, 17).Value = CDate(TextBox8.Value)
lastrow.Offset(1, 18).Value = ComboBox9.Text
lastrow.Offset(1, 19).Value = TextBox10.Text
lastrow.Offset(1, 20).Value = Label15.Caption
lastrow.Offset(1, 21).Value = TextBox11.Text
lastrow.Offset(1, 22).Value = Label20.Caption
lastrow.Offset(1, 23).Value = TextBox12.Text
lastrow.Offset(1, 24).Value = TextBox13.Text
lastrow.Offset(1, 25).Value = TextBox14.Text
lastrow.Offset(1, 26).Value = ComboBox4.Text
lastrow.Offset(1, 39).Value = TextBox52.Text
lastrow.Offset(1, 28).Value = TextBox42.Text
Worksheets("Data").Protect Password:="Password"
MsgBox "Insurance Claim ADDED"
 
response = MsgBox("Do you want to enter another insurance claim?", vbYesNo)
If response = vbYes Then
ComboBox7.SetFocus
TextBox1.Text = Format(Now(), "DD-MM-YYYY")
ComboBox7.Text = ""
ComboBox8.Text = ""
TextBox7.Text = ""
ComboBox3.Text = ""
TextBox25.Text = ""
TextBox28.Text = ""
TextBox27.Text = ""
TextBox53.Text = ""
TextBox26.Text = ""
TextBox30.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox29.Text = ""
TextBox8.Text = ""
ComboBox9.Text = ""
TextBox10.Text = ""
Label15.Caption = ""
TextBox11.Text = ""
Label20.Caption = ""
TextBox12.Text = ""
TextBox13.Text = ""
TextBox14.Text = ""
ComboBox4.Text = ""
TextBox52.Text = ""
TextBox42.Text = ""
Else
Unload Me
End If
Else
MsgBox "Claim Form Incomplete"
End If
End Sub

I think I need to add in something like:

If ComboBox8.Text = "ABC" then
Unload Me
UserForm4.Show

But I'm not entirely sure where to add it!!

Any help would be really appreciated

 

[RichardMGreen]

It's not a problem to include the 4th userform in your code.
You would to check the contents of the relevant Combobox to see what it contains and then display the new userform if necessary (you can do this after all the checks).
The code would be
if combobox11.text="ABC" then userform4.show
(you'll need to change the combobox number as necessary to make sure it's the right one).

It would go after this line:-
If flag = False Then


After the 4th combox has been completed, the messagebox will be displayed and the rest of the code will execute.

 

[kidwispa]

It's not a problem to include the 4th userform in your code.
You would to check the contents of the relevant Combobox to see what it contains and then display the new userform if necessary (you can do this after all the checks).
The code would be
if combobox11.text="ABC" then userform4.show
(you'll need to change the combobox number as necessary to make sure it's the right one).

It would go after this line:-
If flag = False Then


After the 4th combox has been completed, the messagebox will be displayed and the rest of the code will execute.

Hi Richard,

Thanks for your reply, however it doesn't quite give the solution I need. Here's what I need the code to do:

1) If combobox8 doesn't contain "ABC", then execute the original code.
2) If combobox8 contains "ABC", update the "Data" sheet with the information input on userform3, THEN open userform4.

Hope this helps to clarify what I need, and thanks if you can help some more!!!

 

[RichardMGreen]

In that case the code I gave you would go just before the message box line if you want to protect the worksheet first or just before the protect line if not.

 

[kidwispa]

In that case the code I gave you would go just before the message box line if you want to protect the worksheet first or just before the protect line if not.

Thats great Richard - thanks for your help

 

[RichardMGreen]

There may also be a way of looping to check each combobox/textbox instead of using a series of IF statements.
I'm not sure, but I'm fairly certain it's possible.

 

[kidwispa]

There may also be a way of looping to check each combobox/textbox instead of using a series of IF statements.
I'm not sure, but I'm fairly certain it's possible.

I think for the time being as I'm still learning I'm sticking to the 'if it ain't broke don't fix it" school of thought...

On a separate note, when userform4 opens how can I get one of the textboxes to automatically contain the data from a textbox on userform 3? So for example if userform3 textbox2 contains 12345678 when userform4 opens textbox1 on this form automatically shows 12345678 (ie - the number that was input on the sheet just before opening)???

 

[kidwispa]

On a separate note, when userform4 opens how can I get one of the textboxes to automatically contain the data from a textbox on userform 3? So for example if userform3 textbox2 contains 12345678 when userform4 opens textbox1 on this form automatically shows 12345678 (ie - the number that was input on the sheet just before opening)???

Can someone please give me some advice as to whether this is the best way to solve my problem?

Basically when UserForm4 opens the data from UserForm3 will have been copied into the "Data" sheet. What I would like to do is to have a textbox on UserForm4 that contains the data that was held in a textbox on UserForm3 before it was closed, as then when the data is copied into the "Data" sheet from UserForm4 it is done so on the same row as what was copied previously from UserForm3 using Application.Match...

I hope my terminology is clear enough for someone out there to understand and help me with this issue - I've searched online and the only code I could find was this

Private Sub UserForm_Terminate()
MsgBox "Terminate"
UserForm3.TextBox2.Value = UserForm4.TextBox17.Value
End Sub

But this doesn't seem to work - any takers?

 

[Norie]

Shouldn't it ne the other way round.

ie Userfom3 to Userform4, not Userform4 to Userform3

 

[kidwispa]

Shouldn't it ne the other way round.

ie Userfom3 to Userform4, not Userform4 to Userform3

Hi Norie,

I did change it the other way round but also instead of putting it in sub userform_terminate I put it in userform_activate and that seems to do the trick!