BigBadWolf_000
asked on
Checkbox controls disappear on sort
Hi all....
Excel 2010
Checkbox control (ActiveX)
My Sheet has 6 checkbox controls per record. Works great until I do a sort via vba
a couple of the checkboxes disappear!!!
What am I missing....???
Excel 2010
Checkbox control (ActiveX)
My Sheet has 6 checkbox controls per record. Works great until I do a sort via vba
a couple of the checkboxes disappear!!!
What am I missing....???
Just right-click on all the controls and select Format control. On properties, uncheck 'move and size with cells'
ASKER
Thanks.....I thought I would need that checked...however makes sense specialy since the checkbox is overlayed on the cell....I will try it and get back.
ASKER
Hi TommySzalapski:
Under Format Control > Properties > Following are the radio button options (have to choose one)
Move and size with cells
Move but don't size with cells
Don't move or size with cells
Default was #2 - but some checkboxes disappear with every sort
Tried #1 and #3 - some checkboxes disappear with every sort and also some vanish when you click on the checkbox
Any ideas?
Under Format Control > Properties > Following are the radio button options (have to choose one)
Move and size with cells
Move but don't size with cells
Don't move or size with cells
Default was #2 - but some checkboxes disappear with every sort
Tried #1 and #3 - some checkboxes disappear with every sort and also some vanish when you click on the checkbox
Any ideas?
Vanish when you click the checkbox? Do you have macros running? Could you post the file? (Remove any private info of course).
ASKER
Yes macros are running on checking the box and unchecking the box (sheet is also protected)
Below is the sort macro that causes the issue
If that does not help...I will scrub and post file...
Below is the sort macro that causes the issue
If that does not help...I will scrub and post file...
Sub Macro3()
'
' Macro3 Sort Acsending
'
' Keyboard Shortcut: Ctrl+a
'
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, Password:="MyPwd", userinterfaceonly:=True
ActiveSheet.Sort.SortFields.Clear
sort1 = InputBox("Enter Letter of Column to Sort Ascending")
ActiveSheet.Sort.SortFields.Add Key:=Range(sort1 & "3:" & sort1 & "27") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A1:AP27")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Protect ("MyPwd")
Range("A1").Select
End Sub
Sub Macro4()
'
' Macro4 Sort Descending
'
' Keyboard Shortcut: Ctrl+d
'
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, Password:="MyPwd", userinterfaceonly:=True
ActiveSheet.Sort.SortFields.Clear
sort1 = InputBox("Enter Letter of Column to Sort Descending")
ActiveSheet.Sort.SortFields.Add Key:=Range(sort1 & "3:" & sort1 & "27") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A1:AP27")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Protect ("MyPwd")
Range("A1").Select
End Sub
ASKER
Either the acending or desinding macro causes the issue
My guess is they are not 'vanishing' they are just moving on top/under another checkbox. Setting 'do not move or size with cells' should keep them from moving (you'd need to set it on every one, even the ones that are hiding under other ones). It is possible to do this to all of them it a macro.
However, my guess is you actually want them to stay with their correct rows when they are sorted so you probably want them to 'move but don't size with cells'
Now here's the funny issue, if the cell is too small or the checkbox too large that the checkbox isn't entirely within the cell, it won't move at all. What's really odd is that 'undo' does not fix it. They stay moved, but the 'vanished' ones are still there.
So if some of your checkboxes are completely in cells and some aren't, you will see the behavior you describe.
I posted a file showing failing and working examples in two sheets
check.xls
However, my guess is you actually want them to stay with their correct rows when they are sorted so you probably want them to 'move but don't size with cells'
Now here's the funny issue, if the cell is too small or the checkbox too large that the checkbox isn't entirely within the cell, it won't move at all. What's really odd is that 'undo' does not fix it. They stay moved, but the 'vanished' ones are still there.
So if some of your checkboxes are completely in cells and some aren't, you will see the behavior you describe.
I posted a file showing failing and working examples in two sheets
check.xls
If that's not the issue, I'll need to see a sample file showing your error.
ASKER
Thanks,
Unfortunately that was not the issue.
Attached file
Protection pwd is = MyPwd
Happens only on a sort with ctrl+d or ctrl+a
Please test with pwd enabled.
See Help sheet for macro hotkeys CI-v1.0.xlsm
Unfortunately that was not the issue.
Attached file
Protection pwd is = MyPwd
Happens only on a sort with ctrl+d or ctrl+a
Please test with pwd enabled.
See Help sheet for macro hotkeys CI-v1.0.xlsm
ASKER
I noticed an issue....the checkboxes cannot be moved on sort cause they referance an adjacent cell in the onclick macro
ASKER
if "Don't move or size with cells" then I loose the checkboxes on the Ctrh+H hide macro
Also it still moves the checkboxes on the sort macro
I guess may have to consider another sort methadology if even possible :O
Also it still moves the checkboxes on the sort macro
I guess may have to consider another sort methadology if even possible :O
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi TommySzalapski:
using just ActiveSheet.Unprotect "MyPwd" worked, however I do no want the checkboxes to move on the sort ...so when I change the properties to "Don't move or size with cells"
the checkboxes disappear or you have to double click on the checkbox to get the check mark(click on each checkbox with dates to see the issue)
Attached updated file. Alternative - keep -Move but don't size with cells
but remove the columns that contain the checkboxes from sort---if even possible
Any thoughts? CI-v2.0.xlsm
using just ActiveSheet.Unprotect "MyPwd" worked, however I do no want the checkboxes to move on the sort ...so when I change the properties to "Don't move or size with cells"
the checkboxes disappear or you have to double click on the checkbox to get the check mark(click on each checkbox with dates to see the issue)
Attached updated file. Alternative - keep -Move but don't size with cells
but remove the columns that contain the checkboxes from sort---if even possible
Any thoughts? CI-v2.0.xlsm
But don't the checkboxes also relate to the cells in the row? So shouldn't they follow them? You can reset the linked cell property in the code when you sort so the boxes will move but they will still update the cells beneath them.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Those need to be Right(shp.Name, 1)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
So you could update all the Click events with code like the following.
By the way, you can add the click events to all the checkboxes at the same time with the same macro. See this solution to this question
https://www.experts-exchange.com/questions/26626128/VBA-map-multiple-buttons-to-the-same-click-event.html
(Of course change all the references to labels to checkboxes)
By the way, you can add the click events to all the checkboxes at the same time with the same macro. See this solution to this question
https://www.experts-exchange.com/questions/26626128/VBA-map-multiple-buttons-to-the-same-click-event.html
(Of course change all the references to labels to checkboxes)
Private Sub chk_Click()
Dim r As Range
Sheet1.Unprotect ("MyPwd")
Set r = CheckBox1a.TopLeftCell.Offset(0, -1)
If chk.Value = True Then
r.ClearFormats
r.NumberFormat = "M/D/YYYY"
With r.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With r.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With r.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
End With
Sheet1.Protect ("MyPwd")
Else
r.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=TODAY()", Formula2:="=TODAY()+15"
r.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With r.FormatConditions(1).Font
.Color = -16777024
.TintAndShade = 0
End With
With r.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.799981688894314
End With
With r.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With r.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With r.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
End With
r.FormatConditions(1).StopIfTrue = False
Sheet1.Protect ("MyPwd")
End If
End Sub
ASKER
Thanks - I will try it out tomorrow
ASKER
Thnak you for all your help!