Hi everybody,
I've found an expired thread about changing control button facesid and caption by mikeburg which offered some help. It worked "great with some modifications"; but which are they?
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
Untested but try adding the code below to the "CursorMovementLimitActivateDeactivate" macro
Dim CBB1 As CommandBarButton
Set CBB1 = Application. CommandBars.ActionControl
With CBB1
If .Caption = "Turn on input range" Then
.Caption = "Turn off input range"
.FaceId = 353
Else
.Caption = "Turn on input range"
.FaceId = 352
End If
End With
What would this modification be like?
Thanks a lot.
Bookmarks