lookup for a picture (map)

peter_wcx · Mar 14th, 2002, 03:28 PM

I need to pull in a map for a particular physical address. I have about seven maps to clinics. When I put in a store location number in one cell, I'd like for the corresponding map to appear on the page in a defined range.

Jack in the UK · Mar 14th, 2002, 03:32 PM

GP mean much?

If so ...... care to say!

We can make this easy... Im GP if you get me...

dk · Mar 14th, 2002, 04:35 PM

Hi Jack,

What's GP mean?

If you mean that the question is almost impossible to understand without more info then I agree with you!

Quote:

I need to pull in a map for a particular physical address. I have about seven maps to clinics. When I put in a store location number in one cell, I'd like for the corresponding map to appear on the page in a defined range.

What's the 'physical address'? What are the maps to clinics? I think I understand what you want to do (and I think it will require VBA) but without more info it's very difficult to understand what you're after.

Regards,
D

peter_wcx · Mar 14th, 2002, 05:06 PM

I will gladly send the file if needed. On worksheet 1, I have a cell where the numerial store number is entered. I use "vlookup" to retrieve the address of that particular store from a database in worksheet 2. In worksheet 3, I have individual maps to each location pasted. It is these maps that I would like to appear on sheet 1 along with the store address.

Quote:

On 2002-03-14 15:35, dk wrote:
Hi Jack,

What's GP mean?

If you mean that the question is almost impossible to understand without more info then I agree with you!

Quote:

I need to pull in a map for a particular physical address. I have about seven maps to clinics. When I put in a store location number in one cell, I'd like for the corresponding map to appear on the page in a defined range.

What's the 'physical address'? What are the maps to clinics? I think I understand what you want to do (and I think it will require VBA) but without more info it's very difficult to understand what you're after.

Regards,
D

Jack in the UK · Mar 14th, 2002, 05:13 PM

dk hi mate

GP is British Govenment linked to General Practice ... Doctors.. Hospitals and ... so on...

He wont tell us much if so so i asked well i can help off the board on that one see...

BUT

I would as i still say in right... split the maps into sheets and one each vlookup address fax tel email NHSNet and so on and then hyperlink on tagget to that sheet in that region ...... and command button back to switchboard...

This i cant say i have done or would do but ... its possible and just an idea....

multi select mapping is hard work for what i feel is required.. maybe in wrong..

Derek · Mar 15th, 2002, 01:20 AM

Hi peter_wcx

Give this a whirl:

Put a macro button on Worksheet 1 with title "Click for Map"
Paste this code into a module assigned to this macro button:

On Error Resume Next
Application.ScreenUpdating = False
Sheets("Sheet3").Select
Answer = InputBox("Insert number of map required" & vbCrLf & "1. Clinic A" & vbCrLf & "2. Clinic B" & vbCrLf & "3. Clinic C" & vbCrLf & "4. Clinic D" & vbCrLf & "5. Clinic E")
If Answer = 1 Then Range("A1").Copy
If Answer = 2 Then Range("A2").Copy
If Answer = 3 Then Range("A3").Copy
If Answer = 4 Then Range("A4").Copy
If Answer = 5 Then Range("A5").Copy
Sheets("Sheet1").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Comment.Visible = True
End Sub

In Worksheet 3
Put a comment in the 5 cells A1:A5
Edit each comment, as follows
Right click the border
Format comment
tab Color and Lines
open dropdown box for color
Fill Effects…
Picture
Select picture of your map
Click insert and Okay
Stretch your picture comment window to an appropriate size

Click macro button in Worksheet 1
Input box opens
Enter No of map
Hey Presto !

To print map-comment on Sheet1,
go to Page Set Up
tab for Sheet
under Print, comments box
select "As displayed on sheet"
click okay

Have fun
regards
Derek

[ This Message was edited by: Derek on 2002-03-15 00:39 ]

Mar 14th, 2002, 03:32 PM	#2
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,064	GP mean much? If so ...... care to say! We can make this easy... Im GP if you get me... __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Mar 14th, 2002, 05:13 PM	#5
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,064	dk hi mate GP is British Govenment linked to General Practice ... Doctors.. Hospitals and ... so on... He wont tell us much if so so i asked well i can help off the board on that one see... BUT I would as i still say in right... split the maps into sheets and one each vlookup address fax tel email NHSNet and so on and then hyperlink on tagget to that sheet in that region ...... and command button back to switchboard... This i cant say i have done or would do but ... its possible and just an idea.... multi select mapping is hard work for what i feel is required.. maybe in wrong.. __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 14th, 2002, 03:28 PM	#1
peter_wcx New Member Join Date: Mar 2002 Posts: 22	I need to pull in a map for a particular physical address. I have about seven maps to clinics. When I put in a store location number in one cell, I'd like for the corresponding map to appear on the page in a defined range.

Mar 15th, 2002, 01:20 AM	#6
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Hi peter_wcx Give this a whirl: Put a macro button on Worksheet 1 with title "Click for Map" Paste this code into a module assigned to this macro button: On Error Resume Next Application.ScreenUpdating = False Sheets("Sheet3").Select Answer = InputBox("Insert number of map required" & vbCrLf & "1. Clinic A" & vbCrLf & "2. Clinic B" & vbCrLf & "3. Clinic C" & vbCrLf & "4. Clinic D" & vbCrLf & "5. Clinic E") If Answer = 1 Then Range("A1").Copy If Answer = 2 Then Range("A2").Copy If Answer = 3 Then Range("A3").Copy If Answer = 4 Then Range("A4").Copy If Answer = 5 Then Range("A5").Copy Sheets("Sheet1").Select Range("D4").Select Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False ActiveCell.Comment.Visible = True End Sub In Worksheet 3 Put a comment in the 5 cells A1:A5 Edit each comment, as follows Right click the border Format comment tab Color and Lines open dropdown box for color Fill Effects… Picture Select picture of your map Click insert and Okay Stretch your picture comment window to an appropriate size Click macro button in Worksheet 1 Input box opens Enter No of map Hey Presto ! To print map-comment on Sheet1, go to Page Set Up tab for Sheet under Print, comments box select "As displayed on sheet" click okay Have fun regards Derek [ This Message was edited by: Derek on 2002-03-15 00:39 ]