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!

How about using a shape from the drawing toolbar and set the transparfency ratio of the shape?

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

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


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

[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]

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

could you post your file?

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. :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/V...rettnewbie.htm

I love that Bob. Makes communicating steps much easier...

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.

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.

Of course I don't, be my guest, and good luck with it.

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!

Brett,

I know NJ a bit. You anywhere near Summit?

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.

Not that far then, just the other side of 287.