VBA question about API coding and MS Web browser

[gr8_big_geek]

I have inserted a microsoft web browser in my Excel sheet1. at Auto_Run I have a form that pops up to allow you to type in the longitude/latitude. I am trying to run lat/long and get google.maps to display in the API box I inserted. I am running the following code:

Public Sub GeoCode()
On Error Resume Next
Dim sendstring As String
Dim lat As String
Dim lon As String
lat = Sheet1.Cells(3, 6).Value
lon = Sheet1.Cells(2, 6).Value
sendstring = lat & ",+" & "+" & lon
Sheet1.WebBrowser1.Navigate ("http://maps.google.com/maps?q=" & sendstring)
Sheet1.WebBrowser1.Height = 500
Sheet1.WebBrowser1.Width = 675
End Sub

On "This workbook" and "sheet1" I have: Option Explicit so it will reference to them.

Here is my problem. I also would like to do the same thing, but instead of running lat/long, I have another form you can select to enter the address. Once you type in the address and hit the enter button the following code runs:

Public Sub Geo_Code()
On Error Resume Next
Dim address As String
Dim city As String
Dim state As String
Dim sendstring As String
Dim myadd As String
Dim mycty As String
Dim myst As String
Dim mylat As String
Dim mylon As String
myadd = Sheet1.Range("add")
mycty = Sheet1.Range("cty")
myst = Sheet1.Range("st")
mylat = Sheet1.Range("lat")
mylon = Sheet1.Range("lon")
address = Sheet1.Cells(1, 2).Value
city = Sheet1.Cells(2, 2).Value
state = Sheet1.Cells(3, 2).Value
address = Replace(address, " ", "+", 1)
sendstring = address & ",+" & city & "+" & state
Sheet1.WebBrowser2.Navigate ("http://maps.google.com/maps?q=" & sendstring)
Sheet1.WebBrowser2.Height = 500
Sheet1.WebBrowser2.Width = 675
End Sub

My problem is that Excel keeps shutting down and having to recover the document.

I can email a copy of this, b/c I do not see a way to attach it to this forum entry. Either way, I'm at my wits end. I just want it to run my data inputted into the cells as a string and them display them in the API box, or Microsoft WebBrowser1 window on the Excel Sheet1.

Any help would be great. Thanks

 

[sunnyland]

Hello there,

Try this code and see if it display the map as wanted. I have added a postcode named range. I have only used the named ranged to build the send string. Not sure but I think the + sign that you used should be only use to separate field in the URL query ie: from+ to+.

But I am not that familiar with the question

Option Explicit
Public Sub Geo_Code()
Dim sendstring As String
Dim myadd As String
Dim mycty As String
Dim myst As String
Dim mcode As String

myadd = Sheet1.Range("add").Text
mycty = Sheet1.Range("cty").Text
mcode = Sheet1.Range("postcode").Text
myst = Sheet1.Range("st").Text


sendstring = myadd & " " & mycty & " " & mcode & " " & myst 'sorry corrected the + to an & from your previous code
Sheet1.WebBrowser2.Navigate ("http://maps.google.com/maps?q=" & sendstring)
Sheet1.WebBrowser2.Height = 500
Sheet1.WebBrowser2.Width = 675
End Sub

 

[gr8_big_geek]

Thank you very much for your reply!

I was able to get the code working. My problem was that I was trying to have both of the Public Sub's in the same Module. Once I placed each Public Sub in separate modules I was able to run them without Excel shutting down. At the top of the ThisWorkbook I have Option Explicit, along with Sheet1. For some reason the Option Explicit has to reference each Public Sub in separate modules.

I'm very grateful that you responded to my inquiry though, that means a lot! Thank you.

Your geek~