Solved

Googlemaps into userform possible ?

Posted on 2011-02-23
57
730 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
ID: 34963420
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
ID: 34963552
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
ID: 34963753
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
ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34963769
What link do you want to shown on the userform?

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34963776
Helen: That is simply beautiful...

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 34963870
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
ID: 34963910
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
ID: 34963939
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
ID: 34963985
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
ID: 34964023
Yes it is easy if you have a link ready. Calculating the link is something else. :)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34964047
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
ID: 34964051
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
ID: 34964067
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
ID: 34964111
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
ID: 34964153
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
ID: 34964177
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
ID: 34964251
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
ID: 34964533
0
 
LVL 1

Author Comment

by:currentdb
ID: 34964665
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
ID: 34964748
>> 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
ID: 34965102
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
ID: 34965316
>> 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
ID: 34965332
Sid,
Yes that would help. It's better than nothing. Thanks :)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34965380
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
ID: 34965721
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
ID: 34965795
Sure no probs ;)

Sid
0
 
LVL 30

Expert Comment

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

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 34965830
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
 
LVL 30

Expert Comment

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

Sid
0
 
LVL 1

Author Comment

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

Expert Comment

by:SiddharthRout
ID: 34965851
Here it is

Sid
currentdb-v5.xls
0
 
LVL 1

Author Comment

by:currentdb
ID: 34965868
Downloaded it thanks :)

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

Expert Comment

by:SiddharthRout
ID: 34965901
I have already done that ;)

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 34965908
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
ID: 34965929
Gimme 10 mins... I will get that arranged. I am on a call right now.

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 34965945
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
ID: 34965962
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
ID: 34966000
Here it is

Sid
currentdb-v5.xls
0
 
LVL 1

Author Comment

by:currentdb
ID: 34966138
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
ID: 34966163
I have no issues in you splitting points ;)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34966164
Have you checked Rory's file?

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 34966176
Which file is from Rory ?
0
 
LVL 1

Author Comment

by:currentdb
ID: 34966184
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
ID: 34966190
Ha ha ha ... You forgot to assign points to me after all... lolzzz

Sid
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34966327
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
ID: 34966331
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
ID: 34966360
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
ID: 34967915
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
ID: 34969588
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
ID: 34969604
Did you use the Request Attention button at the top of this thread?
0
 
LVL 1

Author Comment

by:currentdb
ID: 34969620
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
ID: 34969665
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
ID: 34969691
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
ID: 34970331
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
ID: 34970383
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
ID: 34979258
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel VBA 10 39
Office 2016 without internet 6 37
Excel Question 17 15
How to transform one row line like this in excel table 2010? 8 16
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

770 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