Attempting to reduce userforms
 

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

Attempting to reduce userforms
 

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"VB Newbie" wrote:

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

Attempting to reduce userforms
 

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

"chijanzen" wrote:

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"VB Newbie" wrote:

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

Attempting to reduce userforms
 

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



"chijanzen" wrote:

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"VB Newbie" wrote:

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

Attempting to reduce userforms
 

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

"chijanzen" wrote:

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"VB Newbie" wrote:

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

Attempting to reduce userforms
 

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"VB Newbie" wrote:

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

"chijanzen" wrote:

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"VB Newbie" wrote:

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

Attempting to reduce userforms
 

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

"chijanzen" wrote:

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"VB Newbie" wrote:

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

"chijanzen" wrote:

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"VB Newbie" wrote:

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

Attempting to reduce userforms
 

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" wrote:

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"VB Newbie" wrote:

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

"chijanzen" wrote:

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"VB Newbie" wrote:

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

Attempting to reduce userforms
 

VB Newbie:

I have other idea

Download File:

http://61.60.224.22/mywebhd/get_file...%5c9411301.xls

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"VB Newbie" wrote:

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" wrote:

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"VB Newbie" wrote:

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

"chijanzen" wrote:

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"VB Newbie" wrote:

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

Attempting to reduce userforms
 

Sorry! again

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"VB Newbie" wrote:

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" wrote:

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"VB Newbie" wrote:

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

"chijanzen" wrote:

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"VB Newbie" wrote:

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