Macro to Change Visibility of GIF Based on Contents of A Cell Containing VLOOKUP

[LilRed94]

Hello,

I'd like to start this off by saying I know basically nothing about VBA and Macros. I'm trying to learn more during down time for work, but I don't have a lot of that.

A coworker of mine has built a calculator in Excel using many functions and drop downs. One of the functions being used is a VLOOKUP function that, based on a drop down, is returning a cell that either has a single space in it so it appears blank, or a string of text that should act as a warning to a user of the calculator. There is conditional formatting on the cell to highlight it in yellow when that warning is returned, but we would like it to be more eye catching, so we have found a GIF that we would like to have pop up when the warning pops up. We have managed to come up with this string of code;

Sub Main()
    If Range(N16) = " " Then
    ActiveSheet.Pictures("Picture 4").Visible = False
    ActiveSheet.Pictures("Picture 4").Visible = True
    End If
End Sub

However when trying to run it, it returns an error that reads "Run-time error 1004: Application-defined or object-defined error"
I've tried searching the internet for what that means and how to fix it, but have not had any luck. Does anybody have an idea of how to make this work?

Thanks in advance!

 

[Micron]

It's a vague message that generally means you're trying to reference something but the reference is invalid. You don't say which line is causing the error. Could be the Range reference? If you don't know, put a break point on the first line and cause the code to run, the step (F8) through until you raise the error.

 

[LilRed94]

It's a vague message that generally means you're trying to reference something but the reference is invalid. You don't say which line is causing the error. Could be the Range reference? If you don't know, put a break point on the first line and cause the code to run, the step (F8) through until you raise the error.

When I step through the code, it highlights the first and then second lines, then the error pops up when I try to move to the third line. Maybe I'm referencing the image incorrectly?

 

[Micron]

That would be my guess. Do that again, but when the 1st line is highlighted, type
?ActiveSheet.Pictures("Picture 4").Name and hit Enter.

If it returns a name, make sure it is exactly the same as in your code. From what I'm seeing in Excel vba, case matters in some places, so make sure of that too. Your syntax worked for me, as did activesheet.shapes(3).name (3 was the index number of my image). You could also type

?ActiveSheet.Name when the code is paused and hit return to verify that the active sheet is the one you expect.
All of this assumes you didn't put the image in an image control.

 

[LilRed94]

That would be my guess. Do that again, but when the 1st line is highlighted, type
?ActiveSheet.Pictures("Picture 4").Name and hit Enter.

If it returns a name, make sure it is exactly the same as in your code. From what I'm seeing in Excel vba, case matters in some places, so make sure of that too. Your syntax worked for me, as did activesheet.shapes(3).name (3 was the index number of my image). You could also type

?ActiveSheet.Name when the code is paused and hit return to verify that the active sheet is the one you expect.
All of this assumes you didn't put the image in an image control.

It did return "Picture 4", so I copied and pasted that into my code to make sure it was exactly the same, and still getting the same error.
Would the fact that it's a GIF instead of a static image be causing an issue?
I checked the active sheet as well, and it is the one that I expect.
I'm not sure what an image control is, but based on a brief Google search, it might be in an image control since it is a GIF

 

[Micron]

Good point, I tried it with a static image. IIRC, animated gifs are not compatible in Access, so perhaps not in Excel either. It's something you can lookup. As for me, just a quick reply as I've got to go out so I leave it up to you.

 

[LilRed94]

Good point, I tried it with a static image. IIRC, animated gifs are not compatible in Access, so perhaps not in Excel either. It's something you can lookup. As for me, just a quick reply as I've got to go out so I leave it up to you.

I'm able to import the GIF into Excel fine, and it plays, so it seems to be compatible. I tried making another test spreadsheet and doing the same code with a static image (verified the name of the image) and I still got the same error.
However, I think I discovered that I may have been putting the code in the wrong place? I was putting the code in the sheet I want it to happen in under the Microsoft Excel Objects. I tried putting it into a Module instead, and now the wording of my error is different - "Method 'Range' of object '_Global' failed"

 

[LilRed94]

I have figured this out - I needed to put my cell location in quotations.
I also put an "else" between the two lines about making the image visibility true or false and made it so the macro would run whenever a certain cell was changed, so the GIF appears and disappears along with the warning.
This is what my final code wound up being;

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("B16").Address Then
         If Range("N16") = " " Then
         ActiveSheet.Pictures("Picture 2").Visible = False
        Else
        ActiveSheet.Pictures("Picture 2").Visible = True
        End If
    End If
End Sub

Thank you very much for all your help, Micron!

 

[Micron]

Glad to see you figured it out.