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]

Try this

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

Substitute the names CheckBoxA and OptionButton1 accordingly

HTH

M.

 

[ExcelUser87]

Sorry, but it still isnt recognizing that BOTH items have to be selected in order to select cell C3.

Thanks for your quick response though.

 

[mikerickson]

Are you asking how to forbid C3 from being selected if one of the controls is not checked?

 

[ExcelUser87]

maybe, but i have 20 combinations that have to be fullfilled. so...

if A and 1 = true then
C3

if A and 2 = true then
C4

and it goes on like this until all possible combinations are accounted for.

i am new to macros and have only taken a basic course so i cant say that i have picked the most effective method of doing this.

I also dont know if forbidding it would be good becuase then i have to also forbid it for all the other combinations as well.

again, i am not the expert so please let me know what would be the best way of accomplishing this.

Thanks!

 

[mikerickson]

Through all combinations? That woudl be more than 20

Is this is a possible state?
Checkbox1=True, CheckBox2=True, CheckBox3=false, CheckBox4=false, CheckBox5=false, OptionButton1 = false, OptionButton2=false, OptionButton3=false, OptionButton4=True

If only one checkbox can be checked at a time, perhaps they should be Option buttons.

In any case, you can look at the 9 controls as digits in a binary number and use Select Case on that number.

I have to return to remunerative employment. I'll look further at this later if needed.

 

[ExcelUser87]

First of all, Thanks so much for your help.

i think i may still be to vague on what im trying to do so ill just lay it out as detailed as possible.

i have developed a form that will capture and input data points into multiple sheets. i have two major classifications; Activity Group and Section Group.

Activity Group (Checkboxes because multiple activity Groups can be chosen)
A, B, C, D, E

Section Group (Option Boxes because only one can be chosen)
1, 2, 3, 4

based on what combination is chosen (A, 1; A, 2; A, 3; A, 4; B, 1; B, 2; etc) a number, that is also inserted in the form, must populate a matrix.

this matrix is continuously built on (the new number entered must add to the existing number in the cell, and this part of the code is ok)

my current code looks like this...

If chkA = True And chk1 = True Then
Range("C3").Select
End If
ActiveCell.Value = ActiveCell.Value + txtEngagementNumber.Value

The matrix the form must populate looks like this...

A B C D E
1 "C3" "D3" "E3" "F3" "G3"
2 "C4"
3 "C5"
4 "C6"

Based on what combination is chosen a different cell will need to be added to. Again multiple Activity Groups can be chosen but only one Section group can be chosen.

is there a "IF and Only IF' type capability with macros? is there a better way to write this code?

Thank You!

 

[mikerickson]

If Activity Groups A and C are checked along with Selection Group 2, what happens?

Is the same data entered into both C4 & E4?
Or would the data that goes in C4 be different from what goes to E4?
Or is that "time for something completely different"

Also, are these controls in a UserForm, ActiveX controls on a sheet or Forms controls on a sheet?

 

[ExcelUser87]

If Activity Groups A and C are checked along with Selection Group 2, what happens?

Is the same data entered into both C4 & E4? well right now i have it set up so the same number will go into the two cells, however once i can get this command to work properly that will need to change. (we can engage with multiple activity groups at any specific event (classified by section group). but we might engage with 3 As but 6 Cs...this will be differentiated once i can get Excel to select the proper cell based on the proper combination)
Or would the data that goes in C4 be different from what goes to E4?
Or is that "time for something completely different"

Also, are these controls in a UserForm, ActiveX controls on a sheet or Forms controls on a sheet?
these controls are in a larger UserForm with other information as well.

 

[Marcelo Branco]

Sorry, but it still isnt recognizing that BOTH items have to be selected in order to select cell C3.

Thanks for your quick response though.

In your code is there another if(condition) before or after this?

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


Maybe another one is selecting C3.

HTH

M.