Home |
Search |
Today's Posts |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox limitations
First, I would never use 1000 checkboxes on a worksheet. But if I had to, I
wouldn't use the checkboxes from the Control Toolbox Toolbar. Those controls are very "intensive" in excel. I'd use the checkboxes from the Forms toolbar. In my experience, the controls from the Forms toolbar behave better and impact excel less. (But I still wouldn't use that many!) This added 2000 checkboxes with no problems: Option Explicit Sub LayOutCheckboxes() Dim myCBX As CheckBox Dim myCell As Range Dim wks As Worksheet Dim iCtr As Long Set wks = ActiveSheet Application.ScreenUpdating = False iCtr = 0 With wks .CheckBoxes.Delete 'nice for testing For Each myCell In .Range("b1:b2000").Cells With myCell .NumberFormat = ";;;" 'hide the true/false iCtr = iCtr + 1 If iCtr Mod 50 = 0 Then DoEvents Application.StatusBar _ = "Processing: " & myCell.Address(0, 0) End If 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) '.OnAction = "'" & ThisWorkbook.Name & "'!CbxClick" End With End With Next myCell End With With Application .StatusBar = False .ScreenUpdating = False End With End Sub I did notice a slowdown on my pc when I got to about 1500 checkboxes. Solution4U wrote: This appears to be a repeatable problem. Please advise if there is a fix for this, my email is Here are the 2 posts from the forum: "Solution4U" wrote: I have 5 columns with over 100 check boxes in each. I keep getting an error when the code runs unless I delete a certain portion of checkboxes. Right now all is running well with 1195 checkboxes but if I add more I get the error. Does anyone know why this is happening or how to get around it? I was only able to add 1098 check boxes using the code below with Excel 2003. I could not find any limits listed in the excel help that explains this problem. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 10/13/2008 by Joel ' ' LeftPos = Range("B1").Left For RowCount = 2 To 4000 Step 2 TopPos = Range("B" & RowCount).Top ActiveSheet.OLEObjects.Add _ ClassType:="Forms.CheckBox.1", _ Link:=False, _ DisplayAsIcon:=False, _ Left:=LeftPos, _ Top:=TopPos, _ Width:=108, _ Height:=19.5 Next RowCount End Sub ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...el.programming -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox limitations
I cannot use the checkbox from the forms toolbar because I need them to hide
along with the column they are in. I don't like using this many checkboxes either but this is for an extensive checklist and it is tough to have a checklist without checkboxes. :) Unless you know of a way to hide the forms toolboxes? "Dave Peterson" wrote: First, I would never use 1000 checkboxes on a worksheet. But if I had to, I wouldn't use the checkboxes from the Control Toolbox Toolbar. Those controls are very "intensive" in excel. I'd use the checkboxes from the Forms toolbar. In my experience, the controls from the Forms toolbar behave better and impact excel less. (But I still wouldn't use that many!) This added 2000 checkboxes with no problems: Option Explicit Sub LayOutCheckboxes() Dim myCBX As CheckBox Dim myCell As Range Dim wks As Worksheet Dim iCtr As Long Set wks = ActiveSheet Application.ScreenUpdating = False iCtr = 0 With wks .CheckBoxes.Delete 'nice for testing For Each myCell In .Range("b1:b2000").Cells With myCell .NumberFormat = ";;;" 'hide the true/false iCtr = iCtr + 1 If iCtr Mod 50 = 0 Then DoEvents Application.StatusBar _ = "Processing: " & myCell.Address(0, 0) End If 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) '.OnAction = "'" & ThisWorkbook.Name & "'!CbxClick" End With End With Next myCell End With With Application .StatusBar = False .ScreenUpdating = False End With End Sub I did notice a slowdown on my pc when I got to about 1500 checkboxes. Solution4U wrote: This appears to be a repeatable problem. Please advise if there is a fix for this, my email is Here are the 2 posts from the forum: "Solution4U" wrote: I have 5 columns with over 100 check boxes in each. I keep getting an error when the code runs unless I delete a certain portion of checkboxes. Right now all is running well with 1195 checkboxes but if I add more I get the error. Does anyone know why this is happening or how to get around it? I was only able to add 1098 check boxes using the code below with Excel 2003. I could not find any limits listed in the excel help that explains this problem. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 10/13/2008 by Joel ' ' LeftPos = Range("B1").Left For RowCount = 2 To 4000 Step 2 TopPos = Range("B" & RowCount).Top ActiveSheet.OLEObjects.Add _ ClassType:="Forms.CheckBox.1", _ Link:=False, _ DisplayAsIcon:=False, _ Left:=LeftPos, _ Top:=TopPos, _ Width:=108, _ Height:=19.5 Next RowCount End Sub ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...el.programming -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox limitations
It's a pain, but you could change the visibility to false. But then when you
show the row (and the checkbox), you'll probably have to reposition them. I wouldn't use either checkbox. I'd just use a cell and format it to show a check mark: (Saved from a previous post.) You may find it easier to use a cell and a Y or X or any character to indicate yes. You'll be able to sort, filter, and even count these easier than using checkboxes. An alternative that I like: Select the range that would have held the checkboxes. 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 (make it as large as you want) 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") Or you can filter by blanks and non-blanks. Solution4U wrote: I cannot use the checkbox from the forms toolbar because I need them to hide along with the column they are in. I don't like using this many checkboxes either but this is for an extensive checklist and it is tough to have a checklist without checkboxes. :) Unless you know of a way to hide the forms toolboxes? "Dave Peterson" wrote: First, I would never use 1000 checkboxes on a worksheet. But if I had to, I wouldn't use the checkboxes from the Control Toolbox Toolbar. Those controls are very "intensive" in excel. I'd use the checkboxes from the Forms toolbar. In my experience, the controls from the Forms toolbar behave better and impact excel less. (But I still wouldn't use that many!) This added 2000 checkboxes with no problems: Option Explicit Sub LayOutCheckboxes() Dim myCBX As CheckBox Dim myCell As Range Dim wks As Worksheet Dim iCtr As Long Set wks = ActiveSheet Application.ScreenUpdating = False iCtr = 0 With wks .CheckBoxes.Delete 'nice for testing For Each myCell In .Range("b1:b2000").Cells With myCell .NumberFormat = ";;;" 'hide the true/false iCtr = iCtr + 1 If iCtr Mod 50 = 0 Then DoEvents Application.StatusBar _ = "Processing: " & myCell.Address(0, 0) End If 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) '.OnAction = "'" & ThisWorkbook.Name & "'!CbxClick" End With End With Next myCell End With With Application .StatusBar = False .ScreenUpdating = False End With End Sub I did notice a slowdown on my pc when I got to about 1500 checkboxes. Solution4U wrote: This appears to be a repeatable problem. Please advise if there is a fix for this, my email is Here are the 2 posts from the forum: "Solution4U" wrote: I have 5 columns with over 100 check boxes in each. I keep getting an error when the code runs unless I delete a certain portion of checkboxes. Right now all is running well with 1195 checkboxes but if I add more I get the error. Does anyone know why this is happening or how to get around it? I was only able to add 1098 check boxes using the code below with Excel 2003. I could not find any limits listed in the excel help that explains this problem. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 10/13/2008 by Joel ' ' LeftPos = Range("B1").Left For RowCount = 2 To 4000 Step 2 TopPos = Range("B" & RowCount).Top ActiveSheet.OLEObjects.Add _ ClassType:="Forms.CheckBox.1", _ Link:=False, _ DisplayAsIcon:=False, _ Left:=LeftPos, _ Top:=TopPos, _ Width:=108, _ Height:=19.5 Next RowCount End Sub ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...el.programming -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to have Checkbox A uncheck with checked Checkbox B | Excel Discussion (Misc queries) | |||
link a checkbox in a sheet to a checkbox on a userform? | Excel Programming | |||
How do I link one checkbox to update another checkbox? | Excel Programming | |||
checkbox on form reset from checkbox on sheet | Excel Programming | |||
Limitations in UDF:s | Excel Programming |