Show/Hide Columns

[Guest]

I want my user to be able to show or hide several columns in my worksheet.
If they are already hidden, they should unhide and vice versa. The following
will unhide hidden columns, but will not hide unhidden columns. What am I
doing wrong?

Private Sub Detail_Click()
' Show/Hide Detail

If Columns("B:Y").Hidden = False Then
Columns("B:Y").Hidden = True
Range("a1").Activate
End If

If Columns("B:Y").Hidden = True Then
Columns("B:Y").Hidden = False
Range("a1").Activate
End If

End Sub

 

[DM Unseen]

Use EntireRow and EntireColumn

e.g. Range("B:Y").EntireColumn.Hidden = true

DM Unseen

 

[Anne Troy]

Try this, Stephanie:

Sub Detail_Click()
' Show/Hide Detail

If Columns("B:C").Hidden = False Then
Columns("B:C").Hidden = True
Else
If Columns("B:C").Hidden = True Then
Columns("B:C").Hidden = False
Range("a1").Activate
End If
End If


End Sub
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

 

[JE McGimpsey]

Your code first hides visible columns, then unhides invisible columns.
You could try an If...Then...Else...End If structure:

Private Sub Detail_Click()
' Show/Hide Detail
If Columns("B:Y").Hidden = False Then
Columns("B:Y").Hidden = True
Else
Columns("B:Y").Hidden = False
End If
Range("a1").Activate
End Sub

or you could "toggle" between hidden and not hidden:

Private Sub Detail_Click()
' Show/Hide Detail
With Columns("B:V")
.Hidden = Not .Hidden
End With
End Sub

 

[Anne Troy]

I'll just keep answering wrong and lame, and you just keep teaching me,
okay, JE?
This is only about the 4th or 5th question in the last 24 hours where I've
learned...
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

 

[Bob Phillips]

Columns("B:Y").Hidden = Not Columns("B:Y").Hidden

 

[Guest]

Works perfectly. Thanks.

Try this, Stephanie:

Sub Detail_Click()
' Show/Hide Detail

If Columns("B:C").Hidden = False Then
Columns("B:C").Hidden = True
Else
If Columns("B:C").Hidden = True Then
Columns("B:C").Hidden = False
Range("a1").Activate
End If
End If


End Sub
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

 

[JE McGimpsey]

Well, first, your code wasn't wrong. It wasn't as efficient as mine, but
the primary consideration should be: does it work when you test it.

Second - posting your solutions is a *great* way to learn. There's
nothing like coming up with a workable answer and posting it, only to
find someone else has posted a different, perhaps more elegant answer
that you can compare it to and learn from. Happens to me all the time...

 

[Anne Troy]

LOL, JE. I have perhaps 30,000 posts in forums "upstairs" (I always see the
newsgroups as some kind of basement. I dunno why!). There is no end to
learning. Now, if I could just figure out how to quit losing the threads
here in the newsgroups, I'd get a lot more posts down here!

Thanks for teaching me!
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com