Solved

Checkbox controls disappear on sort

Posted on 2010-11-22
20
1,539 Views
Last Modified: 2012-05-10
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....???
0
Comment
Question by:BigBadWolf_000
  • 10
  • 10
20 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34192232
Just right-click on all the controls and select Format control. On properties, uncheck 'move and size with cells'
0
 
LVL 14

Author Comment

by:BigBadWolf_000
ID: 34193645
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.
0
 
LVL 14

Author Comment

by:BigBadWolf_000
ID: 34193736
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?
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34193757
Vanish when you click the checkbox? Do you have macros running? Could you post the file? (Remove any private info of course).
0
 
LVL 14

Author Comment

by:BigBadWolf_000
ID: 34193779
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...


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

Open in new window

0
 
LVL 14

Author Comment

by:BigBadWolf_000
ID: 34193785
Either the acending or desinding macro causes the issue
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34194295
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
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34194300
If that's not the issue, I'll need to see a sample file showing your error.
0
 
LVL 14

Author Comment

by:BigBadWolf_000
ID: 34197062
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
0
 
LVL 14

Author Comment

by:BigBadWolf_000
ID: 34197790
I noticed an issue....the checkboxes cannot be moved on sort cause they referance an adjacent cell in the onclick macro
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 14

Author Comment

by:BigBadWolf_000
ID: 34197901
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
0
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 total points
ID: 34198523
I'm not seeing any disappear, but here is how I got it working.
When the sheet is protected, the controls don't move right, even if 'allow sort' is set. So at the top of each of the two sort macros just put
ActiveSheet.Unprotect "MyPwd"

If you're worried about the macro crashing before protection gets turned back on just set On Error GoTo crash and add a label called crash right before the protection turns back on.
You could even display the error too.
Sub Macro3()
'
' Macro3 Sort Acsending
'
' Keyboard Shortcut: Ctrl+a
'
    On Error Goto crash
    ActiveSheet.Unprotect "MyPwd"

    ActiveSheet.Sort.SortFields.Clear
    sort1 = InputBox("Enter Letter of Column to Sort Ascending")
    'sort2 = InputBox("enter second sort column")
    
    ActiveSheet.Sort.SortFields.Add Key:=Range(sort1 & "3:" & sort1 & "27") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    'ActiveSheet.Sort.SortFields.Add Key:=Range(sort2 & "3:" & sort2 & "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
crash:
    ActiveSheet.Protect ("MyPwd")
    If Err.Number <> 0
        MsgBox Err.Description
    End If
    Range("A1").Select
End Sub

Open in new window

0
 
LVL 14

Author Comment

by:BigBadWolf_000
ID: 34206684
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
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34206790
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.
0
 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 500 total points
ID: 34206866
I also recommend you clean up the code some. Like change Macro1 and Macro2 to something like the following (Using ActiveSheet usually isn't good because if the user clicks on a different sheet while the code is running, it does bad things)
Sub Macro1()

'

' Macro1 Hide

'

' Keyboard Shortcut: Ctrl+h

'

    Dim shp As Shape

    

    Sheet1.Unprotect ("MyPwd")

    Sheet1.Range("G:G,I:N,T:T,W:AD,AI:AI,AK:AP").EntireColumn.Hidden = True

    

    For Each shp In Sheet1.Shapes

      If Left(shp.Name, 8) = "CheckBox" And Right(shp.Name) <> "a" And Right(shp.Name) <> "f" Then

        shp.Visible = False

      End If

    Next



    Sheet1.Protect ("MyPwd")

End Sub

Sub Macro2()

'

' Macro2 Unhide

'

' Keyboard Shortcut: Ctrl+u

'

    Dim shp As Shape

    

    Sheet1.Unprotect ("MyPwd")

    Sheet1.EntireColumn.Hidden = False

    

    'Sheet1.Range("A1:A2").Select Do you need this?

    For Each shp In Sheet1.Shapes

      If Left(shp.Name, 8) = "CheckBox" And Right(shp.Name) <> "a" And Right(shp.Name) <> "f" Then

        shp.Visible = False

      End If

    Next

    

    Sheet1.Protect ("MyPwd")

End Sub

Open in new window

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34206872
Those need to be Right(shp.Name, 1)
0
 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 500 total points
ID: 34206906
Also, the checkboxes know where they are. Instead of using Range("U3") for the box in V3 use
Checkbox1a.TopLeftCell.Offset(0,-1) to select the cell one to the left of where the checkbox is.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34206934
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
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Word/Q_26626128.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

Open in new window

0
 
LVL 14

Author Comment

by:BigBadWolf_000
ID: 34222812
Thanks - I will try it out tomorrow
0
 
LVL 14

Author Closing Comment

by:BigBadWolf_000
ID: 34387117
Thnak you for all your help!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now