Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically add Checkboxes to worksheet columns
All,
I have a VBA function that populates a worksheet with data. I'd like to dynamically add a checkbox to the first column of each new row that is added. I want the checkbox to be inserted and remain within the cell. The number of rows to be added is not known ahead of time. What's the best way to accomplish this? Thanks, Mark D'Agosta |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically add Checkboxes to worksheet columns
Mark
Here's a sub I use to but checkboxes next to a QueryTable. Maybe you can adapt it to your situation. Sub UpdateList() Dim sh As Worksheet Dim cell As Range Dim Rng As Range Dim chbx As OLEObject Set sh = ThisWorkbook.Worksheets("sheet1") For Each chbx In sh.OLEObjects chbx.Delete Next chbx sh.Columns(1).ClearContents sh.QueryTables(1).Refresh False Set Rng = sh.Range("b2", sh.Range("b2").End(xlDown)) sh.Range("d2:h2").Columns.AutoFit sh.Range("a1").Rows.AutoFit sh.Range("j1").ColumnWidth = 2 For Each cell In Rng.Cells cell.RowHeight = 15 With sh.OLEObjects.Add("forms.checkbox.1") .Left = cell.Offset(0, -1).Left .Top = cell.Offset(0, -1).Top .Width = cell.Offset(0, -1).Width .LinkedCell = cell.Offset(0, -1).Address .Object.Value = False .Object.Caption = "" End With Next cell Set sh = Nothing Set cell = Nothing Set Rng = Nothing Set chbx = Nothing End Sub -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Mark D'Agosta" wrote in message ... All, I have a VBA function that populates a worksheet with data. I'd like to dynamically add a checkbox to the first column of each new row that is added. I want the checkbox to be inserted and remain within the cell. The number of rows to be added is not known ahead of time. What's the best way to accomplish this? Thanks, Mark D'Agosta |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamically setting worksheet tab colors | Excel Worksheet Functions | |||
Creating a Dynamically Linked Worksheet | Excel Worksheet Functions | |||
Sum columns dynamically | Excel Worksheet Functions | |||
Can't dynamically add withevents OptionButton to worksheet | Excel Programming | |||
How to code event for dynamically generated checkboxes | Excel Programming |