none
Progress bar RRS feed

  • Question

  • Hi, 

    I've created a checklist by using the checkbox from developer tab.

    I like to create a progress bar to keep track of how much I have tick off from the checklist.

    eg. If I have 100 checkbox, then when I tick off 50 of them, I can know from the progress bar that I've done 50%.

    Anyway I can do that?

    Friday, July 1, 2016 8:06 PM

All replies

  • This should do it.  These are the Active X CheckBoxes not Form Controls

    Function CheckBoxPercent() As Single
        Dim obj As OLEObject
        Dim ws As Worksheet
        Dim cnt As Integer
        Dim chk As Integer
        
        Set ws = Worksheets("Sheet1")
         
        For Each obj In ws.OLEObjects
          If obj.progID = "Forms.CheckBox.1" Then
            cnt = cnt + 1
            If obj.Object.Value = True Then
              chk = chk + 1
            End If
          End If
        Next obj
        CheckBoxPercent = chk / cnt
    End Function
    
    Sub Test()
      MsgBox CheckBoxPercent
    End Sub

    Friday, July 1, 2016 11:36 PM
  • When I click run, it says the user defined type is not define though
    Saturday, July 2, 2016 4:27 AM
  • This should do it.  These are the Active X CheckBoxes not Form Controls

    Function CheckBoxPercent() As Single
        Dim obj As OLEObject
        Dim ws As Worksheet
        Dim cnt As Integer
        Dim chk As Integer
        
        Set ws = Worksheets("Sheet1")
         
        For Each obj In ws.OLEObjects
          If obj.progID = "Forms.CheckBox.1" Then
            cnt = cnt + 1
            If obj.Object.Value = True Then
              chk = chk + 1
            End If
          End If
        Next obj
        CheckBoxPercent = chk / cnt
    End Function
    
    Sub Test()
      MsgBox CheckBoxPercent
    End Sub

    I got what you means, but there is some problem
    1. It only work in Excel
    2. It doesn't show me a progress bar, just a percentage..
    Saturday, July 2, 2016 4:42 AM
  • I thought you had the progress bar on the sheet.  You need to put a progress bar on sheet (Microsoft ProgressBarControl, version 6.0).  You can either name ProgressBar (say MyProgressPB) and set it directly or find it and set it.  This code will update value.


    Dim ws As Worksheet
    Dim pb As OLEObject

    Function CheckBoxPercent() As Single
        Dim obj As OLEObject
        Dim cnt As Integer
        Dim chk As Integer

        Set ws = Worksheets("Sheet1")

        For Each obj In ws.OLEObjects
          If obj.progID = "MSComctlLib.ProgCtrl.2" Then
            Set pb = obj
          End If
          If obj.progID = "Forms.CheckBox.1" Then
            cnt = cnt + 1
            If obj.Object.Value = True Then
              chk = chk + 1
            End If
          End If
        Next obj
        CheckBoxPercent = chk / cnt * 100
    End Function

    Sub Test()

    ' 2 ways to update progress bar.

      pb.Object.Value = CheckBoxPercent
      ws.OLEObjects("MyProgressPB").Object.Value = CheckBoxPercent
    End Sub

             




    • Edited by mogulman52 Saturday, July 2, 2016 4:14 PM
    Saturday, July 2, 2016 2:36 PM