Capture data from OptionBox into datasheet

Hello again!

Could anyone please help me with the below?

I have a userForm to capture various data into excel sheet. everything works fine but i have two things to add and zero idea how to do it.

1. add data entry from a option boxes...

There are 2 groups of optionBoxes - "Unit" (3 to choose - UNIT A, UNIT B, UNIT C) and "type" - 9 to choose - red1 red2 etc....
I would like the user to be able to select ONE unit and ONE Type and after the form is submitted it would add the exact values into specific row as in the code below. (Grouping is done)
Here's thecode I already have:

Private Sub cmdSubmit_Click()
Dim ssheet As Worksheet

Set ssheet = ThisWorkbook.Sheets("Workers")


nr = ssheet.Cells(Rows.Count, 3).End(xlUp).Row + 1


Cells(nr, 3) = Me.boxSurname.Text
Cells(nr, 4) = Me.boxForename.Text
Cells(nr, 6) = Me.boxPhone.Value
Cells(nr, 9) = Me.boxDob.Value
Cells(nr, 16) = Me.boxoptout.Text
MsgBox "Employee Added"


UserForm1.Hide
End Sub

Private Sub boxDob_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
Me.boxDob = Format(Me.boxDob, "dd/mm/yyyy")
End Sub


Private Sub boxIdexp_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
Me.boxIdexp = Format(Me.boxIdexp, "dd/mm/yyyy")
End Sub

Also i would like it to convert the input of "y" in

Cells(nr, 16) = Me.boxoptout.Text

into UPPERCASE automaticly before submitting data.
And Finally at the very end I would like the form to clear before it closes.

Any help would be amazing!

I'm a bit confused, what type of control are you using? OptionButton? ComboBox? ListBox?

It looks like you have most of what you want. Without testing the code I see a couple of things you might want to change. You need to qualify which cells you want to place the values in, and what you'd like to change about the cells. Like:

Cells(nr, 3) = Me.boxSurname.Text
ssheet.Cells(nr, 4).value = Me.boxForename.Text
ssheet.Cells(nr, 6).value = Me.boxPhone.Value
ssheet.Cells(nr, 9).value = Me.boxDob.Value
ssheet.Cells(nr, 16).value = UCase(Me.boxoptout.Text)
MsgBox "Employee Added"

e/ On your second question, if you use unload.me to close the userform, I believe it will reset all controls in the userform.

First of all thanks for the "UPPER" value things... so simple I feel embarrassed now....

To specify what I wanted in the first placeAttachment 259700
this is a screen of what the selection part of user form look like. Those are "OptionButton"s
I want to be able to select "Unit" and "Shift" and capture the data into appropriate row in excel.

Hope this helps ...

Okay you have frames around your option buttons are you trying to place the caption on the option button in the cell? or the caption from the frame the option button is in?

The only reason I ask is that Unit and Shift are you frame captions.

Why don't you attach the workbook you're working on using the method I described earlier and we can talk specifics

Daniel B,
Your post does not comply with Rule 12 of our Forum RULES. Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

Okay. Now I see what you're working with. And I agree, it looks good with frames.

Describe to me what cells you want information placed in when you click one of these option buttons. For example:

If the Unit A button is clicked I want ________ placed in X cell.

So if I click the Unit A button it will say Unit A in AL1?

Next time I click Unit C button it will say Unit C in AL2?

Originally Posted by XeRo Solus

So if I click the Unit A button it will say Unit A in AL1?

Next time I click Unit C button it will say Unit C in AL2?

No. When all data from user form gets filled in, than after clicking submit it will paste it all in the next empty Row. so Unit will be in AL8 and shift in AM8.

Why don't you try placing this in the submit button macro wherever you want the option buttons to be evaluated. Test it thoroughly and let me know what you think.

Dim cOpt As Control
Dim x As Integer
Dim y As Integer

x = 0
y = 0
For Each cOpt In Me.Unit
    If cOpt.Value = True Then
        Sheets("Workers").Range("AL" & Rows.Count).End(xlUp).Offset(1).Value = cOpt.Caption
        x = x + 1
    End If
Next cOpt
If x > 0 Then Sheets("Workers").Range("AL" & Rows.Count).End(xlUp).Offset(1).Value = "ALL"
For Each cOpt In Me.Shift
    If cOpt.Value = True Then
        Sheets("Workers").Range("AM" & Rows.Count).End(xlUp).Offset(1).Value = cOpt.Caption
    End If
Next cOpt
If y > 0 Then Sheets("Workers").Range("AM" & Rows.Count).End(xlUp).Offset(1).Value = "FLEX"

I wasn't able to test it.

Why don't you try placing this in the submit button macro wherever you want the option buttons to be evaluated. Test it thoroughly and let me know what you think.

Dim cOpt As Control
Dim x As Integer
Dim y As Integer

x = 0
y = 0
For Each cOpt In Me.Unit
    If cOpt.Value = True Then
        Sheets("Workers").Range("AL" & Rows.Count).End(xlUp).Offset(1).Value = cOpt.Caption
        x = x + 1
    End If
Next cOpt
If x > 0 Then Sheets("Workers").Range("AL" & Rows.Count).End(xlUp).Offset(1).Value = "ALL"
For Each cOpt In Me.Shift
    If cOpt.Value = True Then
        Sheets("Workers").Range("AM" & Rows.Count).End(xlUp).Offset(1).Value = cOpt.Caption
    End If
Next cOpt
If y > 0 Then Sheets("Workers").Range("AM" & Rows.Count).End(xlUp).Offset(1).Value = "FLEX"

I wasn't able to test it.

ps The frame was the right way to go. It's more than just looks its functionality. Option buttons within a frame are evaluated differently. Only one option button in a frame can be clicked at a time, so if you didn't have the frames you couldn't select a shift and a unit at the same time.