Consulting

Results 1 to 16 of 16

Thread: Solved: Transparent Toggle Button

  1. #1

    Solved: Transparent Toggle Button

    This is the first time I have ever tried to code anything in VBA so bare with me please. I am trying to create a toggle button in Excel 2003 that is transparent so that you can see the text/formatting in the cell behind it. When you click the button it will hide/unhide specific rows. So far I've been able to get the toggle to do just that except for the transparent part. In the properties window for the toggle I made sure that the backstyle is set to 0 - fmBackStyleTransparent. This makes the button transparent but as soon as I click on it, it turns opaque. Am I missing something? Any and all help would be greatly appreciated.

    Here is the code I'm working with for the hide/unhide function:
    [vba]Private Sub ToggleButton1_Click()
    If ToggleButton1.Value = False Then
    Range("a12:a12").EntireRow.Hidden = False
    Else
    Range("a12:a12").EntireRow.Hidden = True
    End If
    End Sub[/vba]

    Thanks!

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    How about using a shape from the drawing toolbar and set the transparfency ratio of the shape?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Hi Brett,
    I don't understand buttons on the sheet anyway....they are ugly and always in the way. Why not use a menu item or a toolbar button?

    Just my opinion..
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  4. #4
    Quote Originally Posted by lucas
    Hi Brett,
    I don't understand buttons on the sheet anyway....they are ugly and always in the way. Why not use a menu item or a toolbar button?

    Just my opinion..
    Very true. Keep in mind I'm a complete rookie at this. Messing around with it a little more I was able to use the "image" control from the control toolbar to do the same function but without the ugly raised box. On clicking the transparent image I still get an opaque box until I roll my mouse off of it. BackStyle is set to transparent and BorderStyle is set to none. How do I make it transparent once clicked? Also, I'm having a little bit of an issue adding seperate ranges to my code.

    [VBA]Private Sub Image1_Click()
    If Range("a12:a18").EntireRow.Hidden = True Then
    Range("a12:a18").EntireRow.Hidden = False
    Else
    Range("a12:a18").EntireRow.Hidden = True
    End If
    End Sub[/VBA]

    This shows/hides rows 12-18 but how can I pick up rows 21-32 at the same time?

    I realize what I'm essentially trying to do is add groups in Excel but I was told this was not an option as it is not "asthetically pleasing"........


    Thanks for the quick responses so far! It's much appreciated.

  5. #5
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    [VBA]If Range("a12:a18").EntireRow.Hidden = True Then
    Range("a12:a18").EntireRow.Hidden = False
    Range("a21:a32").EntireRow.Hidden = False
    Else
    Range("a12:a18").EntireRow.Hidden = True
    Range("a21:a32").EntireRow.Hidden = True
    End If
    [/VBA]
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  6. #6
    Quote Originally Posted by lucas
    [vba]If Range("a12:a18").EntireRow.Hidden = True Then
    Range("a12:a18").EntireRow.Hidden = False
    Range("a21:a32").EntireRow.Hidden = False
    Else
    Range("a12:a18").EntireRow.Hidden = True
    Range("a21:a32").EntireRow.Hidden = True
    End If
    [/vba]
    Excellent, Thanks! Now how do I keep the "image" from going opeque when clicked? I do not know how to use an autoshape as a button as XLD suggested.

    Thanks for taking the time in helping me to resolve this.

  7. #7
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    could you post your file?
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    Quote Originally Posted by brettnewbie
    Excellent, Thanks! Now how do I keep the "image" from going opeque when clicked? I do not know how to use an autoshape as a button as XLD suggested.

    Thanks for taking the time in helping me to resolve this.
    Just drag the shape off of the drawing toolbar, right click it and format it.

    Take a look at http://www.xldynamic.com/tutorials/V...rettnewbie.htm
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    I love that Bob. Makes communicating steps much easier...
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  10. #10
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    I agree Steve. When someone just doesn't understand what we are saying, pictures really are worth a thousand words.

    Maybe an idea for VBAX to host these files. I can't use a normal filesharing because there are two files, and they (presumably) will store them wherever.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  11. #11
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    This strikes me as a breakthrough in what we try to do here. Surely the Admins here will consider it. I'm going to leave a note in the mod area with a link to this if you don't mind Bob.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  12. #12
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    Of course I don't, be my guest, and good luck with it.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  13. #13
    Quote Originally Posted by xld
    Just drag the shape off of the drawing toolbar, right click it and format it.
    That works perfectly. Great tutorial also. I appreciate all the help and suggestions from everyone.

    Thank you!

  14. #14
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    Brett,

    I know NJ a bit. You anywhere near Summit?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  15. #15
    Quote Originally Posted by xld
    Brett,

    I know NJ a bit. You anywhere near Summit?
    I'm about 40 minutes from Summit in the Chester/Mendham area.

  16. #16
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    Not that far then, just the other side of 287.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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