Excel VBA. How to write checkbox captions into the cell that contains it

[ashley12]

Hi everyone, thank you for your time.

I'm building an Excel form that transfers info into another sheet in the same workbook. However, some of the cells contains checkboxes, and I want to write the captions of these checkboxes into the corresponding cells before transfering all the data into the other sheet.

Below is my code. The indented part is where I try to write the checkboxes captions. All of typed data is recorded successfully but checkboxes aren't, and I don't know why.
Could you please show me where I got it wrong?

Option Explicit

Sub save()
    Dim form As Worksheet
    Dim database As Worksheet
    Dim database_row As Long
    Dim database_column As Integer
    Dim form_row As Integer
  
    Set form = ThisWorkbook.Sheets("Form")
    Set database = ThisWorkbook.Sheets("Database")
    form_row = 5
  
                    'add value of checkboxes to corresponding cells
                    Dim cb As CheckBox
                   
                    For Each cb In form.CheckBoxes
                        If cb.Value = True Then
                        cb.toplelfcell = cb.TopLeftCell & cb.Caption & vbCrLf
                        End If
                    Next cb
  
    'define database row number
    database_row = database.Range("A" & Application.Rows.Count).End(xlUp).Row + 1
  
  
    'transfer data from form to database
    For database_column = 1 To 49
      
        'look for rows in the from with data
        While form.Cells(form_row, 3) = ""
        form_row = form_row + 1
        Wend
      
        'record data
        database.Cells(database_row, database_column) = form.Cells(form_row, 5)
     
       'reset form field to empty
        form.Cells(form_row, 5) = ""
      
        form_row = form_row + 1

        Next database_column
      

End Sub

 

[Zot]

What type of checkboxes are we talking here, Form Control or Activex?

 

[ashley12]

What type of checkboxes are we talking here, Form Control or Activex?

Hi Zot, I used Acticex since I think they are more flexible. But now I think about it I haven’t considered if they need to be referred differently. Would love to hear some advice from you.

 

[Zot]

For ActiveX :

For Each obj In ws.OLEObjects
      If TypeName(obj.Object) = "CheckBox" Then
             ' your code here
      End If
Next obj

 

[Zot]

Here is sample how to get caption on active x

Sub test()

Dim ws As Worksheet
Dim strX$

Set ws = ActiveSheet

For Each obj In ws.OLEObjects
    If TypeName(obj.Object) = "CheckBox" Then
        strX = obj.Object.Caption
    End If
Next

End Sub

 

[ashley12]

Thanks. I just have a question. Do you need to declare obj as a variable? And do you know a way to refer to the cell that contain the object?

 

[Zot]

Thanks. I just have a question. Do you need to declare obj as a variable? And do you know a way to refer to the cell that contain the object?

Dim obj as object.

The activex object does not reside in cell, right?. It can be anywhere in worksheet. Macro will loop through all object (any type in this case) and then if the object is CheckBox, then you will execute your task.

Try Google Loop Through ActiveX object... or something like that.

 

[ashley12]

Do you think the topleftcell function will work with form control checkboxes?

 

[Zot]

Do you think the topleftcell function will work with form control checkboxes?

Although both Form Control and ActiveX checkbox so not reside (snap to) in cell but like hover above the sheet, they can be positioned to any specific cell.

I see part of the article talk about placing the checkbox.

Excel ActiveX Checkbox - Complete Tutorial With VBA

Excel ActiveX checkboxes. In this tutorial, we show how you can create many checkboxes using Excel VBA and the ActiveX controls.

softtechtutorials.com