Option Button

[ExcelUser87]

I have not been able to find the answer to my questions yet and im not exactly sure how to describe it so i will use an example below.

I am using a form to populate multiple sheets in a file. and example of two different groups of Option buttons and Check boxes i am currently using is below.

Activity Group (Check Boxes)
A B C D E

Section Group (Option Button)
1 2 3 4

Currently my code reads like this:
If A And 1 = True Then
Range("C3").Select
End If

However the problem i am facing is that C3 is selected even if A and 2 are selected or if B and 1 are selected.

How do i make it clear that only if BOTH A and 1 are selected together can C3 be selected?

Thanks!!

 

[Marcelo Branco]

Complementing previous post

Try this

If Me.CheckBoxA.Value = True And Me.OptionButton1.Value = True Then
Range("C3").Select
MsgBox "*** Range C3 Selected ***"
End If

And see what happens.

M.

 

[ExcelUser87]

Yes, this is only one section of the code. before this i have code with If statements populating multiple sheets before this all from the same Userform.

but i havent had any issues with any of the other code.

 

[mikerickson]

This function wil return the (possibly discontinous) range indicated by the users choice of Activity Group(s) and Selection Group.

Function IndicatedRange() As Range
    Dim destinationRange As Range
    Dim rOffset As Long, cOffset As Long
    Dim ActivityGroupControls As Variant
    Dim SelectionGroupControls As Variant
    
    Rem adjust
    Set destinationRange = ThisWorkbook.Sheets("Sheet1").Range("C3")
    ActivityGroupControls = Array(CheckBox1, CheckBox2, CheckBox3, CheckBox4, CheckBox5)
    SelectionGroupControls = Array(OptionButton1, OptionButton2, OptionButton3, OptionButton4)
    
    Set destinationRange = destinationRange.Resize(UBound(SelectionGroupControls) + 1, UBound(ActivityGroupControls) + 1)
    
    For rOffset = 0 To UBound(SelectionGroupControls)
        If SelectionGroupControls(rOffset) Then Exit For
    Next rOffset
    
    If UBound(SelectionGroupControls) < rOffset Then
        Rem no Selection Group Selected
    Else
        Set IndicatedRange = destinationRange.Parent.Cells(Rows.Count, Columns.Count)
        For cOffset = 0 To UBound(ActivityGroupControls)
            If ActivityGroupControls(cOffset) Then
                Set IndicatedRange = Application.Union(IndicatedRange, destinationRange.Cells(1, 1).Offset(rOffset, cOffset))
            End If
        Next cOffset
       
        Set IndicatedRange = Application.Intersect(IndicatedRange, destinationRange)

    End If
End Function

It can be used like this.
Note that using the For Each construct removes the need to test if IndicatedRange Is Nothing.

Dim incrimenter as Double
incrimenter = 1: Rem dummy test value


Dim oneCell as range

For Each oneCell in IndicatedRange
    oneCell.Value = Val(CStr(oneCell.Value)) + Incrimenter
Next oneCell

If IndicatedRange is Nothing Then
    MsgBox "not properly selected"
Else
    MsgBox IndicatedRange.Address & " indicated by check/option buttons."
End If

 

[ExcelUser87]

wow, thanks. unfortunetly i have no idea what this means and therefore i have no idea how to use it.

youve been a lot of help so far so i hate to keep bothering you but do you think you can explain your code to me a little?

 

[mikerickson]

The function should go in the userform's code module.

This section assigns your destination Range (adjust the sheet name etc to match your situation).
It also makes two arrays of controls. One is an array of the checkboxes that indicate Activity Group, the other is an array of the Option Buttons for Selection Group.

    Rem adjust
    Set destinationRange = ThisWorkbook.Sheets("Sheet1").Range("C3")
    ActivityGroupControls = Array(CheckBox1, CheckBox2, CheckBox3, CheckBox4, CheckBox5)
    SelectionGroupControls = Array(OptionButton1, OptionButton2, OptionButton3, OptionButton4)

From previous posts, the row of the destination cell(s) depends on which one of the OptionButtons is selected. Looping through the option buttons and testing if that optionbutton's .Value is True will indicate the rOffset to apply.

To determing which columns are being used, the cOffset loop loops through CheckBoxes. If a checkbox is checked (CheckBox.Value = True) then the indicated cell is added to the IndicatedRange.

Because of the way that Union operates, IndicatedRange is initialized with a cell that is well outside the table range.
The cOffset loop adds cells that are in the table's range to IndicatedRange.
Finally, IndicatedRange is Intersected with the whole DestinationRange (the range of the table) to strip off the initial (out of range) cell.