combining two short VBA scripts from two buttons

[sloan.fader]

learning VBA and Macros in Excel, so forgive my newbie-ness.

I have two button forms, each with it's own macro.

Sub Button8_Click()
Range("J2:Q2").Select
Selection.EntireColumn.Hidden = True
End Sub

Sub Button9_Click()
Range("I2:R2").Select
Selection.EntireColumn.Hidden = False
Range("G2").Select
End Sub


One button(8) hides columns, the other button(9) reveals them. Is
there a simple bit of code that will allow me to combine these
functions onto one button? The macro that runs on click would depend on
the state of the sheet. If columns are hidden, the button would run
the "unhide" macro; if columns are unhidden, the button would run the
"hide" macro.

Also, as a nice touch, the button text would change to reflect
appropriate pending action.

This is, I guess, a basic on/off situation, but I can't figure it out.

 

[Bob Phillips]

Sub Button8_Click()
With Range("J2:Q2")
.EntireColumn.Hidden = not .EntireColumn.Hidden =
End With
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Rick Hansen]

Hey Sloan, You started off on the right foot, but as you learn VBA, you'll
find easier ways as I have. Here is some code you can try. I used the
CommandButton from the Control ToolBox for Button on the Worksheet. Good
Luck, keep try different things...

Rick


Private Sub CommandButton1_Click()
Static flag As Boolean

With Range("J:Q")
If Not flag Then
.EntireColumn.Hidden = True
CommandButton1.Caption = "UnHide Col"
flag = True
Else
.EntireColumn.Hidden = False
CommandButton1.Caption = "Hide Col"
flag = False
End If
End With

End Sub

 

[sloan.fader]

That's great!! I grabbed this code and switched over to the control
toolbox button. It's right-on, and I can (mostly) understand what's
going on. If/Then = on/off.

Bob, thanks for your idea, too. I couldn't get it to work, though. VBA
editor said there was a syntax error in the line:
.EntireColumn.Hidden = not .EntireColumn.Hidden =



A follow up question:
What's the difference between using the button from the
Tools-->Customize-->Commands:Forms area versus the button from the
Control Toolbox?

And can anyone reccommend a good starter text for basic VBA I'd use in
Excel?

 

[Rick Hansen]

Hey Sloan, To answer question about Form Control verus Control ToolBox
Controls is. The Forms Controls a hold over from Excel 5 and Excel 95. which
gives you basic Controls. The Contol ToolBox Control are ActiveX controls.
The ActiveX controls have events and properties that set and use for more
flexible use of the required control. ie I reset the Caption property each
time I clicked on the CommandButton. The forms control does not have this
capability.
There serval good books on begining VBA for Excel. John Walkenbach has
severl books, I use," Excel 2000 Power Programing with VBA", I also use
John Green's , "Excel 2000 VBA Programmer's Reference".

HTH again, Rick

PS: Yes, the if/then is acting as on/off with the use of the Static
Boolean variable 'flag'. When the excel program initially starts the flag
variable is set to false. When the CommandButton is clicked the Caption of
of button changes, also the value of flag will change to true. Click the
Button again and the Caption changes back, the flag value is set back to
false. I should also note the selected columns hide and unhide, with each
toggle of the CommandButton. That in a nut shell is how the code works.

 

[Bob Phillips]

A stupid extra = crept in at the end of the line, delete it and it should be
fine.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[sloan.fader]

I've noticed a quirk with the button/VBA. When I first open the
workbook, if I saved with the worksheet in the "hide" state, the button
has to be clicked twice. The first click it doesn't react, then the
second click it "unhides". After that initial after opening bug, the
button works fine.

Does this have anything to do with running the macro when the book
opens? Would that solve the quirk?