+ Reply to Thread
Results 1 to 2 of 2

Worksheet Checkboxes if row is not blank

Hybrid View

  1. #1
    Registered User
    Join Date
    10-23-2012
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    43

    Worksheet Checkboxes if row is not blank

    I am working with a very large table with a range of $B$7:$G$16006 (with the headers in row 6) that is updated dynamically through sheet formulas.

    My issue is related with inserting a checkbox (Form control type) next to each row in cell $A7 if the formula in column C (second column in my table) does not result in a "" or "0".
    The formula I am working with is:

    Sub AddCheckBoxToCell(Ref_Cell As Range)
     
      Dim ChkBox As CheckBox
      Dim N As Double
     
        With Ref_Cell.Cells(1, 1)
          RefLeft = .Left
          refTop = .Top
          refHeight = .Height
        End With
     
          Set ChkBox = ActiveSheet.CheckBoxes.Add(10, 10, 15, 12)
          N = (refHeight - ChkBox.Height) / 2#
      
          With ChkBox
            .Caption = ""
            .Top = refTop + N
            .Left = RefLeft
            .OnAction = ""
          End With
     
    End Sub
     
    Private Sub Worksheet_Activate()
     
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
      Dim BoxCol As Variant
      Dim CtrlCol As Variant
      Dim LastRow As Long
      Dim R As Long
      Dim StartRow As Long
     
      For Each c In Sheets("Sheet1").CheckBoxes
        c.Delete
      Next
     
        BoxCol = "A"     'Column where Check Box is inserted
        CtrlCol = "C"    'Column that controls if Check Box is inserted
        StartRow = 7
        LastRow = Cells(Rows.Count, CtrlCol).End(xlUp).Row
       
            For R = StartRow To LastRow
              If Cells(R, CtrlCol) <> 0 Then
                AddCheckBoxToCell Cells(R, BoxCol)
              End If
            Next R
               
    End Sub
    The problem with this is that 1) this takes forever to execute/freezes excel due to the number of table rows to loop through (16000) and 2) I cannot figure out how to incorporate the "LinkedCell" code needed to insert TRUE/FALSE in to the address cell of each checkbox.

    I have also been looking at "Marlett checkboxes" however cannot figure out how to create the code to only add the Marlett feature to a cell in column A if the row/cell in column C is not "" or "0":

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     
        If Target.Cells.Count > 1 Then Exit Sub
            If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
                Target.Font.Name = "Marlett"
     
                    If Target = vbNullString Then
                        Target = "a"
                    Else
                        Target = vbNullString
                    End If
            End If
    End Sub
    Suggestion are greatly appreciated, apologies for the long post but I am trying to find the best approach!

  2. #2
    Registered User
    Join Date
    10-23-2012
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Worksheet Checkboxes if row is not blank

    Update: went with the Marlett checkboxes, only needed to modify the If statement:
     If Target = vbNullString And Target.Offset(0,2).Value= 0 Then

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1