+ Reply to Thread
Results 1 to 2 of 2

Toggle change control button face id & caption

  1. #1
    Registered User
    Join Date
    10-03-2009
    Location
    germany
    MS-Off Ver
    Excel 2003
    Posts
    1

    Smile Toggle change control button face id & caption

    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.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Toggle change control button face id & caption

    Welcome to the Board, however, please note

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1