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

ashley12

New Member
Joined
Dec 6, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
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
 
Last edited:

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
983
Office Version
  1. 2016
Platform
  1. Windows
What type of checkboxes are we talking here, Form Control or Activex?
 

ashley12

New Member
Joined
Dec 6, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
983
Office Version
  1. 2016
Platform
  1. Windows
For ActiveX :

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

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
983
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Here is sample how to get caption on active x

VBA Code:
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

New Member
Joined
Dec 6, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
983
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Dec 6, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
Do you think the topleftcell function will work with form control checkboxes?
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
983
Office Version
  1. 2016
Platform
  1. Windows
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.

 

Watch MrExcel Video

Forum statistics

Threads
1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top