PDA

View Full Version : [SOLVED] Show or hide images, according to the value of a cell



brunces
07-25-2005, 01:08 PM
Friends,

How are yaz? :)

Please, if possible, take a look at my "attached file".

What I want is...

I have 3 images in a sheet. According to the value of a cell, I want these images to be shown or hidden. Something like this...

Criteria 1:
- If B4 = 1, ribbon 1 is shown and ribbons 2 and 3 are hidden (not deleted).
- If B4 = 2, ribbon 2 is shown and ribbons 1 and 3 are hidden (not deleted).
- If B4 = 3, ribbon 3 is shown and ribbons 1 and 2 are hidden (not deleted).
- If B4 <> 1, 2 or 3, all ribbons are hidden (not deleted).

Or...

Criteria 2:
- If B4 = 1, ribbon 1 comes to the top (and ribbons 2 and 3 stay in back).
- If B4 = 2, ribbon 2 comes to the top (and ribbons 1 and 3 stay in back).
- If B4 = 3, ribbon 3 comes to the top (and ribbons 1 and 2 stay in back).
- If B4 <> 1, 2 or 3, all ribbons are hidden (not deleted).

That's it! Any of the criterias above is useful to me.

I hope you can help me, for I'm really in need of this! :)

Thanks all for the attention.

Hugs. :)

Bruno

MOS MASTER
07-25-2005, 02:26 PM
Hi Bruno, :yes

You can use a User Defined function for that.

Like this:


Public Function ShowRibbon(oRange As Excel.Range)
Select Case oRange.Value
Case Is = 1
HideShapes True, False, False
Case Is = 2
HideShapes False, True, False
Case Is = 3
HideShapes False, False, True
Case Else
HideShapes False, False, False
End Select
End Function

Private Sub HideShapes(bOne As Boolean, _
bTwo As Boolean, bThree As Boolean)
With Sheets("Place")
.Shapes("Picture 1").Visible = bOne
.Shapes("Picture 2").Visible = bTwo
.Shapes("Picture 3").Visible = bThree
End With
End Sub


In your sheet I types in "C4": "=ShowRibbon(B4)" to make it work.

See attachment for the finished job!

HTH, :whistle:

Bob Phillips
07-25-2005, 04:21 PM
Hi Bruno, :yes

You can use a User Defined function for that.

In your sheet I types in "C4": "=ShowRibbon(B4)" to make it work.



Why use a range Joost, better to pass the argument direct =ShowRibbon(1)?

Zack Barresse
07-25-2005, 04:23 PM
Why use a range Joost, better to pass the argument direct =ShowRibbon(1)?
.. or Variant..

MOS MASTER
07-25-2005, 04:42 PM
To be honest guys I just took the first best thing that came to mind. :yes

So yes there are plenty of alternatives...but they don't matter for fuctionality or speed in this case.

But you're of course right just passing the value or using a variant could work just as well....(and perhaps the next time that will come in mind first) :whistle:

OBP
07-26-2005, 01:37 AM
Using =ShowRibbon(1) in c4 gives a #Value! error.

Bob Phillips
07-26-2005, 03:37 AM
Using =ShowRibbon(1) in c4 gives a #Value! error.

You have to change th code to cater for it.



Public Function ShowRibbon(pzPicture)
Select Case pzPicture
Case Is = 1
HideShapes True, False, False
Case Is = 2
HideShapes False, True, False
Case Is = 3
HideShapes False, False, True
Case Else
HideShapes False, False, False
End Select
End Function

brunces
07-26-2005, 07:38 AM
Friends,

First I'd like to tell you that it worked pretty fine, exactly the way I wanted it to. Thank you very much for your attention and help, guys. :clap:

Now, if possible, and if you don't mind, of course, I'd like to "understand" what was created, I mean, I'm a beginner in VBA, you know, and I always try to learn how to do these codes, so that I don't need to bother you again if I need to create something similar in the future. :)

Well, about HideShapes...

You declared bOne, bTwo and bThree as Boolean. OK!
You associated Picture 1 with bOne, etc. OK! But...

1) How does Excel know that "Picture 1" is the Ribbon 1?, "Picture 2" is the Ribbon 2? and "Picture 3" is the Ribbon 3?? is it because of the order that I insert each one in the sheet?
2) I tried to change the word "Picture #" and it didn't work out. Is this a default word to identify images in a sheet? (If so, cool! Good to know! That was my major doubt: how to NAME the images to deal with their properties.) :)

---

About ShowRibbon

3) You guys talked about Variant instead of Range... Sorry, but what did you mean? Would it be smoother? I mean, less risks of bad behavior of the code, or it doesn't matter (any fo them fits fine)? :thumb

---

To eliminate the function, I've made some changes in the code and put it in sheet's code. Please, I'd like you to see it and tell me if it's OK. At least, it's working fine.

Codes for sheet PLACE



Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("B4").Value
Case Is = 1
HideRibbons True, False, False
Case Is = 2
HideRibbons False, True, False
Case Is = 3
HideRibbons False, False, True
Case Else
HideRibbons False, False, False
End Select
End Sub

Private Sub HideRibbons(picRibbon1 As Boolean, _
picRibbon2 As Boolean, picRibbon3 As Boolean)

With Sheets("Place")
.Shapes("Picture 1").Visible = picRibbon1
.Shapes("Picture 2").Visible = picRibbon2
.Shapes("Picture 3").Visible = picRibbon3
End With
End Sub



Is it correct?

Thank you very much, guys. :bow:

Hugs.

Bruno

Bob Phillips
07-26-2005, 09:18 AM
1) How does Excel know that "Picture 1" is the Ribbon 1?, "Picture 2" is the Ribbon 2? and "Picture 3" is the Ribbon 3?? is it because of the order that I insert each one in the sheet?

Every time a picture is inserted into a workbook it is given the name Picture # where # is the next sequential number.

So, yes you understand correctly.


2) I tried to change the word "Picture #" and it didn't work out. Is this a default word to identify images in a sheet? (If so, cool! Good to know! That was my major doubt: how to NAME the images to deal with their properties.)

It is the id within the Shapes collection. You changed the name property, not the container name.


3) You guys talked about Variant instead of Range... Sorry, but what did you mean? Would it be smoother? I mean, less risks of bad behavior of the code, or it doesn't matter (any fo them fits fine)?

Joost's code was a worksheet UDF, and as he used a range, you needed to pass a range to the UDF telling it where to find the action value. All we suggested was bypassing the cell reference, add the action value in the UDF call directly. Akin to what Target does in the Worksheet_Change event.


To eliminate the function, I've made some changes in the code and put it in sheet's code. Please, I'd like you to see it and tell me if it's OK. At least, it's working fine.

Is it correct?

It works so it must be correct. The only thing I would suggest is not hard-coding Pace sheet name, especially as you have a handle.



Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("B4").Value
Case Is = 1
HideRibbons True, False, False, Me
Case Is = 2
HideRibbons False, True, False, Me
Case Is = 3
HideRibbons False, False, True, Me
Case Else
HideRibbons False, False, False, Me
End Select
End Sub

Private Sub HideRibbons(picRibbon1 As Boolean, _
picRibbon2 As Boolean, picRibbon3 As Boolean, _
sh As Worksheet)
With sh
.Shapes("Picture 1").Visible = picRibbon1
.Shapes("Picture 2").Visible = picRibbon2
.Shapes("Picture 3").Visible = picRibbon3
End With
End Sub

brunces
07-26-2005, 09:37 AM
xld, Joost, OBP...

Thank you very much for your help. You're very cool, guys! :friends:

Hugs for all.

Bruno

MOS MASTER
07-26-2005, 09:39 AM
Using =ShowRibbon(1) in c4 gives a #Value! error.

My code expects a range you aren't giving it one! :yes

MOS MASTER
07-26-2005, 09:42 AM
xld, Joost, OBP...

Thank you very much for your help. You're very cool, guys! :friends:

Hugs for all.

Bruno

Glad we could help Bruno! :yes

brunces
07-26-2005, 09:58 AM
Hey, xld (and friends)!

Sorry, just one more doubt...

Please, what is that "Me" you put in the code for? Just for me to understand it, if you don't mind... :think: (eheheh Beginners are a real pain! :yes )

Thanks, guys!

Bruno

MOS MASTER
07-26-2005, 10:03 AM
Hi, :yes

"Me" is a simple way to refer to the object the code is in.

In this case Bob's code is pasted in the Worksheetcode (Right click sheet tab en choose "view code")

So in Bob's example "Me" refers to that Worksheet!

If the code was in class module ThisWorkbook "Me" would refer to ThisWorkbook object. If the code was in a UserForm, "Me" would refer to the UserForm object....etc..

HTH, :whistle:

brunces
07-26-2005, 10:09 AM
Huuuuummm... Coooool!!!

I did know about the reference, I just didn't know it was used so. :yes

Thank you very much, Joost!

Hugs, fellas! You're great! :thumb

Bruno

MOS MASTER
07-26-2005, 10:10 AM
No problem come back any time! :yes

Bob Phillips
07-26-2005, 10:20 AM
In this case Bob's code ...

Yours and Bruno's code Joost, I just made a minor tweak.

.

Bob Phillips
07-26-2005, 10:22 AM
(eheheh Beginners are a real pain! :yes )

No they are not, they are the reason we keep at it, watching them develop their Excel skills until they are as good as Joost.

Bob Phillips
07-26-2005, 10:25 AM
"Me" is a simple way to refer to the object the code is in.

And Bruno, athat is what I meant when I said we already had a handle. As the code was triggered froma worksheet, Me refers to that worksheet, so we don't need to know its name or anything about it, we can just pass it to our generic function that hides stuff. This way, if you wan t to do something similar on another sheet, the function will still work (providing you drop it into a standard code module).

MOS MASTER
07-26-2005, 10:26 AM
Yours and Bruno's code Joost, I just made a minor tweak.

.

True I meant our joined effort! :yes

MOS MASTER
07-26-2005, 10:27 AM
No they are not, they are the reason we keep at it, watching them develop their Excel skills until they are as good as Joost.

Aren't we the modest one over here! :yes

To expand on that sentence: "Or as brilliant as Bob!"

brunces
07-26-2005, 12:12 PM
Friends,

Me again! Just one doubt...

Do we have to put a "Exit sub" for each "Case"? Example...



...
Select Case Range("B4").Value
Case Is = 1
HideRibbons True, False, Me
Exit Sub
Case Is = 2
HideRibbons False, True, Me
Exit Sub
Case Else
...


Is it necessary to do this or not?

One more thing, maybe it's just a coincidence, but after putting this code in my file and using it, Excel now disables buttons such as "One level up", "Desktop", "My Documents", "Bookmarks" in the "Open File" and "Save As" dialog boxes. When I close Excel and start it again, it returns to normal. When I open that file, it happens again. There are no more codes in the file, but those. Queer! :(

Thank you, guys!

Bruno

MOS MASTER
07-26-2005, 12:30 PM
Friends,

Me again! Just one doubt...

Do we have to put a "Exit sub" for each "Case"? Example...



...
Select Case Range("B4").Value
Case Is = 1
HideRibbons True, False, Me
Exit Sub
Case Is = 2
HideRibbons False, True, Me
Exit Sub
Case Else
...


Well it would depend. I posted a function and if you would still use that Exit Function would be more apropriate. Be adviced put your full code in a question at least with the start and end of a procedure...cause otherwise we cannot answer in a proper way)

But to answer your question...No you don't have to puth Exit sub (or exit function) in the select case statement. (Or you would have good reason to do so)

A select case structure is like a If ElseIf Else Structure.
So if the case is this to that...if case is that do that..if something else do that.

But you can descide for your selve. I would only use the EXIT statement if I really wanted to abort the sub/function at that point in your code.



One more thing, maybe it's just a coincidence, but after putting this code in my file and using it, Excel now disables buttons such as "One level up", "Desktop", "My Documents", "Bookmarks" in the "Open File" and "Save As" dialog boxes. When I close Excel and start it again, it returns to normal. When I open that file, it happens again. There are no more codes in the file, but those. Queer! :(

Thank you, guys!

Bruno

This Part I don't understand. And I'm not able to reproduce this behaviour. Let alone with this simple code.

HTH, :whistle: