Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Get a Value for a Toggle Button (2003)

    I have a toggle button in a userform that shows/hides an extra column and sets the form to Portrait or Landscape depending on whether it's showing or not. It's working nicely, but -- naturally -- I complicated my life by having a macro to run that, among other things, conceals the column. I'd like to get a value for whether the column is hidden or unhidden and use the value to reset the column as the user had it before exiting the macro. This one should be do-able, but I haven't found exactly how to do it. Toggle button code is here, though it probably won't help much:

    Private Sub ToggleButton1_Click()
    If ToggleButton1.Value = False Then
    ActiveSheet.Unprotect
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True

    With ActiveSheet.PageSetup
    .Orientation = xlPortrait
    End With

    ToggleButton1.Caption = "SHOW EXTRA COLUMN"

    ElseIf ToggleButton1.Value = True Then

    ActiveSheet.Unprotect
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = False

    With ActiveSheet.PageSetup
    .Orientation = xlLandscape
    End With

    ToggleButton1.Caption = "HIDE EXTRA COLUMN"

    End If
    End Sub

    Many thanks,

    Ann

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get a Value for a Toggle Button (2003)

    The code you have posted should work. Do you get an error message?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Get a Value for a Toggle Button (2003)

    Does this do what you want?

    Private Sub ToggleButton1_Click()
    If Columns("G:G").Hidden = False Then
    ActiveSheet.Unprotect
    Columns("G:G").Hidden = True
    ActiveSheet.PageSetup.Orientation = xlPortrait
    ToggleButton1.Caption = "SHOW EXTRA COLUMN"
    Else
    ActiveSheet.Unprotect
    Columns("G:G").Hidden = False
    ActiveSheet.PageSetup.Orientation = xlLandscape
    ToggleButton1.Caption = "HIDE EXTRA COLUMN"
    End If
    End Sub

  4. #4
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Get a Value for a Toggle Button (2003)

    I wasn't clear, sigh.

    The existing toggle button code has given no problems -- I was feeling very clever that it not only shows/hides the column but simultaneously toggles back and forth between Portrait and Landscape.

    What I'm looking for is to have a separate macro get a value for the toggle button (or for whether Column G is hidden or visible, either will do).

    MyOtherMacro

    'Get the information as to whether the toggle is True or False, or whether Column G is hidden or not -- however the user has it set at that moment

    .... [MyOtherMacro runs through its paces, including hiding Columns G and H whether already hidden or not]

    ....
    Using the information from the toggle or elsewhere:
    IF G was hidden at the start of MyOtherMacro, keep it hidden, don't UnHide it
    IF G was not hidden at the start of MyOtherMacro, unHide it.
    UnHide H (no problem)
    End

    Obviously this is hardly good VBA code, but I hope it makes sense.

    At the moment, Column G ends up at exiting MyOtherMacro either showing or hiding -- depending on what I instructed VBA to do -- but it's a little disconcerting for the user to see it with the toggle button declaiming SHOW EXTRA COLUMN. In practice, clicking on the button twice will put the whole business back in sync, but it's the sort of thing that is likely to aggrevate a vice-president. In any case, I don't think this is a toggle-button fix; it should be a piece of VBA housekeeping as it exits MyOtherMacro.

    Ann

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Get a Value for a Toggle Button (2003)

    I am not sure I understand completely but are you looking for something like this?

    Steve

    <pre>Option Explicit
    Sub MyOtherMacro()
    Dim bColGHidden As Boolean
    'Check current Col G and strore
    bColGHidden = Columns("G:G").Hidden

    'Do your "stuff"

    'reset Col G to what it was before "stuff" was done
    Columns("G:G").Hidden = bColGHidden
    'UnHide Col H
    Columns("H:H").Hidden = False
    End</pre>


Posting Permissions

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