Toggle change control button face id & caption

The following code toggles a command button turning on & off an input range. Need VBA code to that would change the face id & caption of the command button when the input range goes on & toggle back when the input ranges goes off. Any ideas?

Dim INPUTRANGE
Sub Auto_Open()
'BEGIN INPUTMACROTOGGLE.XLS
'To add a command button
Dim CB As CommandBar
Dim CBB1 As CommandBarButton
Set CB = Application.CommandBars(1)
Set CBB1 = CB.Controls.Add(Type:=msoControlButton, _
before:=CB.Controls.Count, ID:=59, temporary:=True)
With CBB1
.Caption = "Turn on input range"
.FaceId = 352
.Style = msoButtonIconAndCaption
.OnAction = "CursorMovementLimitActivateDeactivate"
End With
End Sub
Sub CursorMovementLimitActivateDeactivate()
'To Activate a limited area of cursor movement & wrap after last cell
If ActiveSheet.ScrollArea = "" Then
INPUTRANGE = InputBox("Enter input cell range with colon (ex-A125:D138): ")
If INPUTRANGE = False Then Goto Done
If INPUTRANGE = "" Then Goto Done
Range(INPUTRANGE).Select
'To turn on underline & vertical lines of cells in input range
With Range(INPUTRANGE)
Selection.Borders(xlInsideVertical).LineStyle = xlContinuous
Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous
End With
'To turn off selection
ActiveCell.Offset(0, 0).Select
'To turn on cursor movement & direction to the right on enter
Application.MoveAfterReturn = True
Application.MoveAfterReturnDirection = xlToRight
'To turn on cursor movement range limits
ActiveSheet.ScrollArea = INPUTRANGE
Else
'To Deactivate a limited of cursor movement
INPUTRANGE = ActiveSheet.ScrollArea
'To turn off underline & vertical lines of cells in input range
Range(INPUTRANGE).Select
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'To turn off selection
ActiveCell.Offset(0, 0).Select
'To turn off cursor movement range limits
ActiveSheet.ScrollArea = ""
Done:
End If
End Sub
Private Sub Auto_Close()
On Error Resume Next
With Application.CommandBars(1)
.Controls("Input range on/off").Delete
End With
'END INPUTMACROTOGGLE.XLS
End Sub

You have code setting the face id and caption. Use the same code to change
it in your toggle routine. You can use a With statement similar to your
button delete portion.

--
Damon Longworth

Don't miss out on the 2005 Excel User Conference
www.ExcelUserConference.com


"mikeburg" <[email protected]> wrote in
message news:[email protected]...
>
> The following code toggles a command button turning on & off an input
> range. Need VBA code to that would change the face id & caption of the
> command button when the input range goes on & toggle back when the
> input ranges goes off. Any ideas?
>
> Dim INPUTRANGE
> Sub Auto_Open()
> 'BEGIN INPUTMACROTOGGLE.XLS
> 'To add a command button
> Dim CB As CommandBar
> Dim CBB1 As CommandBarButton
> Set CB = Application.CommandBars(1)
> Set CBB1 = CB.Controls.Add(Type:=msoControlButton, _
> before:=CB.Controls.Count, ID:=59, temporary:=True)
> With CBB1
> Caption = "Turn on input range"
> FaceId = 352
> Style = msoButtonIconAndCaption
> OnAction = "CursorMovementLimitActivateDeactivate"
> End With
> End Sub
> Sub CursorMovementLimitActivateDeactivate()
> 'To Activate a limited area of cursor movement & wrap after last
> cell
> If ActiveSheet.ScrollArea = "" Then
> INPUTRANGE = InputBox("Enter input cell range with colon
> (ex-A125:D138): ")
> If INPUTRANGE = False Then Goto Done
> If INPUTRANGE = "" Then Goto Done
> Range(INPUTRANGE).Select
> 'To turn on underline & vertical lines of cells in input
> range
> With Range(INPUTRANGE)
> Selection.Borders(xlInsideVertical).LineStyle =
> xlContinuous
> Selection.Borders(xlInsideHorizontal).LineStyle =
> xlContinuous
> End With
> 'To turn off selection
> ActiveCell.Offset(0, 0).Select
> 'To turn on cursor movement & direction to the right on enter
> Application.MoveAfterReturn = True
> Application.MoveAfterReturnDirection = xlToRight
> 'To turn on cursor movement range limits
> ActiveSheet.ScrollArea = INPUTRANGE
> Else
> 'To Deactivate a limited of cursor movement
> INPUTRANGE = ActiveSheet.ScrollArea
> 'To turn off underline & vertical lines of cells in input
> range
> Range(INPUTRANGE).Select
> Selection.Borders(xlInsideVertical).LineStyle = xlNone
> Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
> 'To turn off selection
> ActiveCell.Offset(0, 0).Select
> 'To turn off cursor movement range limits
> ActiveSheet.ScrollArea = ""
> Done:
> End If
> End Sub
> Private Sub Auto_Close()
> On Error Resume Next
> With Application.CommandBars(1)
> Controls("Input range on/off").Delete
> End With
> 'END INPUTMACROTOGGLE.XLS
> End Sub
>
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile:
> http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=382153
>