Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for checkbox generation
Hi all, I'm not the most experienced of Excel users so please bear with me. I' looking to generate a macro (because I'm lazy) that will place about 50 checkboxes in a single column, row by row. It also needs to move an resize according to the data in an adjacent cell (i.e. if the text nex to it is too large and wordwraps another line, the box must move wit it). One checkbox per row is what I'm getting at. This is the code I'm currently using: Dim myCBX As CheckBox Dim myCell As Range With ActiveSheet .CheckBoxes.Delete 'nice for setting up For Each myCell In ActiveSheet.Range("O13:O503").Cells With myCell Set myCBX = .Parent.CheckBoxes.Add _ (Top:=.Top, Width:=.Width, _ Left:=.Left, Height:=.Height) With myCBX .LinkedCell = myCell.Address(external:=True) .Caption = "" .Name = "CBX_" & myCell.Address(0, 0) End With .NumberFormat = ";;;" End With Next myCell End With End Sub Please and thanks -- rmcveig ----------------------------------------------------------------------- rmcveigh's Profile: http://www.excelforum.com/member.php...fo&userid=3735 View this thread: http://www.excelforum.com/showthread.php?threadid=57145 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for checkbox generation
with myCBX
.Placement = xlMoveAndSize .LinkedCell = myCell.Address(external:=True) .Caption = "" .Name = "CBX_" & myCell.Address(0, 0) End With -- Regards, Tom Ogilvy "rmcveigh" wrote: Hi all, I'm not the most experienced of Excel users so please bear with me. I'm looking to generate a macro (because I'm lazy) that will place about 500 checkboxes in a single column, row by row. It also needs to move and resize according to the data in an adjacent cell (i.e. if the text next to it is too large and wordwraps another line, the box must move with it). One checkbox per row is what I'm getting at. This is the code I'm currently using: Dim myCBX As CheckBox Dim myCell As Range With ActiveSheet .CheckBoxes.Delete 'nice for setting up For Each myCell In ActiveSheet.Range("O13:O503").Cells With myCell Set myCBX = .Parent.CheckBoxes.Add _ (Top:=.Top, Width:=.Width, _ Left:=.Left, Height:=.Height) With myCBX .LinkedCell = myCell.Address(external:=True) .Caption = "" .Name = "CBX_" & myCell.Address(0, 0) End With .NumberFormat = ";;;" End With Next myCell End With End Sub Please and thanks. -- rmcveigh ------------------------------------------------------------------------ rmcveigh's Profile: http://www.excelforum.com/member.php...o&userid=37353 View this thread: http://www.excelforum.com/showthread...hreadid=571452 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for checkbox generation
Thanks Tom for the response. It doesn't seem to be working (is this because we used checkboxes from the form toolbox?). It won't even allow you to manually select the 'moveandsize' option for the boxes. So we tried this, hoping to switch over to the controls checkbox (am I making sense?): Option Explicit Sub RunOnce() Dim myCBX As CheckBox Dim myCell As Range With ActiveSheet ..CheckBoxes.Delete 'nice for setting up For Each myCell In ActiveSheet.Range("P13:P503").Cells With myCell Set myCBX = .Parent.OLEObjects.Add(ClassType:="Forms.CheckBox. 1", Link:=False, _ DisplayAsIcon:=False, Left:=.Left, Top:=.Top, _ Width:=.Width, Height:=.Height).Select With myCBX ..Placement = xlMoveAndSize ..LinkedCell = myCell.Address(external:=True) ..Caption = "" ..Name = "CBX_" & myCell.Address(0, 0) End With ..NumberFormat = ";;;" End With Next myCell End With End Sub -- rmcveigh ------------------------------------------------------------------------ rmcveigh's Profile: http://www.excelforum.com/member.php...o&userid=37353 View this thread: http://www.excelforum.com/showthread...hreadid=571452 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for checkbox generation
How about:
Option Explicit Sub RunOnce() Dim myCBX As OLEObject Dim myCell As Range With ActiveSheet For Each myCBX In .OLEObjects If TypeOf myCBX.Object Is MSForms.CheckBox Then myCBX.Delete End If Next myCBX For Each myCell In .Range("P13:P33").Cells With myCell Set myCBX = .Parent.OLEObjects.Add _ (ClassType:="Forms.CheckBox.1", _ Link:=False, DisplayAsIcon:=False, _ Left:=.Left, Top:=.Top, _ Width:=.Width, Height:=.Height) With myCBX .Placement = xlMoveAndSize .LinkedCell = myCell.Address(external:=True) .Object.Caption = "" .Name = "CBX_" & myCell.Address(0, 0) End With .NumberFormat = ";;;" End With Next myCell End With End Sub Notice I changed the range for testing. And I've seen excel behave not so nice when there are lots of controls from the Control toolbox toolbar on a worksheet--you may want to keep an eye open, too. rmcveigh wrote: Thanks Tom for the response. It doesn't seem to be working (is this because we used checkboxes from the form toolbox?). It won't even allow you to manually select the 'moveandsize' option for the boxes. So we tried this, hoping to switch over to the controls checkbox (am I making sense?): Option Explicit Sub RunOnce() Dim myCBX As CheckBox Dim myCell As Range With ActiveSheet CheckBoxes.Delete 'nice for setting up For Each myCell In ActiveSheet.Range("P13:P503").Cells With myCell Set myCBX = .Parent.OLEObjects.Add(ClassType:="Forms.CheckBox. 1", Link:=False, _ DisplayAsIcon:=False, Left:=.Left, Top:=.Top, _ Width:=.Width, Height:=.Height).Select With myCBX Placement = xlMoveAndSize LinkedCell = myCell.Address(external:=True) Caption = "" Name = "CBX_" & myCell.Address(0, 0) End With NumberFormat = ";;;" End With Next myCell End With End Sub -- rmcveigh ------------------------------------------------------------------------ rmcveigh's Profile: http://www.excelforum.com/member.php...o&userid=37353 View this thread: http://www.excelforum.com/showthread...hreadid=571452 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for checkbox generation
Dim myCBX As Object
Dim myCell As Range With ActiveSheet 'CheckBoxes.Delete 'nice for setting up For Each myCell In ActiveSheet.Range("P13:P50") With myCell Set myCBX = ..Parent.OLEObjects.Add(ClassType:="Forms.CheckBox .1", _ Link:=False, _ DisplayAsIcon:=False, _ Left:=.Left, _ Top:=.Top, _ Width:=.Width, _ Height:=.Height) End With With ActiveSheet.OLEObjects("CheckBox1") 'myCBX .Placement = xlMoveAndSize .LinkedCell = myCell.Address(external:=True) .Object.Caption = "" .Name = "CBX_" & myCell.Address(0, 0) End With myCell.NumberFormat = ";;;" Next myCell End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "rmcveigh" wrote in message ... Thanks Tom for the response. It doesn't seem to be working (is this because we used checkboxes from the form toolbox?). It won't even allow you to manually select the 'moveandsize' option for the boxes. So we tried this, hoping to switch over to the controls checkbox (am I making sense?): Option Explicit Sub RunOnce() Dim myCBX As CheckBox Dim myCell As Range With ActiveSheet CheckBoxes.Delete 'nice for setting up For Each myCell In ActiveSheet.Range("P13:P503").Cells With myCell Set myCBX = .Parent.OLEObjects.Add(ClassType:="Forms.CheckBox. 1", Link:=False, _ DisplayAsIcon:=False, Left:=.Left, Top:=.Top, _ Width:=.Width, Height:=.Height).Select With myCBX Placement = xlMoveAndSize LinkedCell = myCell.Address(external:=True) Caption = "" Name = "CBX_" & myCell.Address(0, 0) End With NumberFormat = ";;;" End With Next myCell End With End Sub -- rmcveigh ------------------------------------------------------------------------ rmcveigh's Profile: http://www.excelforum.com/member.php...o&userid=37353 View this thread: http://www.excelforum.com/showthread...hreadid=571452 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for checkbox generation
Sorry, I guess I read Textbox.
Dave showed you a way to replace them, but I would try it manually first. I am not sure you will be happy with the resulting checkbox when you use control toolbox toolbar checkboxes after you increase the row height. Also try checking them after you increase the row height. I don't think I would want that. -- Regards, Tom Ogilvy "rmcveigh" wrote: Thanks Tom for the response. It doesn't seem to be working (is this because we used checkboxes from the form toolbox?). It won't even allow you to manually select the 'moveandsize' option for the boxes. So we tried this, hoping to switch over to the controls checkbox (am I making sense?): Option Explicit Sub RunOnce() Dim myCBX As CheckBox Dim myCell As Range With ActiveSheet .CheckBoxes.Delete 'nice for setting up For Each myCell In ActiveSheet.Range("P13:P503").Cells With myCell Set myCBX = .Parent.OLEObjects.Add(ClassType:="Forms.CheckBox. 1", Link:=False, _ DisplayAsIcon:=False, Left:=.Left, Top:=.Top, _ Width:=.Width, Height:=.Height).Select With myCBX .Placement = xlMoveAndSize .LinkedCell = myCell.Address(external:=True) .Caption = "" .Name = "CBX_" & myCell.Address(0, 0) End With .NumberFormat = ";;;" End With Next myCell End With End Sub -- rmcveigh ------------------------------------------------------------------------ rmcveigh's Profile: http://www.excelforum.com/member.php...o&userid=37353 View this thread: http://www.excelforum.com/showthread...hreadid=571452 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for checkbox generation
Hi guys, Thanks for all your help- the macro definitely places a sequence of checkboxes but now, just to be picky, we're wondering how to a) enlarge the checkboxes; b) make the boxes flat; and c) uncheck the boxes. A pain, I know. :) -- rmcveigh ------------------------------------------------------------------------ rmcveigh's Profile: http://www.excelforum.com/member.php...o&userid=37353 View this thread: http://www.excelforum.com/showthread...hreadid=571452 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for checkbox generation
To Uncheck:
for each obj in Activesheet.OleObjects if typeof obj.object is MSforms.Checkbox then obj.Object.Value = false end if Next their size is related to the row height. so enlarging and flattening should be done with the row height. -- Regards, Tom Ogilvy "rmcveigh" wrote: Hi guys, Thanks for all your help- the macro definitely places a sequence of checkboxes but now, just to be picky, we're wondering how to a) enlarge the checkboxes; b) make the boxes flat; and c) uncheck the boxes. A pain, I know. :) -- rmcveigh ------------------------------------------------------------------------ rmcveigh's Profile: http://www.excelforum.com/member.php...o&userid=37353 View this thread: http://www.excelforum.com/showthread...hreadid=571452 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for checkbox generation
Thanks for the help. We figured out how to flatten and uncheck the boxes- but the problem still remains that when the adjacent cell is word-wrapped (and overflowing onto another line) the checkbox warps and duplicates. Any suggestions? Please and thanks. -- rmcveigh ------------------------------------------------------------------------ rmcveigh's Profile: http://www.excelforum.com/member.php...o&userid=37353 View this thread: http://www.excelforum.com/showthread...hreadid=571452 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for checkbox generation
if a cell is word-wrapped, it doesn't overflow onto another line if you
autofit the cell and if you don't, the text is hidden. warps and duplicates - I already told you making the row height higher isn't pretty. Not sure what you mean beyond that or flatten either. -- Regards, Tom Ogilvy "rmcveigh" wrote: Thanks for the help. We figured out how to flatten and uncheck the boxes- but the problem still remains that when the adjacent cell is word-wrapped (and overflowing onto another line) the checkbox warps and duplicates. Any suggestions? Please and thanks. -- rmcveigh ------------------------------------------------------------------------ rmcveigh's Profile: http://www.excelforum.com/member.php...o&userid=37353 View this thread: http://www.excelforum.com/showthread...hreadid=571452 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checkbox = macro? | Excel Discussion (Misc queries) | |||
Checkbox Macro | Excel Discussion (Misc queries) | |||
Macro for checkbox (2) | Excel Discussion (Misc queries) | |||
macro with checkbox(i) | Excel Programming | |||
VBA Macro - correlations in random number generation | Excel Programming |