G
Guest
Can I include a picture in a formula i.e. if cell a26 >90% then show picture
x, if less then show picture y?...
x, if less then show picture y?...
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Dave Peterson
Maybe you could do something like JE McGimpsey does at:
http://www.mcgimpsey.com/excel/lookuppics.html
http://www.mcgimpsey.com/excel/lookuppics.html
G
Gord Dibben
PACF
Not a formula, but using worksheet event code.
See JE McGimpsey's site for code and instructions.
http://www.mcgimpsey.com/excel/lookuppics.html
Gord Dibben Excel MVP
Not a formula, but using worksheet event code.
See JE McGimpsey's site for code and instructions.
http://www.mcgimpsey.com/excel/lookuppics.html
Gord Dibben Excel MVP
G
Guest
Many thanks - just what I needxed.
Paul
Paul
Gord Dibben said:PACF
Not a formula, but using worksheet event code.
See JE McGimpsey's site for code and instructions.
http://www.mcgimpsey.com/excel/lookuppics.html
Gord Dibben Excel MVP
G
Guest
Many thanks - just what I'm llooking for.
Paul
Paul
Dave Peterson said:Maybe you could do something like JE McGimpsey does at:
http://www.mcgimpsey.com/excel/lookuppics.html
G
Guest
I can't figure out how to insert the pictures? HELP. I can modify the
PicTable on sheet 2 to expand the name range but I haven't been successful in
loading the pictures.
PicTable on sheet 2 to expand the name range but I haven't been successful in
loading the pictures.
D
Dave Peterson
JE's routine assumes that the pictures are already on the worksheet.
So load your pictures manually (or anyway you know), then use JE's code to
hide/show the ones you want.
So load your pictures manually (or anyway you know), then use JE's code to
hide/show the ones you want.
D
Dave Peterson
Maybe you can just check the name and then do nothing for that name:
Option Explicit
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("F1")
For Each oPic In Me.Pictures
If oPic.Name = "Nameofpicturethatshouldnotchange" Then
'do nothing
Else
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
End If
Next oPic
End With
End Sub
Option Explicit
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("F1")
For Each oPic In Me.Pictures
If oPic.Name = "Nameofpicturethatshouldnotchange" Then
'do nothing
Else
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
End If
Next oPic
End With
End Sub
G
Guest
Hi Dave,
I tried the following as below, but its ends up with a compile error.
Thanks for trying.
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("E30")
For Each oPic In Me.Pictures
If oPic.Name = "Picture 1" Then
'do nothing
Else
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub
I tried the following as below, but its ends up with a compile error.
Thanks for trying.
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("E30")
For Each oPic In Me.Pictures
If oPic.Name = "Picture 1" Then
'do nothing
Else
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub
D
Dave Peterson
You dropped an "End If" line near the bottom.
I'm not sure if your code is indented (or if just the message was not), but
indenting can make finding errors like this easier to find. And it would help
others if you indicated the line that was marked as an error and what that error
is, too.
Option Explicit
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("E30")
For Each oPic In Me.Pictures
If oPic.Name = "Picture 1" Then
'do nothing
Else
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
End If '<--- Added
Next oPic
End With
End Sub
I'm not sure if your code is indented (or if just the message was not), but
indenting can make finding errors like this easier to find. And it would help
others if you indicated the line that was marked as an error and what that error
is, too.
Option Explicit
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("E30")
For Each oPic In Me.Pictures
If oPic.Name = "Picture 1" Then
'do nothing
Else
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
End If '<--- Added
Next oPic
End With
End Sub
Hi Dave,
I tried the following as below, but its ends up with a compile error.
Thanks for trying.
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("E30")
For Each oPic In Me.Pictures
If oPic.Name = "Picture 1" Then
'do nothing
Else
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub
G
Guest
Hi Dave,
Thank you, sorry about that, I don't dive into the VB editor very often so
it doesn't stick in the grey cells.
Thanks again for your help and quick response.
Thank you, sorry about that, I don't dive into the VB editor very often so
it doesn't stick in the grey cells.
Thanks again for your help and quick response.
D
Dave Peterson
You can change the fill color for those grey cells.
Select that range
format|cells|Pattern tab|Choose no color
<vvbg>
Select that range
format|cells|Pattern tab|Choose no color
<vvbg>
Hi Dave,
Thank you, sorry about that, I don't dive into the VB editor very often so
it doesn't stick in the grey cells.
Thanks again for your help and quick response.
C
chrstrcy
Dave Peterson said:Maybe you can just check the name and then do nothing for that name:
Option Explicit
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("F1")
For Each oPic In Me.Pictures
If oPic.Name = "Nameofpicturethatshouldnotchange" Then
'do nothing
Else
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
End If
Next oPic
End With
End Sub
C
chrstrcy
I am trying to do the same thing as the other gentleman but apparently I am
missing something. I have entered what you have below but my logo "Picture
1" keeps disappearing everytime I use the other event (I have it inserting a
signature upon a person's name).
***Please help!
missing something. I have entered what you have below but my logo "Picture
1" keeps disappearing everytime I use the other event (I have it inserting a
signature upon a person's name).
***Please help!
D
Dave Peterson
Did you change the name of the picture in the code?
I am trying to do the same thing as the other gentleman but apparently I am
missing something. I have entered what you have below but my logo "Picture
1" keeps disappearing everytime I use the other event (I have it inserting a
signature upon a person's name).
***Please help!
C
chrstrcy
Yes, I changed the code to read "Picture 1" and then I inserted the picture
where I needed it in the file and named it Picture 1. But then when I test
by changed the signature event it deletes the picture 1.
where I needed it in the file and named it Picture 1. But then when I test
by changed the signature event it deletes the picture 1.
C
chrstrcy
Here is my code:
Option Explicit
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("A66")
For Each oPic In Me.Pictures
If oPic.Name = "Picture 1" Then
'do nothing
Else
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
End If '
Next oPic
End With
End Sub
Option Explicit
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("A66")
For Each oPic In Me.Pictures
If oPic.Name = "Picture 1" Then
'do nothing
Else
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
End If '
Next oPic
End With
End Sub
chrstrcy said:Yes, I changed the code to read "Picture 1" and then I inserted the picture
where I needed it in the file and named it Picture 1. But then when I test
by changed the signature event it deletes the picture 1.
D
Dave Peterson
So you want to see "Picture 1" all the time?
Option Explicit
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False 'this hides all the pictures
With me.Range("A66")
For Each oPic In Me.Pictures
If lcase(oPic.Name) = lcase("Picture 1") Then
'show it
oPic.visible = true
Else
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
End If '
Next oPic
End With
End Sub
Option Explicit
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False 'this hides all the pictures
With me.Range("A66")
For Each oPic In Me.Pictures
If lcase(oPic.Name) = lcase("Picture 1") Then
'show it
oPic.visible = true
Else
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
End If '
Next oPic
End With
End Sub
C
chrstrcy
Yes, and I want it to show in cell A1 - does this need to be noted in the
code as well?? I will try what you have below. Thank you for your help.
code as well?? I will try what you have below. Thank you for your help.
D
Dave Peterson
Nope. The code will use the name--not the location.
Did it work when you tried it?
Did it work when you tried it?
Yes, and I want it to show in cell A1 - does this need to be noted in the
code as well?? I will try what you have below. Thank you for your help.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.