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.

 

[ipbr21054]

I understand and its the same for me when it doesnt not,strange as this isnt the first time.

The following code works.
I then added the code back in that i was advised to remove & the same error was shown.
I will leave that code out as i dont know why it works for you and not me.

Can you advise please the following,
Once i submit the transfer & now my focus is set on TextBox2 the date needs to also be shown in TextBox1,not sure how to write the code to do this after transfer button is pressed.
When i first open the form the initialized code works.

As you can see in the code i have added another 3 option buttons
There are 2 questions on my form with 3 possible answers each.
So,
OptionButton 1,2,3 are for 1 question

OptionButton 4,5,6 are for another question

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
    


    
 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 = 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
    If OptionButton4.Value = True Then .Cells(lastrow + 1, 6).Value = "EBAY": OptionButton5.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
End Sub

 

[ipbr21054]

This didnt work for me,
Trying to allow 2 sets of different bulletoption like in my photo screenshot below.

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
    


    
 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 = 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
    
With ThisWorkbook.Worksheets("POSTAGE")


    If OptionButton4.Value = True Then .Cells(lastrow + 1, 6).Value = "EBAY": OptionButton5.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 With
End Sub

 

[ipbr21054]

I was unable to work out how to have the option bullet as above so i had to use checkbox for the second lot.
Thanks for your help.

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
    


    
 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 = 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
    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

 

[My Aswer Is This]

When using Option buttons. You can only choose one. If you have 3 or 5 or how many.
You can only choose one.
Just try doing so without using a script and you will see if you click one the other ones will then unselect themselves.

There is a way to solve this issue.

Put each option button in separate frames.

Look in your control Box and you will se Frame.

 

[ipbr21054]

Hi,
I will look at the frame you mention.

None of these Option Buttons are checked to start with.
I just select one of the to then show a bullet.
This is why i cant work out the code before i submit the form.

 

[ipbr21054]

Ok i have looked at that.

I have now put the option buttons in frames.
You are able to select all 3 of these ?

Using my existing code how would i add in these frames to be checked ?

Do i also put the check boxes in frames ?

 

[My Aswer Is This]

I prefer to work on one issue at a time.
But:

You said:

Can you advise please the following,
Once i submit the transfer & now my focus is set on TextBox2 the date needs to also be shown in TextBox1,not sure how to write the code to do this after transfer button is pressed.
When i first open the form the initialized code works.

One thing is you did not show me initialize code.

In your original post you said set focus on textbox1

Now I see you have changed code to set Focus to textbox2

And I guess you have given up on checking the controls to see if they are filled in.

Again you cannot have more then one Option Button on a User Form selected.

See if you just try doing that manually you should see that.

A Option button is used so a user can select his choice.

Like do you want Apple or Pear

Now if you want you can put different choices inside a Frame

Now user can choose one from each frame.

Like inside frame1 you have:

Apple and Pear

And inside frame2 you could have:

Ford Chevy Toyota

User can only select one from each frame.
​

 

[ipbr21054]

I thought post #23 advised that i couldnt sort the bullet option so have to use a set of check boxes.
I then changed the focus from 1 to 2

When you replied in post #24 i assumed that you had read the posts before and was continuing from my post 23

I am now putting the options in frames

Thanks

 

[My Aswer Is This]

You said:

I have now put the option buttons in frames.
You are able to select all 3 of these ?

Using my existing code how would i add in these frames to be checked ?

Do i also put the check boxes in frames ?

You can select all three that is true.


No you need not put check boxes into a frame.


But you cannot select all three and have all three in the same frame selected.

You said:

Using my existing code how would i add in these frames to be checked ?

Well we were not able earlier to check any control to see if they are checked.

Again I like to deal with one question at a time.


So are now saying you have all your separate option buttons in their proper frame?


Choice of cars must be in one frame and only one can be chosen

Choice of Vegetable must be in a separate frame and only one can be chosen​

_{^{<strike>
</strike>}}​

 

[My Aswer Is This]

We may be getting ahead of each other.

You said:

I thought post ​

#23

advised that i couldnt sort the bullet option so have to use a set of check boxes.

Sort??

I have seen nothing mentioned about sorting.

Sort means to put in some order.

Like A B C instead of G A X


​