Change Pictures

[sivadnitram]

I have a spread sheet that has a picture (picture2) on top of which there is another picture (picture1). Both pictures are of the same size. I have a macro (named pic1toback) which when run selects picture 1 and orders it to the back. Picture 2 is now visible. This macro works fine. The macro now been inserted into the following sub-routine so that it is triggered when cell K1 is equal to 0 (zero)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Range("K1")) Is Nothing Then
If Target.Value = 0 Then
ActiveSheet.Shapes("Picture 1").Select
Selection.ShapeRange.ZOrder msoSendToBack
End If
End If
End Sub

Cell K1 gets its value from cell M1. When I change cell M1 to 0 (zero) cell K1 also changes to 0 (zero). However picture 1 does not move to back until cell K1 is selected. I have tried including select cell K1 at the end of the original macro but it still requires that the focus is moved from cell K1 and then back to cell K1 for the chage of pictures to occur. It is this final action that I am trying to eliminate so that picture 1 moves to back once K1 = 0 with no further input or actions from me.

I am using Excel 2003 on windows XP.

Any help would be most gratefully recieved

Sivadnitram

[GTO]

Greetings,

Instead of using the selection change event, which runs when you select another cell or range, let's use the change event, which runs anytime any cell changes value.

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveSheet.Range("K1").Value = 0 Then
ActiveSheet.Shapes("Picture 1").ZOrder msoSendToBack
End If
End Sub


Also - you'll see that you don't need to select the shape, just reference it.

REgards,

Mark

[sivadnitram]

Dear Mark,
thank you very much for your guidence. It works exactly how I want. I,m new to excel but hope to crack this VBA maze at some time.

Regards and thanks again.

Martin

[GTO]

Howdy back Martin,

Very happy to be of help.

I am still amazed at all what is truly possible thru effective app included commands (ie-vba), complex formulas, as well as the knowledge and patience of those who attend this forum. While you will no doubt sufffer the frustration of folks at work 'goobering' up your creations, at the same time, the challenge of creating stuff that helps others (while remaining 'destruct-proof') is a (IMO) mentally rewarding challenge.


Have a great day!

Mark

[sivadnitram]

Dear Mark,
thank you for your encouragement but may I call on your expertise again. As I said the last solution worked exactly as required on my test page. All cell references were for my test page. However when I incorporate the routine into my workbook I cannot get it to work (and believe me I have tried) I have been constructing a workbook for a lady friend to track her bank account and 4 credit cards for which has a sheet showing various transaction, standing orders etc. These are consolidated on one sheet which shows by monthe the credit or debit of each account. There is a sheet named FOREPIECE which is displayed when ever the workbook is opened. On this sheet there is cell L8 which shows the bank credit or debit. Its value is derived from another sheet. Cell L9 shows how much is owed on all credit cards, again the value derived from another sheet. Cell L10 is the sum of L8 and L9. What I am trying to do is when L10 =>0 (i.e when my friend is out of debt) her photograph (picture 1) looking very sad will step behind picture 2 which is a photo of her laughing. I realise this may seem mundane but I have learnt a great deal from constructing this work book especially nested function, conditional formatting and I thought this would be another way to further my abilities. It will also give her something to strive towards.

One other question (if I may) on my test sheet which as I say works great I tried to add another two pictures (3 & 4) and introduced an identical routine but with pictures 3 & 4. When I try to run my test sheet I get an AMBIGUOS name message. I cannot find anything about this in the help file. Is it somethong to do with SUB_CHANGE routine is a PRIVATE SUB. My excel Bible 2003 and my excel VBA for beginners does not make it too clear.

I thank you most sincerly in anticipation of your being able to help. I hope that sometime in the future I shall be in a position to help someone else.

Regards

Martin

[GTO]

Greetings Martin,

Here is a tiny workbook attached to demo the below, in which you should be able to use either manner to hide the unwanted pic and redisplay the desired one.

I thought to include changing visibility, as this would allow you to have the pics in different locations if this sounds interesting. Of course, as long as the pics are same shape/size/location, sending one to back of Zorder works as well. You can experiment and see which you like the best.

Option Explicit
Dim Pic_Happy As Shape, Pic_Sad As Shape
'
Private Sub Worksheet_Change(ByVal Target As Range)

Set Pic_Happy = ActiveSheet.Shapes("Pic_Happy")
Set Pic_Sad = ActiveSheet.Shapes("Pic_Sad")

If ActiveSheet.Range("L10") >= 0 Then
Pic_Happy.Visible = msoCTrue
Pic_Sad.Visible = msoFalse
Else
Pic_Happy.Visible = msoFalse
Pic_Sad.Visible = msoCTrue
End If

' If ActiveSheet.Range("L10").Value >= 0 Then
' ActiveSheet.Shapes("Picture 1").ZOrder msoSendToBack
' Else
' ActiveSheet.Shapes("Picture 2").Zorder msoSendToBack
' End If
End Sub


One other question...I tried to add another two pictures (3 & 4) and introduced an identical routine but with pictures 3 & 4. When I try to run my test sheet I get an AMBIGUOS name message... Is it somethong to do with SUB_CHANGE routine is a PRIVATE SUB

Well now... It should not have anything to do with a sub being private per se, but if you had two public subs named the same, that would be an issue. Since your friend's book has private info, probably not good to post that, but how about posting the code from the sheet module that's erroring?

Mark