Additions to userform

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
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.



Code:
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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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......
 
Upvote 0
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
 
Upvote 0
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?
 
Last edited:
Upvote 0
Just something like "Please complete all questions etc "

Worded so it then covers option button also
 
Upvote 0
Try this:

Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top