Solved

Googlemaps into userform possible ?

Posted on 2011-02-23
57
665 Views
Last Modified: 2012-06-21
Hi all,

Is is possible to add a location map into an userform ? I know for sure that an image can be added to an userform, but how about a location map like googlemaps ? As the userform contain addresses for specific locations, maps of these location would be added. Then a click on the map would lead to google maps to view the entire map in detail.

Thanks for any help you can provide.
Cheers
currentdb
0
Comment
Question by:currentdb
  • 28
  • 21
  • 4
  • +2
57 Comments
 
LVL 33

Expert Comment

by:jppinto
Comment Utility
I think that you're trying to go way beyond the natural use of Excel and are trying to do things for what Excel wasn't designed to do! I don't think that it's possible to do this, or at least, it's not possible without lots of external programming like the use of Visual Basic .Net, and even that way, I find it hard to do...
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
I'm just trying to push Excel to its limits, see what he's capable of :)

There's just one thing with an userform: you can insert any kind of image. Maybe MS forgot to insert more fonctions to this category ?

Assuming that's not possible, the other alternative would be to add a link on this userform....but I don't see anything where I can add this link...:(
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 180 total points
Comment Utility
I have done this on an Access form.  You could try modifying the code and see if you can get it to work on a UserForm.  That would be an interesting experiment.  See my Access Archon #193 -- here is a link for downloading it:
http://www.helenfeddema.com/Files/accarch193.zip

and here is a screen shot of the form:
Google-Map-on-Access-form.jpg
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
What link do you want to shown on the userform?

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Helen: That is simply beautiful...

Sid
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Hello Helen,

I already heard that it's possible to do this by using Access and thank you for the link.

Indeed, it's a very interesting experiment and I think I'm the only one with such question :)

I tried everything and I don't see how to solve this. Of course Access it's much more easier than Excel and Microsoft should really improve a lot more these Excel features & add-ins.

As I already tried and failed to insert a clickable picture on an userform, there is another alternative...maybe the last one.

What about this: picture will be replaced with a button. This button, on a click, will launch GoogleMaps. But from there, I don't see how the address will appear as I'm sure I have to enter it manually...
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Hello Sid,

>What link do you want to shown on the userform?
The link will just open google maps with the map location. But to add a map location, we need to match address (number, city, province, postal code). And everything it's in a different field.  
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
As I already tried and failed to insert a clickable picture on an userform,

It is easy :) Give me the link where it should take you once the user clicks on the picture.

Sid
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
It is easy ? lol

Here's the link: GoogleMaps
But that's just the normal link. Linking it to the other coordinates it's much more complex than easy :)
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Yes it is easy if you have a link ready. Calculating the link is something else. :)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
For example if you have something like this ready

http://maps.google.co.in/maps?f=d&source=s_d&saddr=Mumbai,+Maharashtra&daddr=&hl=en&geocode=FY8vIgEdZLJXBA&mra=prev&sll=19.020151,72.853823&sspn=0.001265,0.002411&ie=UTF8&z=16

Then I can make open IE and go to that link when the user clicks on a picture.

Sid
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Yes I'm aware that calculating the link is something else :)
Let me know if you want me to upload a sample. It's basically the same userform we worked on for a while.
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Ok do you want help with the code for opening IE and navigating to a 'link' when the user clicks on a picture

OR

Calculation of the link?

Sid
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
The link you submitted at ID 34964047 is something, but how will you figure that it will lead to India ? Because each record do have an address and most adresses are in Canada.

I believe ''Calculation of the link'' is the appropiate answer because the user will click on the link which will opens IE and show him the location...but this one is really impossible.

Let me re-post a sample of the workseet...
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
There's the sample xlsm.

If you type in, let's say record number 1220490, the address associated with the record appears. It would be hard to associate it with GoogleMaps

currentdb-v5.xlsm
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
The challenge is that the address is incomplete. When I paste "216 dillon sydney NF" in GM, I get nothing.

Sid
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
You need to enter the postal code, if not it will not show anything.

Try this, 216 DILLON, SYDNEY, NS, B1P 5C3 and you will see it for sure :)
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Where the &sll=38.479186,-90.746622&sspn=34.115555,79.013672 come from ? These come from the googlemaps link ?

Relevant values, do you mean different values than the ones we have ?
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
>> These come from the googlemaps link ?

Yes.

>> Relevant values, do you mean different values than the ones we have ?

No. But in addition to the ones that we have. for example, in the above link, the link starts with

http://maps.google.co.in/maps?f=q&source=s_q&hl=en&geocode=&q=

then we have

216+DILLON,+SYDNEY,+NS,+B1P+5C3 for 1st link
198+MOUNT+PLEASANT+STREET+SYDNEY+NF+B1N2G6 for 2nd link

We have those values... so the link now becomes

http://maps.google.co.in/maps?f=q&source=s_q&hl=en&geocode=&q=[VALUES]

Then comes the next part which is

&aq=&sll=

which again is a constant so the above becomes

http://maps.google.co.in/maps?f=q&source=s_q&hl=en&geocode=&q=[VALUES WHICH WE HAVE]&aq=&sll=

Then the next part is

38.479186,-90.746622 for the 1st link and
46.134624,-60.185477 for the 2nd link

so the above link now becomes

http://maps.google.co.in/maps?f=q&source=s_q&hl=en&geocode=&q=[VALUES WHICH WE HAVE]&aq=&sll=[VALUES WHICH WE DONT HAVE]

and so on...

Sid
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
I understand. I don't know another alternative...maybe just insert a "normal" link in the picture that will open IE directly into GM and users will enter manually the coordinates. If you have a better idea, it's ok.
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
>> just insert a "normal" link in the picture that will open IE

That I can do. Would that help?

Sid
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Sid,
Yes that would help. It's better than nothing. Thanks :)
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Paste this code in userform code area.

Private Sub Image1_Click()
    Dim strLink As String
    strLink = "http://maps.google.co.in/maps?f=q&source=s_q&hl=en&geocode=&q=216+DILLON,+SYDNEY,+NS,+B1P+5C3&aq=&sll=38.479186,-90.746622&sspn=34.115555,79.013672&ie=UTF8&hq=&hnear=216+Dillon+St,+Cape+Breton,+Cape+Breton+County,+Nova+Scotia+B1P+5C3,+Canada&z=16"
    On Error GoTo ErrIE
    
    Set browser = CreateObject("InternetExplorer.Application")
    browser.Visible = True
    browser.Navigate strLink
    Exit Sub
ErrIE:
    MsgBox Err.Description
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Hi Sid,

I forgot to copy the xlsm file onto my usb disk and now I'm just back home. Hope you don't mind waiting until tomorrow.
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Sure no probs ;)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
BTW if you have internet at home you can download it from above :)

Sid
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Perfect. Thanks for your understanding.

I can download the sample I uploaded earlier, but the only problem is that at home I use only Excel 2003 while at work it's running on 2007. I forgot to save the sample file so it can run on both versions. Just saved it to run on 2007... :(

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Do you want me to save a copy for you and upload it?

Sid
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
If you have time of course, but it's not the end of the world :)
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Here it is

Sid
currentdb-v5.xls
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Downloaded it thanks :)

Now I'll incorporate the code you provided.
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
I have already done that ;)

Sid
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Looks like you already integrated the code cause I was just to, but saw it was already there :)

So it works as you mentioned. However, it's normal that the mouse pointer does not change once you hover it on the picture ? At first, I was not sure that the picture even contained a link. Usually the mouse pointer changes into a hand meaning that this is a link or the whole image border is in another colour.
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Gimme 10 mins... I will get that arranged. I am on a call right now.

Sid
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
No problem take your time. I gotta eat something cause I'm very hungry. I'll be back in abt 30 min.
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 214 total points
Comment Utility
Based on Helen's sample, something like this should work.
currentdb-v5.xlsm
0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 106 total points
Comment Utility
Here it is

Sid
currentdb-v5.xls
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
I'm back and just finished to eat :)

And I reviewed the last file you submitted. It's just perfect! :)

Now that everything works, I will award full points to you, but I see that Helen contributed for a small part. I don't know if you want to give her some points. It's up to you :)
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
I have no issues in you splitting points ;)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Have you checked Rory's file?

Sid
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Which file is from Rory ?
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Oh nooooo, I wanted to assign points to you. Did not see that the one based on Helen's sample was from Rory :(
I will alert mods to re-split points accordingly.
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Ha ha ha ... You forgot to assign points to me after all... lolzzz

Sid
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
On the Access form I have a Web Browser control -- that is what displays the map.  Can you put this control on the userform?  See the Form events for the code that sets the map to display a specific location.
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
I guess I was already very tired and that's why I told you it was better to wait till tomorrow. Anyway I have to search where to alert these mods to change back points equally.
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Hello Helen, we (me and Sid) tried, but it seems quite impossible from this point of view. Excel is just good for calculations and everything while Access is most suitable to manage databases. If you take a look at the userform, you will notice that the address is somewhat scattered between 4 different fields (property number, street name, city, province and of course postal code). If these 4 fields were combined into one single field, it would possibly work. But as separate fields, it's not possible. If it's possible, I would be happy to know how :)
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 214 total points
Comment Utility
The file I posted uses Helen's method. I only had to tweak the code slightly for the different controls and to add some commas to the search string, so most of the credit is hers really!
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
That's why I asked mods to re-open question so I can assign points equitably. No news yet. Opened 2 questions in the General Community Zone and no one replied yet :(
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Did you use the Request Attention button at the top of this thread?
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Yes. Actually this request is in ''pending'' mode. I wonder how much time it will take to change from ''pending'' to ''reviewed'' (or any other option).
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
It may be that they are busy at the moment. I'm sure it will get sorted out in due course.
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Of course. It would be better to have this privilege to undo something like this. I don't know if EE will implement some user fonction where an user can undo its action (giving & spliting) points back to where it was but it will only be available for a very short period of time, maybe 15-30 min and if going over this period of time, mods will handle it. I'm sure such fonction will make them less busy so they can concentrate their efforts on other matters.
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Vee_Mod,

Thank you for your help in re-opening this question. Wasting time sleeping, eating, drinking beer ? ahahahahha. I can understand that from time to time you are surrounded with requests from everywhere.

BTW can you consider my point of view, the one at post ID 34969691 ? I think this way you will be less busy and of course, you can have more sleep :)
0
 
LVL 1

Author Closing Comment

by:currentdb
Comment Utility
Thanks everyone for your help and of course Helen's exemple is beautiful. Hope to see you all again around for another questions! :)
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now