Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Batch-modify checkboxes on Excel

Where are the checkboxes? I'm gonna assume that they're on a worksheet.

Are the checkboxes from the Forms toolbar or the control toolbox toolbar?

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim OLEObj As OLEObject
Dim CBX As CheckBox

Set wks = Worksheets("Sheet1")

'checkboxes from the Forms toolbar
For Each CBX In wks.CheckBoxes
With CBX
.LinkedCell = .TopLeftCell.Address(external:=True)
.TopLeftCell.NumberFormat = ";;;"
End With
Next CBX

'checkboxes from the control toolbox toolbar
For Each OLEObj In wks.OLEObjects
With OLEObj
If TypeOf .Object Is MSForms.CheckBox Then
.LinkedCell = .TopLeftCell.Address(external:=True)
.TopLeftCell.NumberFormat = ";;;"
End If
End With
Next OLEObj

End Sub




lillyanka wrote:

Is there a way to modify several checkboxes at the same time on excel?

I have about 30 checkboxes, and I need to link each one of them to a cell.
Is there a way to do it without going to the properties and changing them one
by one?

Thanks a lot!


--

Dave Peterson
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Batch-modify checkboxes on Excel

They're from the control toolbox toolbar.

Thanks for your help!!

"Dave Peterson" wrote:

Where are the checkboxes? I'm gonna assume that they're on a worksheet.

Are the checkboxes from the Forms toolbar or the control toolbox toolbar?

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim OLEObj As OLEObject
Dim CBX As CheckBox

Set wks = Worksheets("Sheet1")

'checkboxes from the Forms toolbar
For Each CBX In wks.CheckBoxes
With CBX
.LinkedCell = .TopLeftCell.Address(external:=True)
.TopLeftCell.NumberFormat = ";;;"
End With
Next CBX

'checkboxes from the control toolbox toolbar
For Each OLEObj In wks.OLEObjects
With OLEObj
If TypeOf .Object Is MSForms.CheckBox Then
.LinkedCell = .TopLeftCell.Address(external:=True)
.TopLeftCell.NumberFormat = ";;;"
End If
End With
Next OLEObj

End Sub




lillyanka wrote:

Is there a way to modify several checkboxes at the same time on excel?

I have about 30 checkboxes, and I need to link each one of them to a cell.
Is there a way to do it without going to the properties and changing them one
by one?

Thanks a lot!


--

Dave Peterson

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel to Run a Batch File?!? carla New Users to Excel 3 January 20th 07 03:32 AM
Batch renaming of many worksheets in Excel files? Lumen S Excel Discussion (Misc queries) 2 August 17th 06 09:48 PM
Can I batch print several files at once with Excel? blueberry Excel Discussion (Misc queries) 0 July 19th 06 09:44 PM
Sending batch emails via Excel treetop40 Excel Discussion (Misc queries) 0 April 24th 06 11:22 AM
Call a batch file from an Excel Macro Alex Horan Excel Discussion (Misc queries) 0 March 2nd 06 04:29 PM


All times are GMT +1. The time now is 06:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"