VBA Userform: Sending data to specific Sheet depending on which Option Button selected

[Clova13]

Hi all

I'm pretty new to all this so hope someone can help me.

I have a userform set up which initially asks for ComboBox1 (Cmb1) and TextBox1 (Txt1) to be filled in. Then I have two option buttons.

OptonButton1 (Opt1) brings up TextBoxes 2, 3 and 4 (Txt2, Txt3, Txt4)
OptionButton2 (Opt2) brings up Textboxes 5, 6 and 7 (Txt5, Txt6, Txt7)

I need to all the boxes brought up with Opt1 sent to Sheet 1 (Sh1) along with Cmb1 and Txt1
All the boxes brought up with Opt 2 need to go to Sheet 2 (Sh2) along with Cmb1 and Txt1
If Opt1 is selected, boxes brought up under Opt2 don't need to be filled in and vice versa.

Cmb1 is always in column A on the sheet
Txt1 is always in column B on the sheet

Everything I try isn't working. I've only been using VBA for a few days so I'm really green! Help please

Kind Regards,
Rachel

 

[James006]

Hello,

For a start ... why don't you post the macro you are building to store data back to your sheet ...

 

[My Aswer Is This]

You said:

OptonButton1 (Opt1) brings up TextBoxes 2, 3 and 4 (Txt2, Txt3, Txt4)

What does bring up mean?

bring up is not a Excel term

​

 

[Clova13]

Sorry if I missed info you need. The macro I'm using for the OptionButtons are:

If Me.Opt1.Value = True Then
Me.ScoresTab.Visible = True
Me.SoresTab.Value = 0
Me.Opt2.Visible = False
Else
etc...


If Me.Opt2.Value = True Then
Me.SoresTab.Visible = True
Me.ScoresTab.Value = 1
Me .Opt1.Visible = False
Else
etc..

I have the TextBoxes within 2 tabs - depending on which OptionButton they use it shows the TextBoxes under 1 of 2 tabs. I have the Tabs hidden so the user can't change Tabs. Tab 0 needs to input data on Sh1 and Tab 1 on Sh2.

Is that clearer for you?

 

[Clova13]

The code I found, but isn't working, is as follows:

Dim lrTab0 As Long, Dim lrTab1 As Long

lrTab0 = Sheets("Sh1"). Range("A" & Rows.Count). End(xlUP). Row

Sheets ("Sh1"), Cells(lrTab0 + 1, "A"). Value = Cmb1.Value
Sheets ("Sh1"), Cells(lrTab0 + 1, "B"). Value = Txt1.Text
Sheets ("Sh1"), Cells(lrTab0 + 1, "C"). Value = Txt2.Text
Sheets ("Sh1"), Cells(lrTab0 + 1, "C"). Value = Txt3.Text
Sheets ("Sh1"), Cells(lrTab0 + 1, "C"). Value = Txt4.Text

lrTab1 = Sheets("Sh2"). Range("A" & Rows.Count). End(xlUP). Row

Sheets ("Sh2"), Cells(lrTab1 + 1, "A"). Value = Cmb1.Value
Sheets ("Sh2"), Cells(lrTab1 + 1, "B"). Value = Txt1.Text
Sheets ("Sh2"), Cells(lrTab1 + 1, "C"). Value = Txt5.Text
Sheets ("Sh2"), Cells(lrTab1 + 1, "C"). Value = Txt6.Text
Sheets ("Sh2"), Cells(lrTab1 + 1, "C"). Value = Txt7.Text

Not sure if I've just not adapted it right?

 

[My Aswer Is This]

If your brand new to Excel Vba this seems to be a big task to start out with.

Please explain in words what your ultimate goal is​

Sounds like you are wanting to hide controls on some sheets

And you keep using the Term Tab

Tab is normally referred to as a sheet

 

[mikerickson]

You could code like this

Perhaps,

Dim SelectedSheet as worksheet
Dim LastRow as Long

If Me.Opt1.Value Then
    Set SelectedSheet = ThisWorkbook.Sheets("Sh1")
Else
    Set SelectedSheet = ThisWorkbook.Sheets("Sh2")
End If

With SelectedSheet
    With .Cells(.Rows.Count,1).End(xlUP).Offset(1,0)
       .Range("A1").Value = Me.Cmb1.Value
       .Range("B1").Value = Me.Txt1.Text
       .Range("C1").Value = IIf(Me.opt1.Value, Me.txt2.text, Me.txt5.Text)
       .Range("D1").Value = IIf(Me.opt1.Value, Me.txt3.text, Me.txt6.Text)
       .Range("E1").Value = IIf(Me.opt1.Value, Me.txt4.text, Me.txt6.Text)
    End With
End With

 

[Clova13]

Hey Mickrickson

Gave that a go but getting error message saying 'Argument not optional'


It's a massive undertaking - I'm not new to Excel but definitely to VBA. Learning lots very fast

I've got 2 sets of team manager groups (about 10-20 people per group) who'll be submitting data about 300 staff to pull through to my worksheets.

One tab (and sheet) is for one group and other tab (and sheet) for 2nd group. I'll have 3rd sheet to compile it all which is ready. They'll be submitting data under the same headings but I want to make sure they don't use the wrong Textboxes in the form by mistake - trying to make it idiot-proof lol. Hence hiding the Tab not for them to use. One form for all has been requested so that's what I'm trying to do.

 

[mikerickson]

Which line triggers the error?

 

[Clova13]

Never mind. I've got it! Thanks for your help