Start Free Trial

asked on

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

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...

ASKER

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...:(

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

What link do you want to shown on the userform?

Sid

Helen: That is simply beautiful...

Sid

ASKER

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...

ASKER

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.

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

ASKER

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 :)

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

Sid

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

ASKER

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.

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

ASKER

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...

ASKER

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

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

Sid

ASKER

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 :)

Ok I am trying to compare the two links below

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

http://maps.google.co.in/maps?f=q&source=s_q&hl=en&geocode=&q=198+MOUNT+PLEASANT+STREET+SYDNEY+NF+B1N2G6&aq=&sll=46.134624,-60.185477&sspn=0.00742,0.01929&g=216+DILLON,+SYDNEY,+NS,+B1P+5C3&ie=UTF8&z=16

Some of the line in the links are common. What is posing problem is fitting in values like

&sll=38.479186,-90.746622&sspn=34.115555,79.013672

If we had all the relevant values then it will be easy to create the link.

Sid

ASKER

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 ?

>> 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

ASKER

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.

>> just insert a "normal" link in the picture that will open IE

That I can do. Would that help?

Sid

ASKER

Sid,
Yes that would help. It's better than nothing. Thanks :)

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

ASKER

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.

Sure no probs ;)

Sid

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

Sid

ASKER

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... :(

Do you want me to save a copy for you and upload it?

Sid

ASKER

If you have time of course, but it's not the end of the world :)

Here it is

Sid
currentdb-v5.xls

ASKER

Downloaded it thanks :)

Now I'll incorporate the code you provided.

I have already done that ;)

Sid

ASKER

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.

Gimme 10 mins... I will get that arranged. I am on a call right now.

Sid

ASKER

No problem take your time. I gotta eat something cause I'm very hungry. I'll be back in abt 30 min.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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 :)

I have no issues in you splitting points ;)

Sid

Have you checked Rory's file?

Sid

ASKER

Which file is from Rory ?

ASKER

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.

Ha ha ha ... You forgot to assign points to me after all... lolzzz

Sid

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.

ASKER

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.

ASKER

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 :)

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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 :(

Did you use the Request Attention button at the top of this thread?

ASKER

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).

It may be that they are busy at the moment. I'm sure it will get sorted out in due course.

ASKER

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.

ASKER

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 :)

ASKER

Thanks everyone for your help and of course Helen's exemple is beautiful. Hope to see you all again around for another questions! :)

ASKER

I just opened 2 new questions here, if you can help:

https://www.experts-exchange.com/questions/26847475/Force-GoogleMaps-to-be-visible-on-the-userform.html

https://www.experts-exchange.com/questions/26847470/Print-userform-on-8x14-possible.html

Thanks