PDA

View Full Version : Solved: make ALL labels invisible (visible=false)



razer
12-18-2007, 11:16 AM
OK here my problem: On my Excel sheet I have several buttons, lists and labels. What these buttons should do is make a label visible and every button makes a different label visible. What I could do is this:
label1.visible = true
label2.visible = false
label3.visible = false
etc.
and for the next button:
label1.visible = false
label2.visible = true
label3.visible = false
etc.
but because I have a lot of buttons and labels, that would be a lot of text and I really don't like to type anything more than needed. So what I am looking for is a peace of code that turns ALL the labels off in one click. This way I would get something like this:
label(all).visible = false
label1.visible = true

label(all).visible = false
label2.visible = true
This would be only 2 lines for each button instead of 16 lines.
Hope any of you have an idea, thanx.


edited the layout a bit, so other people can make sense of it to

Simon Lloyd
12-18-2007, 12:38 PM
try this:

Sub label_hide()
Dim i As Integer
For i = 1 To 100
On Error GoTo Nxt
ActiveSheet.Shapes("Label" & i).Visible = False
Next
Nxt:
End Sub

Bob Phillips
12-18-2007, 12:40 PM
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 5) = "Label" Then
shp.Visible = False
End If
Next shp
ActiveSheet.Labels("Label 2").Visible = True

Simon Lloyd
12-18-2007, 01:14 PM
Nice Bob, but are there advantages or disadvantages from using either yours or my code?, one i can think of is that mine needs to have the error handling (for some reason i couldnt COUNT the labels on the active sheet to determine For i = 1 to ActiveSheet.Labels.Count)

Bob Phillips
12-18-2007, 01:30 PM
No particular issue than I can see Simon, apart the error trap.

But you can avoid that, you use a different collection



Activesheet.Labels.Count

razer
12-18-2007, 02:41 PM
OK, this is what I ended up with that worked:

Private Sub CommandButton4_Click()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 5) = "Label" Then
shp.Visible = False
End If
Next shp
Label1.Visible = True
End Sub
So, thanx for your help guys, and I will be back here for some more help I think. I am just getting the hang of the simple VBA code and will be trying something harder in the future :). So see you guys around.

unmarkedhelicopter
12-19-2007, 03:50 AM
No, you are again writting TOO much code.
Try :-
Private Sub AllInvis()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 5) = "Label" Then shp.Visible = False
Next shp
End Sub

Private Sub CommandButton4_Click()
Call AllInvis
Label1.Visible = True
End Sub

Private Sub CommandButton5_Click()
Call AllInvis
Label9.Visible = True
End Sub

Private Sub CommandButton6_Click()
Call AllInvis
Label2.Visible = True
End Sub

... etc.

razer
12-19-2007, 11:31 AM
OK thanx for that last code, it is a lot less typing :).
I ended up with the code below. I also edded some images that needed to switch on and off.

Private Sub AllInvis()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 5) = "Label" OR Left(shp.Name, 5) = "Image" Then shp.Visible = False
Next shp

End Sub

Private Sub CommandButton100_Click()
Call AllInvis
Label100.Visible = True
Image100.Visible = True
End Sub

Private Sub CommandButton101_Click()
Call AllInvis
Label101.Visible = True
Image101.Visible = True
End Sub

...etc

Do you guys wanna see what I'm trying to make? If so I'll post my Excel document. I must say I'm quit prode of it already.


edited to an even shorter code

Bob Phillips
12-19-2007, 11:47 AM
Go the whole hog



Private Sub AllInvis(ByRef pzLabel, ByRef pzImage)
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 5) = "Label" Or Left(shp.Name, 5) = "Image" Then shp.Visible = False
Next shp

pzLabel.Visible = True
pzImage.Visible = True

End Sub

Private Sub CommandButton100_Click()
Call AllInvis(Label:=Label100, Image:=Image100)
End Sub

Private Sub CommandButton101_Click()
Call AllInvis(Label:=Label101, Image:=Image101)
End Sub

Simon Lloyd
12-19-2007, 12:14 PM
:)

razer
12-19-2007, 01:47 PM
OK ever shorter, nice job :) but you made a small mistake. It should be:

Private Sub CommandButton100_Click()
Call AllInvis(pzLabel:=Label100, pzImage:=Image100)
End Sub
So with the pzLabel and pzImage

Bob Phillips
12-19-2007, 02:03 PM
OK you got me, but at least I got the idea across. I started calling it Label and thought maybe that was not a good idea :-)

razer
12-19-2007, 02:40 PM
nope calling it Label is not a good idea. I tried that :)
I did feel a bit stupid after I realized my mistake.