0

I would like please to assign a value to a cell by using 4 multiple choice check boxes which their value is 1 per each box if their condition is true. I would like to sum up their value in the linked cell so that cell value can vary. If for instance:

  1. all checkboxes condition is true the value in the linked cell is 4
  2. A few of them are true the value in the linked cell can vary from 1-3

  3. All of them are false the value in the linked cell is 0 

         If CheckBox1.Value = True Then Range("D2").Value = 1


     If CheckBox1.Value = False Then Range("D2").Value = 0 etc.

I wish to solve this problem by using a vba macro.

Tom
  • 65
  • 1
  • 12

3 Answers3

1

Just set the macro foreach checkbox before clicking it.

Public count As Integer

Public Sub btn_Click()
Dim cbName As String

If (count = Null) Then
    count = 0
End If

cbName = Application.Caller

If (Sheets("Tabelle1").Shapes(cbName).ControlFormat.Value = xlOn And count < 4) Then
    count = count + 1
ElseIf (count > 0) Then
    count = count - 1
End If
    Range("A1").Value = count
End Sub

enter image description here

enter image description here

TSH
  • 97
  • 9
  • I have problem Tizzu with type mismatch – Tom Aug 02 '17 at 12:32
  • 1
    you using ActiveX checkboxes i guess which makes it alot harder https://stackoverflow.com/questions/38282148/add-identical-code-to-multiple-combo-boxes If you dont care about format use FormControll Checkboxes – TSH Aug 02 '17 at 12:38
  • What would you suggest instead? – Tom Aug 02 '17 at 12:41
  • I checked again and I am using form control Checkboxes and it keeps giving type mismatch error. Any clue? – Tom Aug 02 '17 at 12:47
  • 1
    either using form control checkboxes or going though Inzina's tutorial (SO post linked above) – TSH Aug 02 '17 at 12:49
  • yes I have changed it in Sheet1. Temporarily is the name of my sheet – Tom Aug 02 '17 at 12:54
  • I mean this little guide located under “Form Controls” on the Developer Tab > Insert menu: checkbox. Is it not correct? – Tom Aug 02 '17 at 13:00
  • Public count As Integer Public Sub btn_Click() Dim CheckBox1 As String If (count = Null) Then count = 0 End If cbName = Application.Caller If (Sheets("Sheet1").Shapes(CheckBox1).ControlFormat.Value = xlOn And count < 4) Then count = count + 1 ElseIf (count > 0) Then count = count - 1 End If Range("A1").Value = count End Sub – Tom Aug 02 '17 at 13:09
  • You renamed my string variable for the checkbox's name http://imgur.com/a/xRRJb (line 7 my upload isnt working ...) but you didnt rename it where the variable gets initialized so CheckBox1 is a empty String – TSH Aug 02 '17 at 13:19
  • Public count As Integer Public Sub btn_Click() Dim CheckBox1 As String If (count = Null) Then count = 0 End If CheckBox1 = Application.Caller If (Sheets("Sheet1").Shapes(CheckBox1).ControlFormat.Value = xlOn And count < 4) Then count = count + 1 ElseIf (count > 0) Then count = count - 1 End If Range("A1").Value = count End Sub – TSH Aug 02 '17 at 13:31
  • CheckBox1 = Application.Caller Type mismatch 13 – Tom Aug 02 '17 at 13:40
  • Just tried it in excel 2016 and 2010 works fine in both for me Im sorry cant help there then – TSH Aug 02 '17 at 13:51
  • No worries, you have done your best. Thanks a lot for your help. I will mark the answer anyway. Thanks again – Tom Aug 02 '17 at 13:55
0

you can do this way. based on linked cells get the 1 if true or Zero if false. and then sum all values.

enter image description here

enter image description here

for VBA solution

    Private Sub CheckBox1_Click()
    Dim str As Integer
    str = 0
    If CheckBox1.Value = True Then str = str + 1
    If CheckBox2.Value = True Then str = str + 1
    If CheckBox3.Value = True Then str = str + 1
    If CheckBox4.Value = True Then str = str + 1
    Range("D2").Value = str
    End Sub
Maddy
  • 771
  • 5
  • 14
  • Thanks a lot but I would prefer to sort it by using a vba macro. As you can imagine this question doesn't include the big picture. – Tom Aug 02 '17 at 10:00
  • Hi Maddy thanks but I have inserted your code and while I am clicking the checkboxes there is no change to cell D2 as I would like. Do you have any insight regarding that? – Tom Aug 02 '17 at 10:29
  • Also when I run the code a window pops up saying object required. Any clue? – Tom Aug 02 '17 at 10:35
  • are you using **ActiveX Controls** check boxs? – Maddy Aug 02 '17 at 10:55
  • Yes I using them – Tom Aug 02 '17 at 12:30
0

This is the answer to my problem:

Option Explicit

   Sub CheckBox1_Click()


Dim count As Integer
If (count = Null) Then
count = 0
End If

count = 0
If ActiveSheet.Shapes("Check Box 1").ControlFormat = xlOn Then count = count + 1
If ActiveSheet.Shapes("Check Box 2").ControlFormat = xlOn Then count = count + 1
If ActiveSheet.Shapes("Check Box 3").ControlFormat = xlOn Then count = count + 1
If ActiveSheet.Shapes("Check Box 4").ControlFormat = xlOn Then count = count + 1
Range("A1").Value = count

End Sub

Tom
  • 65
  • 1
  • 12