PDA

View Full Version : Solved: Transparent Toggle Button



brettnewbie
10-17-2007, 10:45 AM
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:
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

Thanks!

xld
10-17-2007, 10:54 AM
How about using a shape from the drawing toolbar and set the transparfency ratio of the shape?

lucas
10-17-2007, 10:57 AM
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..

brettnewbie
10-17-2007, 12:16 PM
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.

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

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"........:banghead:


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

lucas
10-17-2007, 01:12 PM
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

brettnewbie
10-17-2007, 01:16 PM
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


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. :thumb

lucas
10-17-2007, 01:18 PM
could you post your file?

xld
10-17-2007, 01:52 PM
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. :thumb

Just drag the shape off of the drawing toolbar, right click it and format it.

Take a look at http://www.xldynamic.com/tutorials/VBAExpress/brettnewbie.htm

lucas
10-17-2007, 03:02 PM
I love that Bob. Makes communicating steps much easier...

xld
10-17-2007, 03:08 PM
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.

lucas
10-17-2007, 03:12 PM
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.

xld
10-17-2007, 03:13 PM
Of course I don't, be my guest, and good luck with it.

brettnewbie
10-18-2007, 05:34 AM
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!

xld
10-18-2007, 06:05 AM
Brett,

I know NJ a bit. You anywhere near Summit?

brettnewbie
10-18-2007, 06:47 AM
Brett,

I know NJ a bit. You anywhere near Summit?

I'm about 40 minutes from Summit in the Chester/Mendham area.

xld
10-18-2007, 07:18 AM
Not that far then, just the other side of 287.