Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Check Boxes to Many Cells
Hello,
I have a spreadsheet that I need to put a Check Box in every cell in the cell range of B2 to BO413; I would like to do this automatically using VBA code to fill the cell range I mentioned. I would like to put a button on the sheet and just click the button to add the check boxes. Could someone please help me with the code I need to acomplish this task? Any help will be greatly appreciated. Thank you, Dave Y |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Check Boxes to Many Cells
Dave
See he http://www.dicks-blog.com/archives/2...ksheet-in-vba/ That should get you started, but post back if you need more help. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Dave Y wrote: Hello, I have a spreadsheet that I need to put a Check Box in every cell in the cell range of B2 to BO413; I would like to do this automatically using VBA code to fill the cell range I mentioned. I would like to put a button on the sheet and just click the button to add the check boxes. Could someone please help me with the code I need to acomplish this task? Any help will be greatly appreciated. Thank you, Dave Y |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Check Boxes to Many Cells
Mr. Kusleika,
Thank you very much for your reply. The code seems to work although it keeps freezing my computer after few minutes. I will keep playing with it. I greatly appreciate your help. Dave Y -----Original Message----- Dave See he http://www.dicks-blog.com/archives/2004/10/06/adding- activex-controls-to-worksheet-in-vba/ That should get you started, but post back if you need more help. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Dave Y wrote: Hello, I have a spreadsheet that I need to put a Check Box in every cell in the cell range of B2 to BO413; I would like to do this automatically using VBA code to fill the cell range I mentioned. I would like to put a button on the sheet and just click the button to add the check boxes. Could someone please help me with the code I need to acomplish this task? Any help will be greatly appreciated. Thank you, Dave Y . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Check Boxes to Many Cells
The checkboxes from the Forms toolbar are less taxing on the system:
Option Explicit Sub addCBX() Dim myCBX As CheckBox Dim myCell As Range With ActiveSheet .CheckBoxes.Delete 'nice for testing For Each myCell In ActiveSheet.Range("B2:BO413").Cells With myCell Set myCBX = .Parent.CheckBoxes.Add _ (Top:=.Top, Width:=.Width, _ Left:=.Left, Height:=.Height) With myCBX .LinkedCell = myCell.Address(external:=True) .Caption = "" 'or whatever you want .Name = "CBX_" & myCell.Address(0, 0) End With .NumberFormat = ";;;" End With Next myCell End With End Sub But that's a lot of checkboxes. One more option: Maybe just give the cells a custom format. Format the cells by: selecting them format|cells|number tab|custom category In the "type:" box, put this: alt-0252;alt-0252;alt-0252;alt-0252 But hit and hold the alt key while you're typing the 0252 from the numeric keypad. It should look something like this when you're done. ü;ü;ü;ü (umlaut over the lower case u separated by semicolons) And format that range of cells as Wingdings. Now, no matter what you type (spacebar, x, anyoldtextatall), you'll see a check mark. Hit the delete key on the keyboard to clear the cell. If you have to use that "checkmark" in later formulas: =if(a1="","no checkmark","Yes checkmark") You can just see if the cell is empty. wrote: Mr. Kusleika, Thank you very much for your reply. The code seems to work although it keeps freezing my computer after few minutes. I will keep playing with it. I greatly appreciate your help. Dave Y -----Original Message----- Dave See he http://www.dicks-blog.com/archives/2004/10/06/adding- activex-controls-to-worksheet-in-vba/ That should get you started, but post back if you need more help. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Dave Y wrote: Hello, I have a spreadsheet that I need to put a Check Box in every cell in the cell range of B2 to BO413; I would like to do this automatically using VBA code to fill the cell range I mentioned. I would like to put a button on the sheet and just click the button to add the check boxes. Could someone please help me with the code I need to acomplish this task? Any help will be greatly appreciated. Thank you, Dave Y . -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Check Boxes to Many Cells
Just to add...
When I ran this code, I broke out of the routine and it wasn't half done--after a few minutes. I think I'd use the formatting option for this many cells. Dave Peterson wrote: The checkboxes from the Forms toolbar are less taxing on the system: Option Explicit Sub addCBX() Dim myCBX As CheckBox Dim myCell As Range With ActiveSheet .CheckBoxes.Delete 'nice for testing For Each myCell In ActiveSheet.Range("B2:BO413").Cells With myCell Set myCBX = .Parent.CheckBoxes.Add _ (Top:=.Top, Width:=.Width, _ Left:=.Left, Height:=.Height) With myCBX .LinkedCell = myCell.Address(external:=True) .Caption = "" 'or whatever you want .Name = "CBX_" & myCell.Address(0, 0) End With .NumberFormat = ";;;" End With Next myCell End With End Sub But that's a lot of checkboxes. One more option: Maybe just give the cells a custom format. Format the cells by: selecting them format|cells|number tab|custom category In the "type:" box, put this: alt-0252;alt-0252;alt-0252;alt-0252 But hit and hold the alt key while you're typing the 0252 from the numeric keypad. It should look something like this when you're done. ü;ü;ü;ü (umlaut over the lower case u separated by semicolons) And format that range of cells as Wingdings. Now, no matter what you type (spacebar, x, anyoldtextatall), you'll see a check mark. Hit the delete key on the keyboard to clear the cell. If you have to use that "checkmark" in later formulas: =if(a1="","no checkmark","Yes checkmark") You can just see if the cell is empty. wrote: Mr. Kusleika, Thank you very much for your reply. The code seems to work although it keeps freezing my computer after few minutes. I will keep playing with it. I greatly appreciate your help. Dave Y -----Original Message----- Dave See he http://www.dicks-blog.com/archives/2004/10/06/adding- activex-controls-to-worksheet-in-vba/ That should get you started, but post back if you need more help. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Dave Y wrote: Hello, I have a spreadsheet that I need to put a Check Box in every cell in the cell range of B2 to BO413; I would like to do this automatically using VBA code to fill the cell range I mentioned. I would like to put a button on the sheet and just click the button to add the check boxes. Could someone please help me with the code I need to acomplish this task? Any help will be greatly appreciated. Thank you, Dave Y . -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to link check boxes to 3 different cells | Excel Worksheet Functions | |||
Check boxes linked to other cells? | Excel Worksheet Functions | |||
Clearing Check Boxes and Cells | Excel Discussion (Misc queries) | |||
Clearing Check Boxes and Cells | Excel Discussion (Misc queries) | |||
Clearing Check Boxes and Cells | Excel Worksheet Functions |