Deselecting - VBA

[C Brandt]

In a vba routine I have selected a "button" to change the color of the Font
as an indication of whether the button is in one mode or another. When the
routine is complete the button remains selected and therefore whenever I
depress it, it thinks I am in edit mode and doesn't function as a button.

How do I "deselect" the button within VBA prior to leaving the routine
without selecting something else in the sheet.

Thanks for any assistance,

Craig

 

[Jim Thomlinson]

Post your code...

 

[C Brandt]

Public Sub ARM()
'
'Entry into this routine is by button depression. Each sheet has a button
controling the single cell
'
'
If Sheets("Param").Range("S30") = "Off" Then
Sheets("Param").Range("S30") = "On"
ActiveSheet.Shapes("Button 1").Select
With Selection.Characters(Start:=1, Length:=8).Font
.ColorIndex = 3 ' Red Font indicates that the "Cell
Selection Change" is arm'ed
End With
Else
Sheets("Param").Range("S30") = "Off"
ActiveSheet.Shapes("Button 1").Select
With Selection.Characters(Start:=1, Length:=8).Font
.ColorIndex = 21 ' Green indicates that "Cell Selection Change"
is off.
End With
End If
End Sub

 

[Dave Peterson]

Something has to be selected.

You could keep track of what's selected, then select that again--or even assume
that it was a cell and use:

Activecell.activate

But even better is to not select the button in the first place--just adjust the
font color directly:

Option Explicit
Public Sub ARM()

If lcase(Sheets("Param").Range("S30").Value) = lcase("Off") Then
Sheets("Param").Range("S30").Value = "On"
ActiveSheet.Buttons("Button 1") _
.Characters(Start:=1, Length:=8).Font.ColorIndex = 3
Else
Sheets("Param").Range("S30").Value = "Off"
ActiveSheet.Buttons("Button 1") _
.Characters(Start:=1, Length:=8).Font.ColorIndex = 21
End If
End Sub

 

[C Brandt]

Dave:

I like your last approach. Does the job with excellent results. I assumed
that you needed to select it in order to modify it.

Thanks,
Craig

 

[Dave Peterson]

It's pretty unusual to have to select something to work on it. But the bad news
is that the macro recorder records the actions taken--including .select's. And
determining how to get rid of them can be a pain.