title: Using radio buttons in an Excel vba script
body: <p>I am building a Macro to copy selected rows from a sheet to a selected sheet. For example I want to copy row 3,5,6,7 to Sheet 3. I have thought of using check boxes to select rows and radio buttons to select sheet. In my code I am setting a variable by radio buttons and that variable is used to decide the sheet in which the data has to be copied. </p>
  
  <pre><code>Public Val As String
  Public Sub OptionButton1_Click()
  If OptionButton1.Value = True Then Val = "Sheet2"
  End Sub
  
  Public Sub OptionButton2_Click()
  If OptionButton2.Value = True Then Val = "Sheet3"
  End Sub
  
  
  Sub Addcheckboxes()
  Dim cell, LRow As Single
  Dim chkbx As CheckBox
  Dim MyLeft, MyTop, MyHeight, MyWidth As Double
  
  Application.ScreenUpdating = False
  LRow = ActiveSheet.Range("A" &amp; Rows.Count).End(xlUp).Row
  
  For cell = 2 To LRow
      If Cells(cell, "A").Value &lt;&gt; "" Then
          MyLeft = Cells(cell, "E").Left
          MyTop = Cells(cell, "E").Top
          MyHeight = Cells(cell, "E").Height
          MyWidth = Cells(cell, "E").Width
          ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select
          With Selection
              .Caption = ""
              .Value = xlOff
              .Display3DShading = False
          End With
      End If
  Next cell
  
  Application.ScreenUpdating = True
  
  End Sub
  
  
  
  Sub CopyRows()
  
  For Each chkbx In ActiveSheet.CheckBoxes
      If chkbx.Value = 1 Then
          For r = 1 To Rows.Count
              If Cells(r, 1).Top = chkbx.Top Then
                  With Worksheets(Val)
                      LRow = .Range("A" &amp; Rows.Count).End(xlUp).Row + 1
                      .Range("A" &amp; LRow &amp; ":AF" &amp; LRow) = _
                      Worksheets("Sheet1").Range("A" &amp; r &amp; ":AF" &amp; r).Value
                  End With
                  Exit For
              End If
          Next r
      End If
  Next
  
  End Sub
  </code></pre>
  
  <p>Val variable is being set here by either Option button 1 or 2. And that value is being used by Sub CopyRows() 
  But I am getting an <strong>error at Line 4 in CopyRows().</strong> *<em>It says "Subscript Out of range".</em>* Do you see any problem in my logic or anything else? Thanks. (Please pardon any obvious errors as I am still in learning stage of this).</p>