# Excel Macro open hyperlink



## onq (Mar 22, 2010)

Hi
I was wondering if anyone could help me with macros in Excel 2007.

I'm trying to create a macro that when a certain column has data entered in to it, that a word document opens in a new window (via a hyperlink). It's for a timesheet situation, when a user fills in hours in the sick leave column, we want the Application for Sick Leave to automatically open.

Can anyone help?

Thanks,

Melissa


----------



## RSpecianjr (Jan 20, 2010)

Hey Melissa,

Yeah, shouldn't be to hard to do. 

Do you have any criteria for the data? ie just numbers? how many digits? or does that not matter?

What column is it in? Is it the whole column or a range? for instance from row 2 to 30.

Regards,

Robert Specian Jr.


----------



## onq (Mar 22, 2010)

Hi Robert

The timesheet spreadsheet is set up with a different worksheet for the 12 months, and the sick leave column is in column I
The number entered in column I would be to one decimal place and could be double digits.

Thanks for taking the time to reply!


Melissa


----------



## RSpecianjr (Jan 20, 2010)

Hey Melissa,

Sorry for the delay, I've been getting swamped at work. Bah! haha.

What you will want is an event macro. We will use the "Workbook_SheetChange" Event which is located under the "ThisWorkbook" Object. To get there: 

1. Press Alt+F11 to open the VBA Editor.
2. Double click on "ThisWorkbook" on the left hand side.

If you notice, there are two dropdown menus at the top. They should say "(General)" and "(Declarations)", respectively. If you change General dropdown to Workbook, then the second dropdown will include a list of available Event Macros applied to the entire workbook.

Since we need the script to run when a cell value is changed, we will use the "SheetChange" Event. 

At this point there will probably be two separate Private Sub's, Workbook_Open() and Workbook_SheetChange(ByvalSh as Object, ByVal Target As Range). We only want the second one, I usually clean it up a bit and delete the Workbook_Open macro (including the End Sub that belongs to it).

We have our event, now we need our code. At this point, it will run whenever any cell in the workbook is changed. Let's limit that to column I. If we look, we can see that a variable has already been set to the current cell's range.

```
Private Sub Workbook_SheetChange(ByvalSh as Object, [B]ByVal Target As Range[/B])
```
Lets determine if the Target is in column I (I is the 9th column). Then open a hyperlink if it is.


```
If Target.Column = 9 Then 
     ActiveWorkbook.FollowHyperlink "Hyperlink", NewWindow = True
End If
```
Really, you could add a lot more functionality to it. Each product in Microsoft Office is built to work with the other products in Microsoft Office. So, Depending on how you have your Word document setup, You could have it automatically fill the information in for you.

Taking a step back, you could also determine the length of the value entered to insure it is a one or two digit number. That would look something like:


```
If Target.Column = 9 And Len(Target.Value) < 3 Then
     ActiveWorkbook.FollowHyperlink "Hyperlink", NewWindow = True
End If
```
With this code there is only one problem. If you are changing multiple cells at once it will produce an error. I don't imagine you would be changing multiple instances at once, except maybe to delete the data. Simple error handling should suffice. 


```
On Error Goto Ext
If Target.Column = 9 And Len(Target.Value) < 3 Then
     ActiveWorkbook.FollowHyperlink "Hyperlink", NewWindow = True
End If
Ext:
```
If you would like the hyperlink to open regardless of putting/clearing multiple cells in, then use "On Error Resume Next" instead.

Hope this helps,

Robert Specian Jr.


----------



## onq (Mar 22, 2010)

Hi again Robert
Thanks so much for your help. VB script is all very new to me, but I've got it working so that's great.
One thing that I'm stuck on is the timesheet workbook has 12 worksheets, one for each month and mine only seems to be working on the one worksheet. So do I need to write the VB script on to each workbook? Or is there a better way of doing that? If you want me to email you through a copy of the worksheet let me know (if you have the time).

Thanks again.


Melissa


----------

