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" <mikeburg.1r5qqa_1119661502.6813...excelforum-nospam.com> wrote in
message news:mikeburg.1r5qqa_1119661502.6813...excelforum-nospam.com...
> 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?action=getinfo&userid=24581> View this thread:
http://www.excelforum.com/showthread.php?threadid=382153>