Attempting to reduce userforms

[VB Newbie]

Dear anyone who would like to assist-

I think I can reduce the number of userforms by 75-80%, however I can't find
a quick fix to my issue. Here is what I have thus far.

Userform1 with 4 optionbuttons.
Userform2 with 2 textboxes and a commandbutton.

Each optionbutton will open up Userform2. If optionbutton1 is selected, I
want the textboxes to place their values in A1 and A2, OB2 to B1 and B2, OB3
to C1 and C2, and OB4 to D1 and D2 using commandbutton1.

Is this feasible using scripts in Userform2 or do I need to set up
individual scripts for each optionbutton?

Any help would be greatly appreciated!!

 

[chijanzen]

VB Newbie:

add this code to Userform2

'Userform2
Private Sub CommandButton1_Click()
Dim Ctrl As MSForms.Control
Dim ob As String
For Each Ctrl In UserForm1.Controls
If TypeName(Ctrl) = "OptionButton" And Ctrl = True Then
ob = Ctrl.Name
End If
Next
r = Val(Right(ob, 1))
Cells(1, r) = Me.TextBox1.Text
Cells(2, r) = Me.TextBox2.Text
End Sub

 

[VB Newbie]

Thank you kindly for your assistance. I will put this to the test tonight
and thank you again when it works

 

[VB Newbie]

That helped out greatly. Now, if I wanted to start with Cell K1, would I
change:

"r = Val(Right(ob, 1)) "
into
"r = Val(Right(ob, 1)) + 11" ?

Thank you for your assistance!!!

 

[VB Newbie]

Or would Cells(1, r).offset(0,11) and Cells(2, r).offset(0,11) be a better
idea?

 

[chijanzen]

VB Newbie:

try,

MsgBox Chr(107)

'Userform2
Private Sub CommandButton1_Click()
Dim Ctrl As MSForms.Control
Dim ob As String
For Each Ctrl In UserForm1.Controls
If TypeName(Ctrl) = "OptionButton" And Ctrl = True Then
ob = Ctrl.Name
End If
Next
r = Chr((106 + Val(Right(ob, 1))))
Cells(1, r) = Me.TextBox1.Text
Cells(2, r) = Me.TextBox2.Text
End Sub

 

[VB Newbie]

Your assistance has been a huge help and headache reliever!!! Many thanks to
you and your time with me!

 

[VB Newbie]

Hi again-

I have found that I can use the same userform for 6 other forms, which use
the same column just different row address. For userform1, cells goto k1 and
k2, userform2 to k3 and k4.... thru userform 6.

However, I am trying to figure out the best way to handle this situation.
I've been toying with the following idea:

For Each Ctrl In UserForm1.Controls
If TypeName(Ctrl) = "OptionButton" And Ctrl = false Then goto userform2
else
ob = Ctrl.Name
Next
r = Chr((106 + Val(Right(ob, 1))))
Cells(1, r) = Me.TextBox1.Text
Cells(2, r) = Me.TextBox2.Text
end if

userform2:
dim ob2 as string
For Each Ctrl In UserForm2.Controls
If TypeName(Ctrl) = "OptionButton" And Ctrl = false Then goto userform2
else
ob2 = Ctrl.Name
Next
r = Chr((106 + Val(Right(ob2, 1))))
Cells(3, r) = Me.TextBox1.Text
Cells(4, r) = Me.TextBox2.Text
end if

Would you recommend something else or did I do something wrong? I just
can't seem to get the script to function. Thanks again for your assistance!!!

 

[chijanzen]

VB Newbie:

I have other idea

Download File:

http://61.60.224.22/mywebhd/get_file.aspx?name=00057063\9411301.xls

 

[chijanzen]

Sorry! again

http://www.vba.holyou.net/file/9411301.xls