Results 1 to 5 of 5

Thread: Checkbox Change Procedure
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Checkbox Change Procedure

    Hi all,

    I have a load of checkboxes where once ticked (or unticked) put a "X" into a cell.

    Instead of:

    Code:
        If Me.chkTempWarm.Value = True Then
            wsMain.Range("H25").Value = "X"
        ElseIf Me.chkTempWarm.Value = False Then
            wsMain.Range("H25").Value = ""
        End If
    
        If Me.chkTempMild.Value = True Then
            wsMain.Range("K25").Value = "X"
        ElseIf Me.chkTempMild.Value = False Then
            wsMain.Range("K25").Value = ""
        End If
    So on a so forth,

    Is there a simpler way to loop through all the checkboxes and, and once one is ticked, it will put the "X" in the relevant cell.

  2. #2
    Board Regular bobsan42's Avatar
    Join Date
    Jul 2010
    Location
    Bulgaria, GMT+2 (42.891813,25.313594)
    Posts
    1,281
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Checkbox Change Procedure

    Are the checkboxes on a form ? From you code it seems yes.

    One way is to set the controlsource property of the checkboxes on the form. Something like: ='Sheet3 (2)'!A24
    Then Upon changing the value of the chkbox the value of the linked cell will change between TRUE and FALSE (this can then be transfered as formula if you need to see an X to another cell. no coding here.

    Second way is to loop through all controls:
    Code:
    dim chb as control, str1 as string, varval as variant
     for each chb in Me.Controls
      select case chb.name
       case "chkTempWarm": str1 = "H25"
       case "chkTempMild": str1 = "K25"
       case else: str1 = ""
      end select
      if str1 <>"" then 
       if chb.value then varval="X" else varval=""
      else
       varval=null
      end if
      if not isnull(varval) then wsMain.range(str1).value = varval
     next chb
    "...it's sad that in our blindness we gather thorns for flowers..."
    mostly using:
    windows 7 +10 (64-bit) / excel 2013 +2016 (32-bit) / access 2013 +2016 (32-bit) / some imagination & Google of course
    You don't need to read between the lines - just read them all!

  3. #3
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,887
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Checkbox Change Procedure

    Another way
    - create 2 parallel arrays to hold the matching names \ cell references
    - place procedure in SHEET module if checkboxes are on a worksheet or in USERFORM module (if on UserForm)

    Code:
    Sub XorNothing()
        Dim wsMain As Worksheet: Set wsMain = Sheets("MainSheet")
        Dim CBs, Refs, i As Long
    'create arrays of parallel values 
        CBs = Array(chkTempWarm, chkTempMild, chkTempCool, chkTempCold)
        Refs = Array("H25", "J25", "L25", "N25")
    'loop 
        For i = LBound(CBs) To UBound(CBs)
            wsMain.Range(Refs(i)).ClearContents
            If CBs(i) Then wsMain.Range(Refs(i)) = "X"
        Next i
    End Sub
    Last edited by Yongle; Mar 27th, 2019 at 08:15 AM.

  4. #4
    New Member
    Join Date
    Aug 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Checkbox Change Procedure

    Fantastic bobsan42, that code works a treat. Is there a way to go the other way, so if there is an X in a range, the CheckBox value is True?

    Code:
    Sub GetData()
    Dim rng As Range
        For Each rng In wsMain
            Select Case wsMain.Range
                Case "H25" = "X": frmRGT.chkTempWarm.Vlaue = True
            End Select
        Next rng
        
    End Sub

  5. #5
    Board Regular bobsan42's Avatar
    Join Date
    Jul 2010
    Location
    Bulgaria, GMT+2 (42.891813,25.313594)
    Posts
    1,281
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Checkbox Change Procedure

    If you link the checkboxes to cells you will not need this code. Or even the one above.

    However your code should look like this if you want it to work:
    Code:
    Sub GetData()
    Dim rng As Range
    
        For Each rng In wsMain.Range ("H25:H50")
        with rng
            Select Case .address
                Case "$H$25": frmRGT.chkTempWarm.Vlaue = (.Value="X")
                Case "$K$25": frmRGT.chkTempMild.Vlaue = (.Value="X")
                Case .................
            End Select
        end with
        Next rng
    End Sub
    You need to adjust the bits in blue.
    "...it's sad that in our blindness we gather thorns for flowers..."
    mostly using:
    windows 7 +10 (64-bit) / excel 2013 +2016 (32-bit) / access 2013 +2016 (32-bit) / some imagination & Google of course
    You don't need to read between the lines - just read them all!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •