how to 'deselect' textframes and shapes? (on a worksheet...)

[kbishop94]

picture to illustrate what is happening after command button code is ran:


the code: (after which point it leaves the two text frames "selected")

Private Sub cmdColumnO_Click()
'
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
'
If ActiveSheet.Shapes("lblColumnO1").TextFrame.Characters.Text = "UNHIDE" Then
        cmdColumnO.Visible = False
        cmdColumnO.Visible = True
'
        ActiveSheet.Columns("M:O").ColumnWidth = 0.83
        ActiveSheet.Shapes("lblColumnO1").TextFrame.Characters.Text = "HIDE"
        ActiveSheet.Shapes.Range(Array("lblColumnO2", "lblColumnO1")).Select
                With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill
                    .ForeColor.RGB = RGB(0, 0, 255)
                    .Transparency = 0
                    .Solid
                End With
'
        Application.GoTo Reference:=Range("b1"), Scroll:=True
'
Else
'
        ActiveSheet.Columns("M:O").EntireColumn.Hidden = True
        ActiveSheet.Shapes("lblColumnO1").TextFrame.Characters.Text = "UNHIDE"
        ActiveSheet.Shapes.Range(Array("lblColumnO2", "lblColumnO1")).Select
            With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill
                .ForeColor.RGB = RGB(127, 127, 127)
                .Transparency = 0
                .Solid
            End With
'
        cmdColumnO.Visible = False
        cmdColumnO.Visible = True
'
End If
'
ActiveWindow.Zoom = 115
'
Application.GoTo Reference:=Range("b1"), Scroll:=True
'
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
'
End Sub

Thank you.

 

[RoryA]

Don't select them in the first place...

 

[kbishop94]

Don't select them in the first place...

lol, excellent advice. I shall take it under advisement

but here's what makes me think that it might be something simple:

My command button acts as a toggle for hiding/unhiding columns M-O. When the action is to UNHIDE the columns, (the 'If' part of the code) the two text frames aren't selected when the code ends. Its only when they go to being hidden (the 'Else' part of the code) that they stay selected. The code for selecting and formatting the text frames are the same for both actions (except for the font color difference), but yet they act differently in this aspect (one stays selected and the other doesnt.)

Here is the line for what correctly 'un-selects' the two shapes/text frames in the 'If' section... (if I take this out, then the shapes/frames will stay selected like the other half of the code does...)

Application.GoTo Reference:=Range("b1"), Scroll:=True

Ok, so simple solution, I'll just use that same code in the other half of the 'If/Else' procedure to 'un-select' it when that half runs... except it doesn't in this case. (no matter where i seem to put in the line including mutiple times/places, the two shapes/frames remain selected... )

Here is what it looks like when the code 'un-hides' the three columns (this is the first part of the procedure- the 'If' section before the 'Else' code. Its the 'Else' side that I cannot seem to get it to 'un-select' the shapes at the end. )

 

[RoryA]

Simpler solution: don’t select them in the first place…

 

[kbishop94]

Thanks, but I kept on trying different things until I finally found a solution...

If I put this (select a cell and clear the clipboard) immediately after the 'Else' command, it works: (but just selecting a cell doesn't... as that was the first thing I tried early on.)

  Range("A1").Select
  Application.CutCopyMode = False

Also, if I don't select the two frames in the first place then how would I go about applying the formatting changes that I need that are in the code?

 

[RoryA]

Change:

ActiveSheet.Shapes.Range(Array("lblColumnO2", "lblColumnO1")).Select
                With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill

to:

ActiveSheet.Shapes.Range(Array("lblColumnO2", "lblColumnO1")).TextFrame2.TextRange.Font.Fill

 

[kbishop94]

Change:

ActiveSheet.Shapes.Range(Array("lblColumnO2", "lblColumnO1")).Select
                With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill

to:

ActiveSheet.Shapes.Range(Array("lblColumnO2", "lblColumnO1")).TextFrame2.TextRange.Font.Fill

that indicates my "ahh ha!" moment...

Thank you, RoryA. Much cleaner that way.

 

[RoryA]

Glad we could help.