Excel & Skype | assigning "tel:" to a button

[manc]

Good morning,

I have the following formula in A1:
=IF(D22="","",HYPERLINK("tel:"&VLOOKUP(D22,TR_Contacts_Data,4,FALSE)))

It looks up the phone number against the chosen contact name from a drop-down list in D22.

Works great - I can click on A1 and it links with Skype and dials the number.

What i want, is instead of me having to click on cell A1, i want a nice "phone" button, so you click on the button and it dials the number in A1 for you.

Any ideas? I've looked on the web and there used to be a Skype for office toolbar but it isn't compatible with our version of office, and, is no longer available for download from official channels anyway.

It sounds pretty easy to do - assign a macro to the button whereby the macro runs the hyperlink.
Just need to know the code to put.

Any help or pointers, greatly appreciated as always.

 

[cooper645]

Found this on a site elsewhere and edited it to suit your needs.

Sub PhoneLink()


    ActiveSheet.Hyperlinks.Add _
        Anchor:=Worksheets("Sheet1").Shapes("[COLOR=#ff0000]PhoneLogo[/COLOR]"), _[COLOR=#008000]'Change "PhoneLogo" to whatever the shape name is[/COLOR]
        Address:="tel:" & Range("A1").Value
    
End Sub

 

[manc]

[SOLVED] Re: Excel & Skype | assigning "tel:" to a button

cooper645,

Thanks for your time my friend. Works like a charm.

Best regards
manc

 

[cooper645]

Re: [SOLVED] Re: Excel & Skype | assigning "tel:" to a button

Pleasure.

 

[manc]

Re: [SOLVED] Re: Excel & Skype | assigning "tel:" to a button

cooper645,

I've encountered an issue.

If i select "phonenumberA" from drop down list in cell A1, click "PhoneLogo" button and make a call, then it works fine.
However, if I then go and choose a "phonenumberB" from drop down list, "phonenumberA" hyperlink remains attached to the "PhoneLogo" button.

I can confirm this by right clicking on the "PhoneLogo" image, selecting Edit Link, and it displays "phonenumberA" in the address box.

I've tried modifying the code slightly as follows, hopefully to clear the hyperlink before the add event, but it makes no difference:

Sub PhoneLink()

    ActiveSheet.Hyperlinks.Delete
    ActiveSheet.RefreshAll
    ActiveSheet.Hyperlinks.Add _
        Anchor:=Worksheets("Sheet1").Shapes("PhoneLogo"), Address:="tel:" & Range("A1").Value
        
End Sub

Any idea how to solve the issue?

Best regards
manc

 

[cooper645]

Re: [SOLVED] Re: Excel & Skype | assigning "tel:" to a button

Manc,

Give this a bash,

Sub PhoneLink()

    ActiveSheet.Shapes.Range(Array("PhoneLogo")).Select
    Selection.ShapeRange.Item(1).Hyperlink.Delete
    
    ActiveSheet.Hyperlinks.Add _
        Anchor:=Worksheets("Sheet1").Shapes("PhoneLogo"), Address:="tel:" & Range("A1").Value
        
End Sub

 

[wideboydixon]

Re: [SOLVED] Re: Excel & Skype | assigning "tel:" to a button

If you don't want to use the hyperlink on a shape/button then you could assign this macro to it:

Public Sub PhoneTheNumberInA1()

ActiveWorkbook.FollowHyperlink Address:="tel:" & Range("A1").Value, NewWindow:=True

End Sub

WBD

 

[manc]

Re: [SOLVED] Re: Excel & Skype | assigning "tel:" to a button

@cooper645,

Thanks for your suggestion but it says object not defined when it hits this line:

[COLOR=#333333]Selection.ShapeRange.Item(1).Hyperlink.Delete

@WBD, your example seems to solve the issue and works when the macro is attached to the shape.
I put the code in the ThisWorkbook.
How can i change it slightly so it doesn't matter which sheet the shape is on, it still refers to 'sheet1', 'A1'?
So i could be on sheet3 with a shape and when its clicked it still refers to the value in 'sheet1', 'A1'?

Best regards
manc[/COLOR]

 

[cooper645]

Re: [SOLVED] Re: Excel & Skype | assigning "tel:" to a button

Manc,

Just one other thought, could you not record a macro of you just removing the hyperlink and then copy that into the start of your code?

 

[wideboydixon]

Re: [SOLVED] Re: Excel & Skype | assigning "tel:" to a button

Public Sub PhoneTheNumberInA1()

ActiveWorkbook.FollowHyperlink Address:="tel:" & Sheets("Sheet1").Range("A1").Value, NewWindow:=True

End Sub

WBD