In , Dave Peterson
spake thusly:

Why not add 3 optionbuttons and then let the user select the one
they want?

Well, the user is me. I don't want that many buttons devoted to
this one silly set of actions. I have other buttons in mind and
will have a whole damn row of buttons. :-)


================================================== ==============
Dallman Ross wrote:

In response to my original --

Short version: I want a macro to hide columns of a certain
cell color and font color.

Dave Peterson had replied as
follows:

Option Explicit Sub testme()

Dim iCol As Long

With Worksheets("sheet1") For iCol = 1 To 30 If
.Cells(1, iCol).Font.ColorIndex = 6 _ And .Cells(1,
iCol).Interior.ColorIndex = 35 Then .Columns(iCol).Hidden =
True Else .Columns(iCol).Hidden = False End If Next iCol End
With End Sub

As I posted already, this is great and solved my problem. Now
I'm trying to take it a bit further. This is an early foray
for me into the world of VBA macros. I'm stuck on a couple of
new ideas.

Okay, I decided it would be cool to make a button (control
box) to toggle my hidden fields on and off. I'd never made
a button like that before, though I've been using Excel for
years. Well, I did a Google search and found this excellent
page:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=416

I converted the above code of our Dave's to a toggle button.
It now looks like so:

Option Explicit

Private Sub ToggleButton1_Click()

Dim iCol As Long

With Worksheets("2006 Realized Gains") For iCol = 1 To 30
If .Cells(2, iCol).Font.ColorIndex = 5 _ And .Cells(2,
iCol).Interior.ColorIndex = 24 Then

.Columns(iCol).Hidden = ToggleButton1.Value
Else .Columns(iCol).Hidden = False End If Next iCol End With
End Sub

Well, that works, and I think it's slick! (Setting the .Hidden
value to ToggleButton1.Value was my idea. If I set the "Else"
value to "Not ToggleButton1.Value", that's kind of fun also:
as you probably see already, the toggle then is back and forth
between hidden and unhidden columns.

I actually would find it useful (or at least loads of fun!)
to have a three-way toggle: (a) show the regular (normally
unhidden) cols; (b) show only the regularly hidden cols; and
(c) show all cols.

In order to do that, I suppose one way is to examine some state
or other at the top of the Else region. I'm stuck for how,
though. Ideas gladly entertained!

The next wish for improvement is to use something like
Bob Phillips shows in a concurrent article in this group,
Message-ID: :

Do While Activecell.Offset(0,-1).Value < "" 'do some stuff
Activecell.Offset(1,0).Select Loop

I see what he's doing, but I am too weak on actual VBA code
to have yet been able to figure out how to incorporate that
concept into the above. In other words, lose the "For 1-30"
loop I have and replace it with a Do-While loop.

Thanks for further suggestions!

Dallman Ross