Overlaying Shapeor Picture based on contents of a cell

[oam]

I need to have a Shape or Picture Overlay or Popup when the user selects Vacation in cell G3 as show below. This will give a clear indication to the time recorders that the employee is on Vacation.

Is this possible to do with a formula or a macro?
Thank you for all your help.

11892

[OG Loc]

An easier solution could be to use conditional formatting, so have all the vacation rows be a certain colour when G3 = "Vacation". Should still be clear to the user that something is different about them, and once every knows that green = vacation for example, it'll be just like having an imagine but won't been any code in the workbook.

Let me know if you try and do this and have any further questions about it!

[oam]

I originally had the sheet setup where if "Vacation" was selected then the rows would turn color but I was requested to add the word "Vacation" to the sheet to ensure the time keepers did not miss the Vacation timesheets.

I have been searching the Internet for solutions and came up with a macro (see below) that sound like is should work but I have been unable to make it work correctly. I need a macro that will add the shape or picture when the word “Vacation” is displayed and remove it when it does not.

Thanks for your help





Private Sub Worksheet_Change(ByVal Target As Range)
Dim oPic As Pictures
Me.Pictures.Visible = True
With Target
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Offset(-1, 1).Top
oPic.Left = .Offset(-1, 1).Left
Exit For
End If
Next oPic
End With
End Sub

[OG Loc]

You should be able to do what you want with a slightly simpler version of that code. Try this::


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$3" Then
If Target.Value = "Vacation" Then
ActiveSheet.Shapes("Vacation").Visible = True
Else
ActiveSheet.Shapes("Vacation").Visible = False
End If
End If
End Sub

You need to call the image Vacation, and as you can probably tell, this code makes the picture appear when cell G3 has Vacation written in it, and makes it disappear when it doesn't. You need to put this code in the sheet in question, rather than in a new module or the 'ThisWorkbook' object.

[oam]

OG Loc, thank you for your quick reply!
This code works well however, when I showed the results to the timekeepers they asked if I could get it to display "Corrected" if G3 contained corrected also. So what I need is when G3 equals "Vacation" Vacation shape is visible and "Corrected" is not visible and when G3 equals "Corrected" Corrected shape is visible and Vacation is not; hope this makes sense.
Sorry for asking to change a code that does as I asked for but I did not know the whole request at the time. They said this is the last change.

Thank you for your time

[OG Loc]

OG Loc, thank you for your quick reply!
This code works well however, when I showed the results to the timekeepers they asked if I could get it to display "Corrected" if G3 contained corrected also. So what I need is when G3 equals "Vacation" Vacation shape is visible and "Corrected" is not visible and when G3 equals "Corrected" Corrected shape is visible and Vacation is not; hope this makes sense.
Sorry for asking to change a code that does as I asked for but I did not know the whole request at the time. They said this is the last change.

Thank you for your time

A little change to the code should allow this


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$3" Then
If Target.Value = "Vacation" Then
ActiveSheet.Shapes("Vacation").Visible = True
ActiveSheet.Shapes("Corrected").Visible = False
Elseif Target.Value = "Corrected" Then
ActiveSheet.Shapes("Vacation").Visible = False
ActiveSheet.Shapes("Corrected").Visible = True
Else
ActiveSheet.Shapes("Vacation").Visible = False
ActiveSheet.Shapes("Corrected").Visible = False
End If
End If
End Sub

You'll just need to put a picture called Corrected in on top of the vacation one. Let me know how it goes.

[snb]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$3" Then
ActiveSheet.Shapes("Vacation").Visible = Target.Value = "Vacation"
ActiveSheet.Shapes("Corrected").Visible = Target.Value = "Corrected"
end if
End Sub

[oam]

You guys are the bomb!! Both codes work Great!!

Thank you so much.