+ Reply to Thread
Results 1 to 4 of 4

Toggle Button 'State' Problem on Workbook Open

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Toggle Button 'State' Problem on Workbook Open

    I have a Workbook that, when opened, hides the 'Status Bar', the 'Formula Bar' and the 'Worksheet Tabs' and resets them when the workbook is closed.

    Private Sub Workbook_Activate()
        On Error Resume Next
        With Application
            .DisplayFormulaBar = False
            .DisplayStatusBar = False
            .CommandBars("Worksheet Menu Bar").Enabled = False
        End With
    
        With ActiveWindow
            .DisplayWorkbookTabs = False
        End With
    
    End Sub
    
    Private Sub Workbook_Deactivate()
        On Error Resume Next
        With Application
            .DisplayFormulaBar = True
            .DisplayStatusBar = True
            .CommandBars("Worksheet Menu Bar").Enabled = True
        End With
    
        With ActiveWindow
            .DisplayWorkbookTabs = True
        End With
    
    End Sub

    On Sheet1, I have a Shape (Rounded Rectangle) which I'm using as a toggle button.

    The button starts by saying 'Show'. When the user clicks on the button, the macro below, shows the formula bar, the status bar and the worksheet tabs and the button text changes to 'Hide'. So far, so good.

    If the user now saves the worksheet, closes it and then re opens it, the toggle button still say's 'Hide' because this is the how it was left when the user saved and closed the workbook, however, because the workbook is set to open fullscreen, the button actually needs to say 'Show', when the workbook is opened?

    How can I ensure that when the workbook is opened, the button text will always say 'Show' by default, irrespective of what it said when the user saved and closed the workbook?

    Sub RoundedRectangle2_Click()
    
        Dim shp As Shape
        
        With ActiveSheet
            Set shp = .Shapes(Application.Caller)
    
        End With
        
        With shp.TextFrame.Characters
            If .Text = "Show" Then .Text = "Hide" Else .Text = "Show"
        End With
        
        With ActiveSheet
            Application.DisplayStatusBar = Not Application.DisplayStatusBar
            Application.DisplayFormulaBar = Not Application.DisplayFormulaBar
            ActiveWindow.DisplayWorkbookTabs = Not ActiveWindow.DisplayWorkbookTabs
        End With
        
    End Sub
    I've attached a simple sample to demonstrate.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Toggle Button 'State' Problem on Workbook Open

    The in the workbook_open event add

    Sheets(1).Shapes("Rounded Rectangle 2").TextFrame.Characters.Text = "Show"
    Please take time to read the forum rules

  3. #3
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Toggle Button 'State' Problem on Workbook Open

    Steffan,

    Many thanks for all your help, your code is working perfectly now that I realsied how to add the activate and deactivate correctly and this latest addition solves my button text problem as well...

    Your help is greatly appreciated...

  4. #4
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Toggle Button 'State' Problem on Workbook Open

    Glad i could help.

    Remember to mark your threads as solved if the answer given has solved your problem.

+ 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