Home |
Search |
Today's Posts |
#1
|
|||
|
|||
check boxes - copy
I would like to copy (hundreds) of check boxes in a spreadsheet. The
checkbox must be assigned to a cell to work in a formula. When I copy the checkbox down however, all check boxes will either be checked, or unchecked. Is there a way to copy check boxes, when they are assigned to another cell, so that each check box can be used individually? I created the check boxes through the forms toolbar. Thanks for your help, this is a great forum and I only hope to give some day as much help as I am currently receiving! |
#2
|
|||
|
|||
check boxes - copy
Hey, Mark-
Things got busy yesterday, and I missed your post. You can think of a checkbox as a control that is layered on top of your spreadsheet and works with it- but when you copy a checkbox you're copying that control and not the cell references within it. That's why you the "refers to" cell in the checkbox parameters don't change when you move the checkbox. I wrote some code that will create a number of checkboxes that you specify, starting on a row that you specify. It creates a new checkbox for each row and a unique cell reference on that same row. Depending on how your spreadsheet it arranged and formatted, you may need to change some things in the following lines of code. In this line, ActiveSheet.CheckBoxes.Add(10.5, 0 + ((K - 1) * 12.75), 5, 10).Select 10.5 is the horizontal distance from the left margin of your sprdsht 0 + ((K - 1) * 12.75 is the vertical distance from the top of the sprdsht 5 is the control width 10 is the control height The 10.5 setting lands the checkbox in column A- you may want to adjust that The 0 + ((K - 1) * 12.75 setting assumes the default row height of 12.75. You may need to adjust that to match the row height of your spreadsheet. This line Adrs = "B" & K ....situates the TRUE / FALSE cell in column B Input windows will appear asking which row to start on, and how many checkboxes to create. The code follows: Sub Many_Checkboxes() Dim Adrs As String Dim K As Long Dim Beg As Long, Fin As Long Beg = InputBox("Start on what row?") Fin = InputBox("How many checkboxes?") For K = Beg To Fin Adrs = "B" & K ActiveSheet.CheckBoxes.Add(10.5, 0 + ((K - 1) * 12.75), 5, 10).Select Selection.Characters.Text = "" With Selection .Value = xlOn .LinkedCell = Adrs .Display3DShading = False End With Next K End Sub |
#3
|
|||
|
|||
check boxes - copy
Thanks Dave, your solution sounds exactly what I was looking for.
Thanks for all your help, I appreciate it very much. Mark "Dave O" wrote: Hey, Mark- Things got busy yesterday, and I missed your post. You can think of a checkbox as a control that is layered on top of your spreadsheet and works with it- but when you copy a checkbox you're copying that control and not the cell references within it. That's why you the "refers to" cell in the checkbox parameters don't change when you move the checkbox. I wrote some code that will create a number of checkboxes that you specify, starting on a row that you specify. It creates a new checkbox for each row and a unique cell reference on that same row. Depending on how your spreadsheet it arranged and formatted, you may need to change some things in the following lines of code. In this line, ActiveSheet.CheckBoxes.Add(10.5, 0 + ((K - 1) * 12.75), 5, 10).Select 10.5 is the horizontal distance from the left margin of your sprdsht 0 + ((K - 1) * 12.75 is the vertical distance from the top of the sprdsht 5 is the control width 10 is the control height The 10.5 setting lands the checkbox in column A- you may want to adjust that The 0 + ((K - 1) * 12.75 setting assumes the default row height of 12.75. You may need to adjust that to match the row height of your spreadsheet. This line Adrs = "B" & K ....situates the TRUE / FALSE cell in column B Input windows will appear asking which row to start on, and how many checkboxes to create. The code follows: Sub Many_Checkboxes() Dim Adrs As String Dim K As Long Dim Beg As Long, Fin As Long Beg = InputBox("Start on what row?") Fin = InputBox("How many checkboxes?") For K = Beg To Fin Adrs = "B" & K ActiveSheet.CheckBoxes.Add(10.5, 0 + ((K - 1) * 12.75), 5, 10).Select Selection.Characters.Text = "" With Selection .Value = xlOn .LinkedCell = Adrs .Display3DShading = False End With Next K End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check Boxes | Excel Discussion (Misc queries) | |||
Column of check boxes | Excel Worksheet Functions | |||
Copy drop down boxes and associate to cells dynamically in excel | Excel Worksheet Functions | |||
check boxes that highlight row | Excel Discussion (Misc queries) | |||
Count Check Boxes | Excel Worksheet Functions |