Additions to userform

[ipbr21054]

Morning,

I have made a userform but need a couple of additions that im not to be about.

The code in use is shown below.

The addition would be that once i press Command Button 1 it should make sure that TextBox 1,2,3,4,5,6 & Option Button1,2,3 has been answered otherwise show msgbox to advise question not answered etc.

The last addition would then be once the above has been successfully transfered to my sheet the text boxes / option buttons should be cleared & focus set on text box 1 ready for the next entry.

Private Sub CommandButton1_Click()

LastRow = ThisWorkbook.Worksheets("POSTAGE").Cells(Rows.Count, 1).End(xlUp).Row


ThisWorkbook.Worksheets("POSTAGE").Cells(LastRow + 1, 1).Value = TextBox1.Text


ThisWorkbook.Worksheets("POSTAGE").Cells(LastRow + 1, 2).Value = TextBox2.Text


ThisWorkbook.Worksheets("POSTAGE").Cells(LastRow + 1, 3).Value = TextBox3.Text


ThisWorkbook.Worksheets("POSTAGE").Cells(LastRow + 1, 5).Value = TextBox4.Text


ThisWorkbook.Worksheets("POSTAGE").Cells(LastRow + 1, 6).Value = TextBox5.Text


ThisWorkbook.Worksheets("POSTAGE").Cells(LastRow + 1, 9).Value = TextBox6.Text




If OptionButton1.Value = True Then
ThisWorkbook.Worksheets("POSTAGE").Cells(LastRow + 1, 8).Value = "DR"
End If


If OptionButton2.Value = True Then
ThisWorkbook.Worksheets("POSTAGE").Cells(LastRow + 1, 8).Value = "IVY"
End If


If OptionButton3.Value = True Then
ThisWorkbook.Worksheets("POSTAGE").Cells(LastRow + 1, 8).Value = "N/A"
End If




End Sub

Have a nice day.

 

[My Aswer Is This]

What does this mean?
You said:

show msgbox to advise question not answered etc.

define etc.

We would need to know what exactly you want the message to say so we do not write a script and then you say:

I wanted it to say......
​

 

[ipbr21054]

If say text box 2 did not have an entry typed then when you press the command button the msgbox would then be shown advising the user that a certain question was not answered above. in this case the user foget to type something in text box 2.

Basically every text box & 1 option button must be completed.

Thanks

 

[My Aswer Is This]

So if Textbox 1 and Textbox 3 are empty must the message Box say Text1 and Textbox3 are empty?

Or can it just say some of your Textboxes are empty?

 

[ipbr21054]

Just something like "Please complete all questions etc "

Worded so it then covers option button also

 

[My Aswer Is This]

Try this:

Private Sub CommandButton1_Click()
'Modified  9/24/2018  7:06:34 AM  EDT
Dim i As Long
Dim x as Long
Dim ctrl As Control
Dim lastrow As Long
lastrow = ThisWorkbook.Worksheets("POSTAGE").Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 1 To 6
        If Me.Controls("TextBox" & i).Value = "" Then MsgBox "Some Textbox is empty": Exit Sub
    Next
    
    For i = 1 To 3
        If Me.Controls("OptionButton" & i).Value = False Then x = x + 1
    Next
    If x = 3 Then MsgBox "No Option Button is selected": Exit Sub
    
 With ThisWorkbook.Worksheets("POSTAGE")
    .Cells(lastrow + 1, 1).Value = TextBox1.Text: TextBox1.Value = ""
    .Cells(lastrow + 1, 2).Value = TextBox2.Text: TextBox2.Value = ""
    .Cells(lastrow + 1, 3).Value = TextBox3.Text: TextBox3.Value = ""
    .Cells(lastrow + 1, 5).Value = TextBox4.Text: TextBox4.Value = ""
    .Cells(lastrow + 1, 6).Value = TextBox5.Text: TextBox5.Value = ""
    .Cells(lastrow + 1, 9).Value = TextBox6.Text: TextBox6.Value = ""
    If OptionButton1.Value = True Then .Cells(lastrow + 1, 8).Value = "DR": OptionButton1.Value = False
    If OptionButton2.Value = True Then .Cells(lastrow + 1, 8).Value = "IVY": OptionButton2.Value = False
    If OptionButton3.Value = True Then .Cells(lastrow + 1, 8).Value = "N/A": OptionButton3.Value = False
End With
TextBox1.SetFocus
End Sub

 

[ipbr21054]

Hi,
Testing it with a question not answered & hitting transfer then i see your msgbox all ok.

If i complete all questions and hit Transfer then i see a run time error Could not find the specified object.
Debug then shows this in yellow.

If Me.Controls("TextBox" & i).Value = "" Then

 

[My Aswer Is This]

Well I tested the script and it all worked for me.

You need to have Textbox's named
TextBox1 To TextBox6

Be sure Textbox's are named properly

They should all have default names.

 

[ipbr21054]

Ok

Let me check this out.

 

[ipbr21054]

Yes text box is like TextBox1 TextBox2 etc