A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Insert Picture from dropdown selection



 
 
Thread Tools Display Modes
  #11  
Old March 1st, 2006, 10:40 AM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Insert Picture from dropdown selection


Hello,

I tried the method on the McGimpsey site, but it doesn't seem to work
in my case. Well, it does exactly what it is supposed to do I guess,
but I have other graphics on my sheet (another picture=a logo that
should stay fixed and a bunch of Combo boxes). Isn't there a way to
define a list of the photo's that should be hidden, rather then
everything on the page? The problem is that the "Me.Pictures.Visible =
False" command hides everything, including what should remain visible.
Help would be very much appreciated.

Kind regards,


--
Jufa
------------------------------------------------------------------------
Jufa's Profile: http://www.excelforum.com/member.php...o&userid=30742
View this thread: http://www.excelforum.com/showthread...hreadid=513797

  #12  
Old March 2nd, 2006, 03:34 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Insert Picture from dropdown selection


Hello Jufa

As I mentioned above, I'm not an expert in VBA but if you add a line to
the code shown on the McGimpsey site you can retain your logo.

Try this:

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
ActiveSheet.Shapes("Picture 8").Visible = True
With Range("F1")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub

Notice the added line: ActiveSheet.Shapes("Picture 8").Visible = True
(You could add additional lines right after it for other pictures you
want retained)

This will keep Picture 8 visible at all times so add that line to your
code and use the name of your logo instead of Picture 8


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=513797

  #13  
Old March 7th, 2006, 03:14 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Insert Picture from dropdown selection


Hey Cutter,
Thanks for your input. That should safeguard my logo, but I'm not too
sure about the dropdown boxes. They dissapear too.
However, I see other use for you line of code. it seems that if I use
your line, but define all pictures that can be triggered as a result of
my dropdown as ActiveSheet.Shapes("Picture 8").Visible = False and omit
the Me.Pictures.Visible = False line, I might get where I want to be.

Don't have time to test it right now. What I'm trying to do is actually
an "in between project". The basics are finished, but the fine-tuning is
for when my main monthly recurrent projects are finished. I just wanted
to say thanks right away though. I'll be in touch later to inform you
whether it worked, or ask more questions

So thanks and see you later.


--
Jufa
------------------------------------------------------------------------
Jufa's Profile: http://www.excelforum.com/member.php...o&userid=30742
View this thread: http://www.excelforum.com/showthread...hreadid=513797

  #14  
Old March 10th, 2006, 05:01 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Insert Picture from dropdown selection


Hello all,

I used cutter's code-line to hide all pictures I do not want to see (13
flags, picture 25 through 37). However, when I select an item now in the
drop down list, I get a "Run-time error '13' Type mismatch. When I open
the debugger, the arrow points to "For Each oPic In Me.Pictures" Can
someone please have a look at my code and see what's wrong? I would
LOVE to get this thing working. I left out the line
"Me.Pictures.Visible = True" at the beginning as I see no use for it
since I define all pictures NOT to show. Many thanks in advance!

Private Sub Worksheet_Calculate()
Dim oPic As Picture
ActiveSheet.Shapes("Picture 25").Visible = False
ActiveSheet.Shapes("Picture 26").Visible = False
ActiveSheet.Shapes("Picture 27").Visible = False
ActiveSheet.Shapes("Picture 28").Visible = False
ActiveSheet.Shapes("Picture 29").Visible = False
ActiveSheet.Shapes("Picture 30").Visible = False
ActiveSheet.Shapes("Picture 31").Visible = False
ActiveSheet.Shapes("Picture 32").Visible = False
ActiveSheet.Shapes("Picture 33").Visible = False
ActiveSheet.Shapes("Picture 34").Visible = False
ActiveSheet.Shapes("Picture 35").Visible = False
ActiveSheet.Shapes("Picture 36").Visible = False
ActiveSheet.Shapes("Picture 37").Visible = False
With Range("E3")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub


--
Jufa
------------------------------------------------------------------------
Jufa's Profile: http://www.excelforum.com/member.php...o&userid=30742
View this thread: http://www.excelforum.com/showthread...hreadid=513797

  #15  
Old March 11th, 2006, 11:23 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Insert Picture from dropdown selection


Hi Jufa

To use this method you have to define the pictures that you want to
show, not the ones that you want to hide. If you have too many then
hopefully one of the VBA experts will jump in with an alternative
method.

The line you took out is needed because the line that is now causing
the error refers to it. And note that the line you took out is:
Me.Pictures.Visible = False (Not =True) as you stated in your last post


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=513797

  #16  
Old March 13th, 2006, 04:21 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Insert Picture from dropdown selection


Hey Cutter,

I'm now using exactly your code (except for the cell reference which I
adjusted to my needs), but it keeps giving the error on the line "For
Each oPic In Me.pictures". I have no clue of what I'm doing wrong. When
I apply the same addition of code (ActiveSheet.Shapes("Picture
1").Visible = True) it works fine, it's just that in my sheet, the
error keeps popping up.

Can the problem be, that it says "For Each oPic in Me.Pictures" while
some pictures seem to be missing. For example. The pictures that are in
my look-up table are pictures 25-37. The numbers before that refer to
the logo and drop down boxes I guess. However, when I define those
first 24, I'm told that he doesn't recognise some of them and I have to
remove those numbers. I think this is due to the fact that while
originally setting up my sheet I deleted some pictures/drop down boxes
and added them again. Excel however, just keeps taking the next number
instead of recycling the nrs of the pics that were deleted. Can this be
causing my conflict and how can I solve this??

Kind regards,


--
Jufa
------------------------------------------------------------------------
Jufa's Profile: http://www.excelforum.com/member.php...o&userid=30742
View this thread: http://www.excelforum.com/showthread...hreadid=513797

  #17  
Old March 13th, 2006, 05:37 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Insert Picture from dropdown selection


Let me see the code you're using. Keep in mind that I'm not an expert
but I'll see if I can help.


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=513797

  #18  
Old March 15th, 2006, 02:27 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Insert Picture from dropdown selection


Hey Cutter,
First of all, thanks for all your help. I was using a regular combobox
like you mention, but maybe it's something in the settings (I use
placement 1) that identifies them like pictures?
Anyways, I started over and used data validation now instead of the
drop boxes and now it's working just fine. It looks a bit less
professional in my opinion, but it will have to do. It has been
distributed. I'm a financial analyst in the first place and I couldn't
keep spending time in cosmetics. I think somehow something in my
original sheet got messed up and the code SHOULD be working fine, but
it just isn't. Maybe for a learning proces I'll post it. This is the
most simple form, asking to keep the logo visible. I would have to add
a few more to keep the drop boxes. Again, thanks for your efforts. Best
of luck. I will still be checking out your comments though. I hate it
when things don't work the way I want them too and I might distribute
an update later It's the line "For Each oPic..." that seems to block
things.

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
ActiveSheet.Shapes("Picture 7").Visible = True
With Range("F3")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub


--
Jufa
------------------------------------------------------------------------
Jufa's Profile: http://www.excelforum.com/member.php...o&userid=30742
View this thread: http://www.excelforum.com/showthread...hreadid=513797

  #19  
Old March 21st, 2006, 04:01 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Insert Picture from dropdown selection

Hi Cutter is it possible to do this the other way around and have the
pictures themselves be in the drop down? I have many parts to choose from and
a person in the field probably won't know what the part number is but they
will be able to look at the part. It would be great if they were able to pick
it out from a list of pictures and then have the part number displayed in a
cell next to it.

Sorry to jump in like this but I haven't been able to get any response on
whether or not this is possible and whom to talk to. ANY help would be much
appreciated!

Thanks in advance.

"Cutter" wrote:


Let me see the code you're using. Keep in mind that I'm not an expert
but I'll see if I can help.


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=513797


  #20  
Old March 21st, 2006, 04:40 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Insert Picture from dropdown selection


Jono

I doubt that it's possible. I've never heard of it being done (or even
heard of anyone asking for it until now). But you'd have to hear from
the VBA experts for a definite answer.

Cutter


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=513797

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Setting up protected document that can insert picture (photo ID template) Craig Page Layout 5 July 13th, 2007 01:15 PM
insert picture to a specfic range of cells CORY General Discussion 8 February 3rd, 2006 06:50 PM
Excel 2003 - insert picture - Not Responding trinity Setting up and Configuration 4 October 17th, 2005 12:46 PM
Making a frame to insert a picture Jean Powerpoint 2 September 11th, 2005 11:47 PM
Insert picture with VBA wschiro Powerpoint 2 April 25th, 2005 06:14 AM


All times are GMT +1. The time now is 04:18 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.