how to assign macro for 8 checkbox with different condition.

[yudi_rambutan]

Dear Mr.Excel

I have 8 checkbox with name(A,B,C,D,E,F,G,H) and I have 8 cell.
If one of the cell(A1 until A7) I type "A" so checkbox A Will be checked(enabled).
so..how to program it use macroexcel??please help to support me.

 

[Damon Ostrander]

Hi Yudi,

There is a need for quite a bit of clarification of your problem before an answer can be given.

1. Why did you mention A1:A7? In your example you only enter text in A1. Should another checkbox get checked if its letter is entered in A2?

2. Should checkbox A get automatically unchecked if "A" is subsequently removed from A1?

3. If the value in A1 is edited to "C" should checkbox A be automatically unchecked and checkbox C checked?

4. What if you want to check all 8 checkboxes? You mentioned 8 cells, but then gave only A1:A7? Did you intend A1:A8?

5. Can letters be entered in A1:A7 in any order? If A1="D", A2="A", A3="F", should checkboxes A, D, F be checked?

Damon

 

[yudi_rambutan]

Dear Mr.Damon..

Thank you very much for your help.
I am very sorry for not clear question..

let me explain:
yes,you're correct I mean cell/range from A1:A8(8 cell).
if I type "A" then the checkbox with name A will be check automatically....
then if I continue type "B" at cell A1, the checkbox with name A will be unchecked...
it will be checked condition at checkbox with the name B.

this condition is same with other cell and checkbox.
I try use code below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("A1") = "A" Or Range("A2") = "A" Or Range("A3") = "A" Or Range("A4") = "A" Or Range("A5") = "A" Or Range("A6") = "A" Or Range("A7") = "A" Or Range("A8") = "A" Then
CheckBox1.Value = 1
Else
CheckBox1.Value = 0
If Range("A1") = "B" Or Range("A2") = "B" Or Range("A3") = "B" Or Range("A4") = "B" Or Range("A5") = "B" Or Range("A6") = "B" Or Range("A7") = "B" Or Range("A8") = "B" Then CheckBox2.Value = 1
Else
CheckBox2.Value = 0
...
...
...until checkbox 8
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub


I've try it, the code is conflic not function like I want it..
could you please help me??

 

[Damon Ostrander]

Hi again Yudi,

Okay, I believe I understand what you want now. Here is code I would recommend. Notice that I used the worksheet Change rather than SelectionChange event, because the check only needs to be done if one of the cells in the sheet is changed or edited.

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Target, Range("A1:A8")) Is Nothing Then
      With Application.WorksheetFunction
         If .CountIf(Range("A1:A8"), "A") = 0 Then
            CheckBox1.Value = 0
         Else
            CheckBox1.Value = 1
         End If
         If .CountIf(Range("A1:A8"), "B") = 0 Then
            CheckBox2.Value = 0
         Else
            CheckBox2.Value = 1
         End If
         If .CountIf(Range("A1:A8"), "C") = 0 Then
            CheckBox3.Value = 0
         Else
            CheckBox3.Value = 1
         End If
         If .CountIf(Range("A1:A8"), "D") = 0 Then
            CheckBox4.Value = 0
         Else
            CheckBox4.Value = 1
         End If
         If .CountIf(Range("A1:A8"), "E") = 0 Then
            CheckBox5.Value = 0
         Else
            CheckBox5.Value = 1
         End If
         If .CountIf(Range("A1:A8"), "F") = 0 Then
            CheckBox6.Value = 0
         Else
            CheckBox6.Value = 1
         End If
         If .CountIf(Range("A1:A8"), "G") = 0 Then
            CheckBox7.Value = 0
         Else
            CheckBox7.Value = 1
         End If
         If .CountIf(Range("A1:A8"), "H") = 0 Then
            CheckBox8.Value = 0
         Else
            CheckBox8.Value = 1
         End If
      End With
   End If

End Sub

Let me know if this code doesn't do what you want.

And Keep Excelling.

Damon

 

[yudi_rambutan]

Dear Mr.Damon..

Thank you very much..you're great..
this code is very usefully ..
...