Command Button Code [SOLVED]

[dparteka]

In cell B5 is... HublicEngineeringCustomer's FilesSiemens EnergyDocument LinksP0001813700 DocLink.xls


In cell C5 is... =HYPERLINK(B5)


If I click cell C5 it hyperlinks to the document specified in cell B5


I want to use a Command Button to spark the hyperlink in C5, can you tell me what the code would be?

 

[bobhc]

Good day dparteka


I am assuming you mean a button on the spread sheet.


Just place a shape on the sheet, right click and choose hyperlink and then set up the path in the dialogue box.

 

[SirJB7]

Hi, all!


For just a simple case like this b(ut)ob(ut)hc's suggestion works fine, but there's a underlying main point.


There're 2 types of controls, Form controls (those of the upper part of the Insert Control window at the Programmer tab) and ActiveX controls (those of the lower part). Form controls like a command button built with a shape and an assigned macro aren't updated since almost 10 years, if not more, they're easy to use but they have less flexibility than newer ActiveX controls which all behave in the same way and with the same general interface for setting properties and code: right button on the control, Properties and View Code. Obviously it's preferable to use these ones rather than those older ones, unless you want to achieve a simple task like in this case.


Regards!

 

[dparteka]

Thanks guys... I should have told you that I'm using XP and Excel 2003 so if I right click the Control Toolbox Command Button that is on the spreadsheet I do not get an option to choose "Hyperlink". What I'm looking for is code that will trigger cell C5. Also, the path in B5 changes from spreadsheet to spreadsheet so it can not be part of the code. I tried using this... Range("C5").Select, which causes the curser to go to C5 but it does not trigger the hyperlink. To SirJB7, if I understand you correctly I believe I am using ActiveX control and I think that because after creating and right clicking the button image I choose "View Code".

 

[SirJB7]

Hi, dparteka!

If you get the View Code option after right clicking surely you're using ActiveX controls.

Regarding 2003 version I apologize but actually I almost forgot everything about it.

Regards!

 

[dparteka]

I'd appreciate any help if someone has a solution to this... I’m using XP and Excel 2003


In cell B5 is this text... HublicEngineeringCustomer's FilesSiemens EnergyDocument LinksP0001813700 DocLink.xls


In cell C5 is... =HYPERLINK(B5)


If I click cell C5 it hyperlinks to the document specified in cell B5


I want to use a Control Toolbox Command Button that is on the spreadsheet to trigger the hyperlink in C5, can you tell me what the code would be?


NOTE: The path in B5 changes from spreadsheet to spreadsheet so it can not be part of the code.

 

[NARAYANK991]

Hi ,


Try this :

[pre]

Sub Button1_Click()
Link = [C5]
On Error GoTo NoCanDo
ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True
Exit Sub
NoCanDo:
MsgBox "Cannot open " & Link
End Sub

[/pre]
Copied from :


http://j-walk.com/ss/excel/tips/tip71.htm


Narayan

 

[dparteka]

Hey NARAYANK991... you saved me, thanks. One last thing, what do I change to have the hyperlink open in a maxinum window?

 

[NARAYANK991]

Hi ,


See if including the following statement before the Exit Sub statement works :

[pre]

ActiveWindow.WindowState = xlMaximized

[/pre]
Narayan

 

[dparteka]

Narayan... works great, thanks for your all help