Using radio buttons in an Excel vba script

Question

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.

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" & Rows.Count).End(xlUp).Row

For cell = 2 To LRow
    If Cells(cell, "A").Value <> "" 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" & Rows.Count).End(xlUp).Row + 1
                    .Range("A" & LRow & ":AF" & LRow) = _
                    Worksheets("Sheet1").Range("A" & r & ":AF" & r).Value
                End With
                Exit For
            End If
        Next r
    End If
Next

End Sub

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 error at Line 4 in CopyRows(). *It says "Subscript Out of range".* 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).

Answer 1

This is not really an answer to your question, it's a suggestion about an alternative to what you are doing. It didn't fit in a comment, so I write it here as an answer.

I learned to stay away from check boxes and other controls on the sheets. They are not well managed by Excel (problems working with multiple windows, with split windows, with large sheets, impossible to create hundreds of controls, etc.), and difficult to manage in VBA or VSTO.

I usually do something like this: when the user clicks on a cell, the Worksheet_SelectionChange checks whether that cell contains a check box, a radio button or a button. A cell contains, or rather is, a radio button when it contains the text "¡" or "¤" (with the font Wingdings), a check box when it contains the text "¨" or "þ" (again Wingdings), a button when it contains whatever text you decide it's a button.

If the selected cell is a radio button the macro resets all the other radios to unchecked ("¡") and sets the selected one to checked ("¤").

If the selected cell is a check box the macro swaps "¨" with "þ".

If it's a button the macro executes the code associated with the button.

If the selected cell is a check box or a button, the macro also selects another cell (without a fake control), to allow the user to click on the same control and fire the event again.

Here is an example of code. This code must be in a worksheet module, not in a code module, so the sub called Worksheet_SelectionChange is recognized as a worksheet event and fired whenever the selection on that sheet is changed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  'exit if the selected range contains more than one cell
  If Target.Columns.Count > 1 Then Exit Sub
  If Target.Rows.Count > 1 Then Exit Sub

  'check for radio buttons
  If Target.Text = "¡" Then
    Application.EnableEvents = False
    Range("B1:B3") = "¡"
    Target = "¤"
    Application.EnableEvents = True
  End If

  'check for check boxes
  If Target.Text = "þ" Then
    Application.EnableEvents = False
    Target = "¨"
    Target.Offset(0, 1).Select
    Application.EnableEvents = True
  ElseIf Target.Text = "¨" Then
    Application.EnableEvents = False
    Target = "þ"
    Target.Offset(0, 1).Select
    Application.EnableEvents = True
  End If

  'check for button
  Dim Txt As String
  If Target.Text = "[Show stats]" Then
    Txt = "Radio 1 = " & IIf(Range("B1") = "¤", "Yes", "No") & vbLf
    Txt = Txt & "Radio 2 = " & IIf(Range("B2") = "¤", "Yes", "No") & vbLf
    Txt = Txt & "Radio 3 = " & IIf(Range("B3") = "¤", "Yes", "No") & vbLf
    Txt = Txt & "Check 1 = " & IIf(Range("B5") = "þ", "Yes", "No") & vbLf
    Txt = Txt & "Check 2 = " & IIf(Range("B6") = "þ", "Yes", "No") & vbLf

    MsgBox Txt

    Application.EnableEvents = False
    Target.Offset(0, 1).Select
    Application.EnableEvents = True
  End If
End Sub

Here is a snippet of a worksheet that works with the code listed above: