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]

No I do not think that would work.

Try this

You would need Two but you should see the ideal:

If OptionButton1.Value = False And OptionButton2.Value = False And OptionButton3.Value = False Then MsgBox "Help Me"

 

[ipbr21054]

I got your idea and now using the code below which works.

There is one annoying feature when a text box or option has not been selected,see example.

I leave the text box ITEM empty.
I then select Transfer.
I now see a message saying Item Not Entered.
I then click OK

But i now see another message saying Not All Have Been Answered ???
I know that because of the previous message.

Can this second message be deleted as its not really needed.
I did delete what i thought was correct but then each time all my previous entries were erased & you then need to type it all in again

Private Sub CommandButton1_Click()Cancel = 0
If TextBox2.Text = "" Then
    Cancel = 1
    MsgBox "Customer not entered"
    TextBox2.SetFocus
ElseIf TextBox3.Text = "" Then
    Cancel = 1
    MsgBox "Item Not Entered"
    TextBox3.SetFocus
ElseIf TextBox4.Text = "" Then
    Cancel = 1
    MsgBox "Tracking Not Entered"
    TextBox4.SetFocus
ElseIf TextBox5.Text = "" Then
    Cancel = 1
    MsgBox "Username Not Selected"
    TextBox5.SetFocus
    
ElseIf OptionButton1.Value = False And OptionButton2.Value = False And OptionButton3.Value = False Then
    Cancel = 1
    MsgBox "Select Origin"
    
ElseIf OptionButton4.Value = False And OptionButton5.Value = False And OptionButton6.Value = False Then
    Cancel = 1
    MsgBox "Select Ebay Account"
    
End If


If Cancel = 1 Then
    MsgBox "Not All Have Been Answered"
    Exit Sub
End If


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
    


    
 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, 9).Value = TextBox5.Text: TextBox5.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
    If OptionButton4.Value = True Then .Cells(lastrow + 1, 6).Value = "EBAY": OptionButton4.Value = False
    If OptionButton5.Value = True Then .Cells(lastrow + 1, 6).Value = "WEB SITE": OptionButton5.Value = False
    If OptionButton6.Value = True Then .Cells(lastrow + 1, 6).Value = "N/A": OptionButton6.Value = False
End With
TextBox2.SetFocus
TextBox1.Value = Now
TextBox1 = Format(TextBox1.Value, "dd/mm/yyyy")
End Sub

 

[My Aswer Is This]

I never use this type checking.

I thought you said earlier you received this suggestion from someone else and it worked for you and you were happy with it.

But now it seems as if it's not working for you.

I never like using this cancel= type code.

But you said earlier it worked for you so I do not know what to say.

 

[ipbr21054]

It does work for me.

I was concentrating along with you to sort out the frames etc.
Now that is all done i mentioned this.

Its no big deal just a pain being told twice of something that the first message told you

Thanks

 

[My Aswer Is This]

So I guess all is well and everything is working for you.
Take care

 

[ipbr21054]

YES

Many thanks for your time with helping me with this.

See You Again