Userform code to open UK post code within Google Maps

[ipbr21054]

On my userform i have TextBox3 which the user enters the customers Post Code.

On the same userform i have a commandbutton called OpenGoogleMaps.

What i am looking for is when the command button is pressed the code looks at the entry in TextBox3 & opens that Post Code in Google Maps.

The is the current code that is on the button for opening Google Maps but not sure how to proceed.

Private Sub OpenGoogleMaps_Click()
ActiveWorkbook.FollowHyperlink Address:="https://www.google.co.uk/maps", NewWindow:=True
End Sub

So basically look in TextBox2 value = BS9 2HH
Now open BS9 2HH in Google Maps.

 

[ipbr21054]

A form i use has the feature which its code is this,

GL2 4AR · Gloucester, UK

Postal code

www.google.com

Does this help with how we write the code ?

At present GL2 4AR is the value in TextBox3

 

[ipbr21054]

This works on the command button.
Just need to write the code for it to get the post code from TextBox3 & apply it to the Google maps code on command button

Private Sub OpenGoogleMaps_Click()
ActiveWorkbook.FollowHyperlink Address:="https://www.google.co.uk/maps/place/GL2+4AR", NewWindow:=True
End Sub

 

[NoSparks]

Private Sub OpenGoogleMaps_Click()
    Dim postcode As String
    postcode = Split(Me.TextBox3.Value, " ")(0) & "+" & Split(Me.TextBox3.Value, " ")(1)
    ActiveWorkbook.FollowHyperlink Address:="https://www.google.co.uk/maps/place/" & postcode, NewWindow:=True
End Sub

 

[ipbr21054]

Hi,
Nearly works.

I enter in TextBox3 BS9 2HH & run the command button.
Google opens to BS9 which is a large area where BS9 2HH pinpoints it more to the street in question

You helped me out before with a post code & i assumed you were in the UK.
So UK post codes are like this,

BS9 2HH or say BS23 4HT

Does the help to edit the code supplied above to open like what was typed in the Textbox3

Thanks

When i entered BS9 2HH the url is as shown.
BS9 · Bristol, UK so its missing the +2HH part

 

[ipbr21054]

Just to advise as i added a bit of code on the end.

Private Sub OpenGoogleMaps_Click()

    Dim postcode As String
    postcode = Split(Me.TextBox3.Value, " ")(0) & "+" & Split(Me.TextBox3.Value, " ")(1) & "+"
    ActiveWorkbook.FollowHyperlink Address:="https://www.google.co.uk/maps/place/" & postcode, NewWindow:=True

End Sub

If i type this format post code BS29 6HD 7 characters
Then it works.

But it doesnt for when i try this format BS9 2HH 6 characters

 

[NoSparks]

From post#3

This works on the command button.
Just need to write the code for it to get the post code from TextBox3 & apply it to the Google maps code on command button

Private Sub OpenGoogleMaps_Click()
ActiveWorkbook.FollowHyperlink Address:="https://www.google.co.uk/maps/place/GL2+4AR", NewWindow:=True
End Sub

The code of post#4 does that no matter if you enter a 3-space-3 or 4-space-3 postal code.

I enter in TextBox3 BS9 2HH & run the command button.
Google opens to BS9 which is a large area where BS9 2HH pinpoints it more to the street in question

I can only reproduce that when there is more than 1 space in the middle of the postal code

Just to advise as i added a bit of code on the end.

Why you would do that

If i type this format post code BS29 6HD 7 characters
Then it works.

But it doesnt for when i try this format BS9 2HH 6 characters

They both work for me with the code of post#4

 

[ipbr21054]

So i have used the code from post #4
For me it would only work for these style post codes BS29 6HD, BS24 7RT, TQ12 4JX

I tried the other post codes BUT all only went to the first part of the post code.
Example, i type BS9 2HH but it only pulled up BS9 when Google opened.

So this code is for TextBox3

Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(Me.TextBox3) > 0 Then
        Me.TextBox3.Value = Format(Replace(Me.TextBox3.Value, " ", ""), "@@@@ @@@")
    End If
End Sub

If i remove that code it works fine for both of them every time.

Put this code on your Textbox & see if it then goes wrong

Type BS9 2HH & see if Google return this,

 

[NoSparks]

try adding the trim function to _BeforeUpdate

Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(Me.TextBox3) > 0 Then
        Me.TextBox3.Value = Trim(Format(Replace(Me.TextBox3.Value, " ", ""), "@@@@ @@@"))
    End If
End Sub

 

[ipbr21054]

That did the trick & works fine.

Many thanks for the help.