Toggle change control button face id & caption
 

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, _
befo=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

--
mikebur
-----------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...fo&userid=2458
View this thread: http://www.excelforum.com/showthread.php?threadid=38215

Toggle change control button face id & caption
 

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" wrote in
message ...

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, _
befo=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