Passing value from a combo box control created at run time to a worksheet

[envy86]

I am novice to handling classes, I don't know if am complicating a simple thing. If there are mistakes kindly let me know. I have given the code below. I have a frame and few controls inside the frame in the design mode. I have an add command button next to the frame. If I click that, this code will help to create a frame at run-time similar to the one in the design mode. There will be an add and remove button outside the new frame. If remove is clicked the created frame will be removed based on the class handle event(see the class module code below).

My question here is, you can see many comboboxes and option buttons created inside the frame(created at run-time). If I enter a value(lets say a number) inside the combobox, it should be passed on to a cell in my workbook. The similar frame created in design mode has some code for passing the value to a workbook which works perfectly. However, sending those values from the controls created at run-time is not working. In the following code, cbx & cbxbot are the comboxboxes,optand & optor are the option buttons. Does using .value property help in this case?

[COLOR=#333333]Option Explicit[/COLOR]

<code style="margin: 0px; padding: 0px; font-style: inherit;">Private Cbx(12) As MSForms.ComboBox
Private Cbx1(12) As MSForms.ComboBox
Private Opt(12) As MSForms.OptionButton
Private Sub CommandButton2_Click()
  Dim Fram1 As Control 
 Dim add_cmd1 As Control 
 Dim sub_cmd1 As Control 
 Dim CommandButton1 As Control
  Dim I As Long Set Fram1 = Me.Controls.Add("Forms.Frame.1", "frm1")
   With Fram1  
  ' Do something
  End With 
   Set add_cmd1 = Me.Controls.Add("Forms.CommandButton.1", "cmdadd1", False) 
   With add_cmd1     
   ' Have something here  
  End With
 Set sub_cmd1 = Me.Controls.Add("Forms.CommandButton.1", "cmdsub1", False)
    With sub_cmd1  
       ' Have something here 
   End With     
     For I = 1 To 12 Step 2  
  Set Cbx(I) = Fram1.Controls.Add("Forms.ComboBox.1", "cbx" & I) 
   With Cbx(I)  
   ' Have something here  
 Next I    
  For I = 2 To 12 Step 2   
 Set Cbx(I) = Fram1.Controls.Add("Forms.ComboBox.1", "cbx" & I)   
 With Cbx(I)    ' Have something here  
 Next I  
For I = 1 To 12 Step 2  
  Set Cbx1(I) = Fram1.Controls.Add("Forms.ComboBox.1", "cbxbot" & I)
    With Cbx1(I)    
 ' Have something here     
 End With   Next I     
  For I = 2 To 12 Step 2    
Set Cbx1(I) = Fram1.Controls.Add("Forms.ComboBox.1", "cbxbot" & I)   
 With Cbx1(I)    
 ' Have something here      
End With   Next I     
 For I = 1 To 12 Step 2  
  Set Opt(I) = Fram1.Controls.Add("Forms.OptionButton.1", "optand" & I)  
  With Opt(I)    
 ' Have something here    
  End With   
Next I       
For I = 2 To 12 Step 2   
 Set Opt(I) = Fram1.Controls.Add("Forms.OptionButton.1", "optor" & I) 
   With Opt(I)   
  ' Have something here    
  End With  
 Next I    
  </code>[COLOR=#333333]End Sub
[/COLOR]

The following is in my class1 module. This helps to add one more frame along with the other controls, when add button is pressed.

[COLOR=#333333]Option Explicit[/COLOR]

<code style="margin: 0px; padding: 0px; font-style: inherit;">Dim C As New Class1
Private Cbx_1(12) As MSForms.ComboBox
Private Cbx1_1(12) As MSForms.ComboBox
Private Opt1(12) As MSForms.OptionButton
Public WithEvents CmdEvents As MSForms.CommandButton
Private Sub CmdEvents_Click()  
  Dim Fram2 As Control   
 Dim Txt2 As Control    
Dim add_cmd2 As Control   
 Dim sub_cmd2 As Control   
 Dim I As Long  
  Dim J As Long   
    Set Fram2 = UserForm1.Controls.Add("Forms.Frame.1", "frm2")
 With Fram2 
  ' Do something
  End With 
For I = 1 To 12 Step 2 
   Set Cbx_1(I) = Fram2.Controls.Add("Forms.ComboBox.1", "cbx_1")
    With Cbx_1(I)   
 'do something   
  End With   
Next I        
For I = 2 To 12 Step 2   
 Set Cbx_1(I) = Fram2.Controls.Add("Forms.ComboBox.1", "cbx_1")
    With Cbx_1(I)    
' Do Something    
 End With   
Next I        
 For I = 1 To 12 Step 2     
   Set Cbx1_1(I) = Fram2.Controls.Add("Forms.ComboBox.1", "cbx1_1")  
  With Cbx1_1(I)  
 ' Do something    
  End With 
  Next I      
 For I = 2 To 12 Step 2    
    Set Cbx1_1(I) = Fram2.Controls.Add("Forms.ComboBox.1", "cbx1_1") 
   With Cbx1_1(I)   
 ' Do Something     
 End With  
 Next I      
 Set add_cmd2 = UserForm1.Controls.Add("Forms.CommandButton.1", "cmdadd2", False)
    With add_cmd2  
    ' Do something  
  End With 
Set sub_cmd2 = UserForm1.Controls.Add("Forms.CommandButton.1", "cmdsub2", False)
    With sub_cmd2     
  ' Do something   
 End With     
 For I = 1 To 12 Step 2   
 Set Opt1(I) = Fram2.Controls.Add("Forms.OptionButton.1", "opt1and" & I)   
 With Opt1(I)  
 ' Do something    
  End With   
Next I       
For I = 2 To 12 Step 2   
 Set Opt1(I) = Fram2.Controls.Add("Forms.OptionButton.1", "opt1or" & I)  
  With Opt1(I)  
 ' Do something     
 End With  
 Next I      
 </code>[COLOR=#333333]End Sub
[/COLOR]

The following is in my class2 module. This helps to delete the entire frame along with the other controls, when remove button is pressed.

[COLOR=#333333]Option Explicit[/COLOR]

<code style="margin: 0px; padding: 0px; font-style: inherit;">Dim D As New Class2
Public WithEvents CmdEvents As MSForms.CommandButton
Private Sub CmdEvents_Click() 
Dim Fram1 As Control
 Dim sub_cmd1 As Control 
 UserForm1.Controls.Remove ("frm1") 
UserForm1.Controls.Remove ("cmdadd1")
 UserForm1.Controls.Remove ("cmdsub1")
 </code>[COLOR=#333333]End Sub
[/COLOR]

I am giving an example of the logic for one of the combobox/optionbuttons used in the design mode controls.

[COLOR=#333333]With sheet1[/COLOR]

<code style="margin: 0px; padding: 0px; font-style: inherit;">   If ASandC1.Value = True Then 
              .Range("A25") = ComboBox1 & Me.ComboBox2.Value    
              .Range("B25") = ComboBox3 & Me.ComboBox4.Value    
             Else            
             .Range("A25") = ComboBox1 & Me.ComboBox2.Value      
             .Range("A26") = ComboBox3 & Me.ComboBox4.Value 
           End If  
</code>[COLOR=#333333]   End With
[/COLOR]

Here ASandC1 is the optionbutton for AND, else it's the selection of OR button between two comboboxes used in the design mode. Using a similar code for the runtime controls wasn't working. I am not sure if i can do the same for the run-time controls. I am able to pass the values of the combobox using the code given above which is under userform. However, the values entered in the combo boxes that are created through class event (refer class 1 code) are not passed to the excel sheet. I am new to handling classes and I know am making a big mistake here. I would appreciate if someone can guide me through or give a hint regarding this issue.

Thanks,
Vasanth