Googlemaps into userform possible ?

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
LVL 1
currentdbAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Helen FeddemaConnect With a Mentor Commented:
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
 
jppintoCommented:
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
 
currentdbAuthor Commented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
SiddharthRoutCommented:
What link do you want to shown on the userform?

Sid
0
 
SiddharthRoutCommented:
Helen: That is simply beautiful...

Sid
0
 
currentdbAuthor Commented:
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
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
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
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
Yes it is easy if you have a link ready. Calculating the link is something else. :)

Sid
0
 
SiddharthRoutCommented:
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
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
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
 
currentdbAuthor Commented:
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
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
The challenge is that the address is incomplete. When I paste "216 dillon sydney NF" in GM, I get nothing.

Sid
0
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
0
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
>> 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
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
>> just insert a "normal" link in the picture that will open IE

That I can do. Would that help?

Sid
0
 
currentdbAuthor Commented:
Sid,
Yes that would help. It's better than nothing. Thanks :)
0
 
SiddharthRoutCommented:
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
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
Sure no probs ;)

Sid
0
 
SiddharthRoutCommented:
BTW if you have internet at home you can download it from above :)

Sid
0
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
Do you want me to save a copy for you and upload it?

Sid
0
 
currentdbAuthor Commented:
If you have time of course, but it's not the end of the world :)
0
 
SiddharthRoutCommented:
Here it is

Sid
currentdb-v5.xls
0
 
currentdbAuthor Commented:
Downloaded it thanks :)

Now I'll incorporate the code you provided.
0
 
SiddharthRoutCommented:
I have already done that ;)

Sid
0
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
Gimme 10 mins... I will get that arranged. I am on a call right now.

Sid
0
 
currentdbAuthor Commented:
No problem take your time. I gotta eat something cause I'm very hungry. I'll be back in abt 30 min.
0
 
Rory ArchibaldConnect With a Mentor Commented:
Based on Helen's sample, something like this should work.
currentdb-v5.xlsm
0
 
SiddharthRoutConnect With a Mentor Commented:
Here it is

Sid
currentdb-v5.xls
0
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
I have no issues in you splitting points ;)

Sid
0
 
SiddharthRoutCommented:
Have you checked Rory's file?

Sid
0
 
currentdbAuthor Commented:
Which file is from Rory ?
0
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
Ha ha ha ... You forgot to assign points to me after all... lolzzz

Sid
0
 
Helen FeddemaCommented:
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
 
currentdbAuthor Commented:
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
 
currentdbAuthor Commented:
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
 
Rory ArchibaldConnect With a Mentor Commented:
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
 
currentdbAuthor Commented:
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
 
Rory ArchibaldCommented:
Did you use the Request Attention button at the top of this thread?
0
 
currentdbAuthor Commented:
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
 
Rory ArchibaldCommented:
It may be that they are busy at the moment. I'm sure it will get sorted out in due course.
0
 
currentdbAuthor Commented:
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
 
currentdbAuthor Commented:
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
 
currentdbAuthor Commented:
Thanks everyone for your help and of course Helen's exemple is beautiful. Hope to see you all again around for another questions! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.