Toggle change control button face id & caption

M

mikeburg

The following code toggles a command button turning on & off an inpu
range. Need VBA code to that would change the face id & caption of th
command button when the input range goes on & toggle back when th
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 las
cell
If ActiveSheet.ScrollArea = "" Then
INPUTRANGE = InputBox("Enter input cell range with colo
(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 inpu
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 inpu
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 Su
 
D

Damon Longworth

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top