VBA Userform Passing Data Between Checkbox and WS Cell

[DixiePiper]

I'm not even sure how to ask this question. I am working to export data, one sheet at a time, from a variable series of worksheets to a master table on another sheet. I'm trying to design/develop a userform with the export code and a checklist to keep track of what I've done. So far the userform initializes with checkboxes based on a list of companies in a named table. If the data for that company has already been exported, there should be an "x" in the "Done" column. Right now, when the userform loads, the checkbox loads with a value of TRUE if the "Done" column contains = "x". Where I'm struggling is on how to check the box on the userform and update the "Done" column accordingly.

There are a variety of reasons this needs to be dynamic: the company list can change during the course of the project, the total lines of data on the master table can be hundreds and hundreds of rows, and I often get interrupted. I need a way to see where I am in this particular step of the project.

The code that creates the checkboxes looks like this: (Exel 2013; Windows 10)

Private Sub UserForm_Initialize()
    Dim newChkBx As MSForms.CheckBox
    Dim rngSrc, rngCell As Range
    Dim topPos As Integer
    Dim maxWidth As Long
    
    Set rngSrc = Worksheets("Admin - General").Range("Team[Abbr.]")
    
    topPos = 90
    maxWidth = 261
    
    For Each rngCell In rngSrc
        If rngCell.value <> "" Then
            Set newChkBx = Me.Controls.Add("Forms.Checkbox.1")
            With newChkBx
                .Caption = rngCell.value
                .Font.name = "Calibri"
                .Font.Size = 11
                .Left = 12
                .Top = topPos
                .AutoSize = True
                If .Width > maxWidth Then maxWidth = .Width
                If rngCell.Offset(, -2).value = "x" Then
                    newChkBx.value = True
                End If
            End With
            topPos = topPos + 18
        End If
    Next rngCell
    
    Me.Width = maxWidth
    Me.Height = topPos + 36
    
End Sub

I need to be able to check a box on the userform and then have the "finish" button update the appropriate cell in the source table. The table looks like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Done[/TD]
[TD]Company Name[/TD]
[TD]Abbr.[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Company A[/TD]
[TD]CompA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Company B[/TD]
[TD]CompB[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Company C[/TD]
[TD]CompC[/TD]
[/TR]
</tbody>[/TABLE]

With the above code, when the userform initializes, the checkboxes for each company appear and the checkbox for Company A is checked. However, I need to be able to check Company B on the userform and update the table accordingly.

TIA

 

[Computerman]

DixiePiper,
I ran into the same type of problem with a project I am creating. Although my issue was with ComboBox'es, the solution should be the same. You will need to name the Checkboxes immediately after you create them. I created a subroutine that has the following code:

Sub ComboBoxRename()
For Each CtRl In ActiveSheet.OLEObjects
        If TypeName(CtRl.Object) = "ComboBox" Then
            If CtRl.Name = "ComboBox1" Then
                    CtRl.Name = "cmbTZ" & (RC - 3)
            End If
        End If
    Next

End Sub

just call this subroutine immediately after the End With line in your code, and change my the ComboBox in the typename line to Checkbox and change the ComboBox1 to Checkbox1 in the if line and finally change the name you want your checkbox to have in the Ctrl.name line. I also have a way to delete all the Comboxes (checkboxes) when the form is cleared if you need it.

Hope that helps,
Computerman

 

[Norie]

Instead of creating checkboxes dynamically why not use a listbox?

It could be populated with the company name and abbreviation and set up to have a checkbox on each row.

That checkbox could be set true/false based on a column in your master table, presuming you have a column to show if a company has already been dealt with.

 

[DixiePiper]

Computerman,
Right now I'm working with a userform vs. objects embedded into the worksheet. I appreciate the input and I'll definitely cache this away for future reference.

DixiePiper

 

[DixiePiper]

Instead of creating checkboxes dynamically why not use a listbox?

It could be populated with the company name and abbreviation and set up to have a checkbox on each row.

That checkbox could be set true/false based on a column in your master table, presuming you have a column to show if a company has already been dealt with.

Norie,
Appreciate the feedback. I played around with using a listbox but it wasn't giving me the functionality I needed. I want to be able to check the box and have that passed back to the source table so the next time the form is called, the box for that company shows as checked/completed.

I've been playing around with this, following various rabbit holes and I've made some progress. The initialize code has been amended to name each textbox with "chk" & the rngCell value. From there, I've added an "update" button with the following loop to return the name of only those boxes that are checked:

    For Each ctrl In Me.Controls        
        If InStr(ctrl.name, "chk") Then
            If ctrl.value = True Then
                targetVal = ctrl.Caption
                MsgBox targetVal
            End If
        End If
        
    Next ctrl

What I'm trying to figure out is how to take the targetVal and use it in a Range.Find (or similar) within the original source table to find the cell address in the "Abbr." column and then use and Offset to update the "Done" column.

Thoughts? I am very new to VBA programming and that I've figured out this much on my own is a miracle

 

[Norie]

It's really easy to pass the status of the checked box back to the table.

What exactly do you have, e.g. columns etc, do you have in your table?

Can you post some example data?

 

[DixiePiper]

Norie,
I've had no issue passing data back from checkboxes I created & named. The challenge with this was isolating only the boxes that were checked and then matching the textbox caption to correct row in the table and updating another column. I went back to tinkering with the Range.Find method and got it to work. The final code looks like this:

Private Sub btnExpCont_Click()
    Dim aGen As Worksheet    
    Dim rngSrc, rngFnd As Range
    Dim targetVal As String
    
    Set aGen = Worksheets("Admin - General")
    Set rngSrc = aGen.Range("Team[Abbr.]")
    
    Application.ScreenUpdating = False
    
    For Each ctrl In Me.Controls
        If InStr(ctrl.name, "chk") Then
            If ctrl.value = True Then
                targetVal = ctrl.Caption
                
                On Error Resume Next
                With rngSrc
                    Set rngFnd = .Find(targetVal, After:=.Cells(1, 1))
                On Error GoTo 0
                    If Not rngFnd Is Nothing Then Application.Goto rngFnd, True
                    rngFnd.Offset(, -2).value = "x"
                End With
                    
            End If
        End If
        
    Next ctrl
    
    Application.ScreenUpdating = True
        
End Sub

It needs a few tweaks but this accomplishes exactly what I needed. I think my issue with the Range.Find was in properly setting error handling as well as figuring out the correct syntax for the actual .Find method.

Again, I appreciate your offer of assistance. I'm pretty stoked that I figured this out on my own.

DixiePiper