Additions to userform

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,226
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.
 
Sort by date Sort by votes
I have added a frame.
The i have put 3 option buttons in that frame.

I will then add another frame and then add the other 3 options buttons inside that frame.

See my problem is not being able to concentrate 100%,the reason i say that is because i posted the incorrect code.

The correct code in use at present is shown below.

It is easy for me to get confused then when i make a mistake like the code in use i then dont see the problem.

Code: 
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
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 CheckBox1.Value = True Then .Cells(lastrow + 1, 6).Value = "EBAY": CheckBox1.Value = False
    If CheckBox2.Value = True Then .Cells(lastrow + 1, 6).Value = "WEB SITE": CheckBox2.Value = False
    If CheckBox3.Value = True Then .Cells(lastrow + 1, 6).Value = "N/A": CheckBox3.Value = False
End With
TextBox2.SetFocus
TextBox1.Value = Now
TextBox1 = Format(TextBox1.Value, "dd/mm/yyyy")
End Sub
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
So put all your Option Buttons into frames. Manually look and see how they work.

Then you will see you can only select one from each frame.

Now if your going to need 14 different frames.
We may need to look at doing this some other way.

And then get back with me.
 
Upvote 0
Thanks.
I am doing it now.

I have 2 frames with 3 buttons inside each.

Nearly done so reply back very soon
 
Upvote 0
I don't like using this Cancel=1 thing but if it works for you that's great.

I still don't understand why my original code did not work.

And having to tell the user exactly what Textbox is empty seems a little bit over the top to me.

The user on his own should be able to see what Textbox is empty.

But then this is your choice. If this type checking works for you that's great.

Now how to write code like you have and you want to tell user a Option Button has not been selected.

I guess you would just have to say You chose no option button.
 
Upvote 0
OK,
So the code in use is shown below.

Frame 1 consists of 3 OptionBullets

Frame 2 consists of 3 OptionBullets

The code shown below check ONLY for text box vales before the form is submitted to my sheet.

Now that i have added these 2 Frames i need the nesscessary extra bits of code added please.

"If you need any other info then i am here"


Code: 
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
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 CheckBox1.Value = True Then .Cells(lastrow + 1, 6).Value = "EBAY": CheckBox1.Value = False
    If CheckBox2.Value = True Then .Cells(lastrow + 1, 6).Value = "WEB SITE": CheckBox2.Value = False
    If CheckBox3.Value = True Then .Cells(lastrow + 1, 6).Value = "N/A": CheckBox3.Value = False
End With
TextBox2.SetFocus
TextBox1.Value = Now
TextBox1 = Format(TextBox1.Value, "dd/mm/yyyy")
End Sub
 
Upvote 0
You said:
Now that i have added these 2 Frames i need the nesscessary extra bits of code added please.​


What extra bits of code??

What else do you need.
​
 
Upvote 0
Before i added these frames the code only check the text boxes before the transfer to my sheet would happen.

So doesnt the code need to make sure that a option button was selected within each frame.

Otherwise the transfer would happen and the cells where i should be getting some text added will be empty
 
Upvote 0
I gave you this code when you said you only had three Option buttons.

And you said it did not work.

Not sure why you first said you had three now you have 6.

And I just had the message tell the user he did not choice a Option Button.

But now it seems you want the user told exactly what option button was not chosen just like your telling the user exactly what textbox is not filled it.

I really do not know how to do that.

Code: 
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
 
Upvote 0
So looking at my existing code which checks text boxes dont i now need to add something like the below so the code makes sure a option in the frame was also selected.

Code: 
ElseIf Frame1 = False  Then
    Cancel = 1
    MsgBox "No option selected"
     Frame1.SetFocus
ElseIf Frame2 = False Then
    Cancel = 1
    MsgBox "No option selected"
     Frame2.SetFocus
 
Upvote 0
That code would not work for me even though it worked for you.
So i then needed to move on.
I found something that did work for me so i started to use it.

Hours ago i had 3 options but now have 6
Frame 1 has 3 options
Frame 2 has 3 options

If the user did not make a selection say in frame 1 then a msgbox should advise him of this just like not entering any text in a text box
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Userform to worksheet values followed by 3 digits
Replies
2
Views
190
ipbr21054
I
D
  • Solved
Copy row based on cell value and paste as text in another worksheet
Replies
2
Views
488
DH937
D
T
  • Question
How to show Msg if Record not found in table
Replies
3
Views
99
myall_blues
myall_blues
M
  • Solved
Copy to Next Row if Value in Specified Row
Replies
3
Views
629
Joe4
Joe4
E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
289
offthelip
O

Forum statistics

Threads
1,215,125
Messages
6,123,195
Members
449,090
Latest member
bes000

Share this page

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