Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding multiple checkboxes
I am creating a large data entry excel spreadsheet and I want to be able to
insert, probably 100+ check boxes, my problem is that I dont want to have to associate every single checkbox with every single cell. is there a way that I can just click and drag (like fomulas do) and have the cells be populated by checkboxes. I do not want to have to right click each checkbox -- properties -- control, and then link the value to a cell. Please help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding multiple checkboxes
Here's a macro by Dave Peterson:
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("a2:a10").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 Change the range (set to A2:A10) as needed. The macro automatically sets the linked cell to be the same cell the checkbox is "in". It also sets the linked cell font format to be "invisible". If you want custom captions for each checkbox you have to do that manually. If you want no caption "un REM" the line: '.Caption = "" 'or whatever you want To: ..Caption = "" 'or whatever you want Note: make sure the column is wide enough (about ~130 pixels) otherwise the default caption could get truncated. Biff "timmeah4" wrote in message ... I am creating a large data entry excel spreadsheet and I want to be able to insert, probably 100+ check boxes, my problem is that I dont want to have to associate every single checkbox with every single cell. is there a way that I can just click and drag (like fomulas do) and have the cells be populated by checkboxes. I do not want to have to right click each checkbox -- properties -- control, and then link the value to a cell. Please help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding multiple checkboxes
Thank you so much, this macro is awesome!
"T. Valko" wrote: Here's a macro by Dave Peterson: 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("a2:a10").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 Change the range (set to A2:A10) as needed. The macro automatically sets the linked cell to be the same cell the checkbox is "in". It also sets the linked cell font format to be "invisible". If you want custom captions for each checkbox you have to do that manually. If you want no caption "un REM" the line: '.Caption = "" 'or whatever you want To: ..Caption = "" 'or whatever you want Note: make sure the column is wide enough (about ~130 pixels) otherwise the default caption could get truncated. Biff "timmeah4" wrote in message ... I am creating a large data entry excel spreadsheet and I want to be able to insert, probably 100+ check boxes, my problem is that I dont want to have to associate every single checkbox with every single cell. is there a way that I can just click and drag (like fomulas do) and have the cells be populated by checkboxes. I do not want to have to right click each checkbox -- properties -- control, and then link the value to a cell. Please help! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding multiple checkboxes
I'll take the credit for realizing how useful it is and saving it and give
Dave due credit for writing it. Dave's the man! Biff "timmeah4" wrote in message ... Thank you so much, this macro is awesome! "T. Valko" wrote: Here's a macro by Dave Peterson: 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("a2:a10").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 Change the range (set to A2:A10) as needed. The macro automatically sets the linked cell to be the same cell the checkbox is "in". It also sets the linked cell font format to be "invisible". If you want custom captions for each checkbox you have to do that manually. If you want no caption "un REM" the line: '.Caption = "" 'or whatever you want To: ..Caption = "" 'or whatever you want Note: make sure the column is wide enough (about ~130 pixels) otherwise the default caption could get truncated. Biff "timmeah4" wrote in message ... I am creating a large data entry excel spreadsheet and I want to be able to insert, probably 100+ check boxes, my problem is that I dont want to have to associate every single checkbox with every single cell. is there a way that I can just click and drag (like fomulas do) and have the cells be populated by checkboxes. I do not want to have to right click each checkbox -- properties -- control, and then link the value to a cell. Please help! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding multiple checkboxes
It may be pretty, but I like to just put X's in cells <bg.
"T. Valko" wrote: I'll take the credit for realizing how useful it is and saving it and give Dave due credit for writing it. Dave's the man! Biff "timmeah4" wrote in message ... Thank you so much, this macro is awesome! "T. Valko" wrote: Here's a macro by Dave Peterson: 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("a2:a10").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 Change the range (set to A2:A10) as needed. The macro automatically sets the linked cell to be the same cell the checkbox is "in". It also sets the linked cell font format to be "invisible". If you want custom captions for each checkbox you have to do that manually. If you want no caption "un REM" the line: '.Caption = "" 'or whatever you want To: ..Caption = "" 'or whatever you want Note: make sure the column is wide enough (about ~130 pixels) otherwise the default caption could get truncated. Biff "timmeah4" wrote in message ... I am creating a large data entry excel spreadsheet and I want to be able to insert, probably 100+ check boxes, my problem is that I dont want to have to associate every single checkbox with every single cell. is there a way that I can just click and drag (like fomulas do) and have the cells be populated by checkboxes. I do not want to have to right click each checkbox -- properties -- control, and then link the value to a cell. Please help! -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding multiple checkboxes
Hi guys, I dont know if youll reply now that this has gotten to the 4th page.
But im noticing the check boxes start to creep up in their postition the more I add, i want to add 30 of these checkboxes to a particular column, and the 30th is on the 29th row, it makes it hard for the user to know exactly what they are clicking. Is there a way to clean this formatting up? As in I have 30checkboxes in 29 rows, rather than a check box in each row "Dave Peterson" wrote: It may be pretty, but I like to just put X's in cells <bg. "T. Valko" wrote: I'll take the credit for realizing how useful it is and saving it and give Dave due credit for writing it. Dave's the man! Biff "timmeah4" wrote in message ... Thank you so much, this macro is awesome! "T. Valko" wrote: Here's a macro by Dave Peterson: 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("a2:a10").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 Change the range (set to A2:A10) as needed. The macro automatically sets the linked cell to be the same cell the checkbox is "in". It also sets the linked cell font format to be "invisible". If you want custom captions for each checkbox you have to do that manually. If you want no caption "un REM" the line: '.Caption = "" 'or whatever you want To: ..Caption = "" 'or whatever you want Note: make sure the column is wide enough (about ~130 pixels) otherwise the default caption could get truncated. Biff "timmeah4" wrote in message ... I am creating a large data entry excel spreadsheet and I want to be able to insert, probably 100+ check boxes, my problem is that I dont want to have to associate every single checkbox with every single cell. is there a way that I can just click and drag (like fomulas do) and have the cells be populated by checkboxes. I do not want to have to right click each checkbox -- properties -- control, and then link the value to a cell. Please help! -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding multiple checkboxes
Hmmm.....
I've not experienced that. I tried it in various range sizes up to 200 rows and each ckbx is positioned exactly the same in each row. I'm using the default row height 12.75. Are all your rows the same height? Other than that I have no other thoughts. Biff "timmeah4" wrote in message ... Hi guys, I dont know if youll reply now that this has gotten to the 4th page. But im noticing the check boxes start to creep up in their postition the more I add, i want to add 30 of these checkboxes to a particular column, and the 30th is on the 29th row, it makes it hard for the user to know exactly what they are clicking. Is there a way to clean this formatting up? As in I have 30checkboxes in 29 rows, rather than a check box in each row "Dave Peterson" wrote: It may be pretty, but I like to just put X's in cells <bg. "T. Valko" wrote: I'll take the credit for realizing how useful it is and saving it and give Dave due credit for writing it. Dave's the man! Biff "timmeah4" wrote in message ... Thank you so much, this macro is awesome! "T. Valko" wrote: Here's a macro by Dave Peterson: 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("a2:a10").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 Change the range (set to A2:A10) as needed. The macro automatically sets the linked cell to be the same cell the checkbox is "in". It also sets the linked cell font format to be "invisible". If you want custom captions for each checkbox you have to do that manually. If you want no caption "un REM" the line: '.Caption = "" 'or whatever you want To: ..Caption = "" 'or whatever you want Note: make sure the column is wide enough (about ~130 pixels) otherwise the default caption could get truncated. Biff "timmeah4" wrote in message ... I am creating a large data entry excel spreadsheet and I want to be able to insert, probably 100+ check boxes, my problem is that I dont want to have to associate every single checkbox with every single cell. is there a way that I can just click and drag (like fomulas do) and have the cells be populated by checkboxes. I do not want to have to right click each checkbox -- properties -- control, and then link the value to a cell. Please help! -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding multiple checkboxes
Just to add to Biff's thoughts. I seem to have better luck when the zoom is set
to 100%. What is your zoom set to? timmeah4 wrote: Hi guys, I dont know if youll reply now that this has gotten to the 4th page. But im noticing the check boxes start to creep up in their postition the more I add, i want to add 30 of these checkboxes to a particular column, and the 30th is on the 29th row, it makes it hard for the user to know exactly what they are clicking. Is there a way to clean this formatting up? As in I have 30checkboxes in 29 rows, rather than a check box in each row "Dave Peterson" wrote: It may be pretty, but I like to just put X's in cells <bg. "T. Valko" wrote: I'll take the credit for realizing how useful it is and saving it and give Dave due credit for writing it. Dave's the man! Biff "timmeah4" wrote in message ... Thank you so much, this macro is awesome! "T. Valko" wrote: Here's a macro by Dave Peterson: 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("a2:a10").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 Change the range (set to A2:A10) as needed. The macro automatically sets the linked cell to be the same cell the checkbox is "in". It also sets the linked cell font format to be "invisible". If you want custom captions for each checkbox you have to do that manually. If you want no caption "un REM" the line: '.Caption = "" 'or whatever you want To: ..Caption = "" 'or whatever you want Note: make sure the column is wide enough (about ~130 pixels) otherwise the default caption could get truncated. Biff "timmeah4" wrote in message ... I am creating a large data entry excel spreadsheet and I want to be able to insert, probably 100+ check boxes, my problem is that I dont want to have to associate every single checkbox with every single cell. is there a way that I can just click and drag (like fomulas do) and have the cells be populated by checkboxes. I do not want to have to right click each checkbox -- properties -- control, and then link the value to a cell. Please help! -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding multiple worksheets | Excel Worksheet Functions | |||
Adding same cells across multiple worksheets | Excel Worksheet Functions | |||
adding duplicate text to multiple cells | Excel Discussion (Misc queries) | |||
Adding Data Using Multiple Worksheets to Total into a Grand Total | Excel Worksheet Functions | |||
Need Function for adding rows on multiple sheets... struggling rookie ;) Thanks. | Excel Worksheet Functions |