Back to Excel Newsletter Archives

OzGrid Excel and VBA Newsletter August 2006

Search Tips

Newsletter Index

EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS

EXCEL TIPS AND TRICKS

HYPERLINKS

Hyperlinks provide us with a convenient way to jump to a specific cell, worksheet, workbook, another program on your hard drive, a network, an intranet or the internet. We can give our hyperlinks meaningful names, and provide a screen tip available when the mouse is hovered over them.

TO CREATE A HYPERLINK TO AN EXISTING FILE OR WEB PAGE

The easiest way to do this is to right click on the cell in which you wish to place your hyperlink, then select Hyperlink from the shortcut menu.

Under Link to: in the dialog select Existing File or Web Page. Once you do this, there are a few options you can choose.

Current Folder

If you want to select a file from the current folder, use this option and click the file you want to link to.

Browsed Pages

If you want to select a web page, use this option to select from a list of web pages you have browsed.

Recent Files

If you want to link to a recently used file, use this option.

Address

You can paste the address of the file you wish to link to in this area.

Finally, you can type the text you wish to display in the Text to Display option, and if you wish you can add a screen tip that can be seen when the mouse is hovered over the hyperlink.

CREATE A HYPERLINK TO A SPECIFIC PLACE ON A WEB PAGE

To hyperlink to a specific point in a webpage, you must first bookmark the location you wish to hyperlink to.

You can then make a selection from Current Folder, Browsed Pages, Recent Files, type in the address in the Address: area or select the web page by opening your browser and searching for the web page you want to link to. Once found, switch back to Excel without closing down your browser.

Click Bookmark, then double click the bookmark you want to use.

TO CREATE A HYPERLINK TO A PLACE IN A WORKBOOK

If you wish to link to another location in your current workbook, or another workbook, you can use a cell reference to do this. You can also use a named range , but you must ensure that the cells are named in the destination workbook.

Again right click on your text or graphic you wish to represent the hyperlink, then select Hyperlink from the shortcut menu.

There are two options you can select:

Place in this Document: Use this option to link to a location in the current workbook.

Existing File or Web Page:To link to a location in another workbook, choose Existing File or Web Page.  Once you have located the workbook you with to link to, select the Bookmark button and in the list under Cell Reference, select the sheet you wish to link to, and in the Type in the cell reference box, enter in the cell reference number. Click OK. If you have given your cells a defined name, then in the list under Defined Names, click the name of the range you wish to link to and click OK.

If you use this option, it is better to use a defined name to link to another Worksheet. If you do this and your worksheet name changes, your hyperlinks will be unaffected.

CREATE A HYPERLINK TO A NEW FILE

To create a hyperlink to a new file, you must first right click on your text or graphic to be used to represent the hyperlink.

Select Create New Document from the Link to: area of the dialog.

Under Name of new document: enter in the name of your document. The full path should be shown under the heading Full path: If this is not the path you want, click the Change button and select the location, then click OK.

There are two options under the When to Edit: box, make the choice to either (a) Edit the New Document Later or (b) Edit the New Document Now.

HYPERLINK TO AN EMAIL ADDRESS

Finally, if you wish to hyperlink to a specific email address, again right click on your text or graphic to represent your hyperlink. Then select Email address under the Link to: area of the dialog. In the email address and Subject box, type in the recipient and the subject of your email.

* Note that some browsers and email accounts will not recognize a Subject line.

HYPERLINK TO A CHART SHEET

Unfortunately there is no standard way to link to a Chart Sheets. In fact, Excel will not let you do this. The workaround to this is quite simple though.

1) Add a new Worksheet.

2) On the Worksheet you would like the hyperlink to the Chart sheet on, add a hyperlink to the new Worksheet. However, the text to display should read something like: Spending Chart or any applicable text.

3) Activate the newly added Worksheet and go to Format>Sheet>Hide

4) Right click on the hyperlink Worksheet name tab and choose View Code. In here paste the code below and change "Spending Chart" to suite your specific text.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)    If Target.TextToDisplay = "Spending Chart" Then Sheets("Spending Chart").SelectEnd Sub

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 Instant Buy/Download,30 Day Money Back Guarantee & Free Excel Help for LIFE!

Range("A1").AutoFilter Field:=1, Criteria1:=">12/08/2006"

However, when you playback the macro you will likely get an error, or unexpected results. This is because Excel sees the dates as a US date, not the format you use in Windows Regional Settings. One way around this problem is to pass the date to a Long Variable using the DateSerial function. All dates in Excel are stored as Long integers. If you are familiar with Excel and Dates/Times read these pages.

The DateSerial Function is invaluable to those of us that work on PCs without a US date format specified Windows Regional Settings. Its Syntax is; DateSerial(year, month, day)

If your date is being taken from existing data, say a worksheet cell, you can use the DateSerial as shown below;

Dim dDate As DateIf IsDate(Range("A1")) Then    dDate = Range("A1")    dDate = DateSerial(Year(dDate), Month(dDate), Day(dDate))End if

dDate = DateSerial(2006, 8, 12)

Now we are certain our date is being read correctly we can pass it to a Long Variable, e.g

dDate = DateSerial(2006, 8, 12)lDate = dDate

Sub FilterByDate()Dim dDate As DateDim strDate As StringDim lDate As Long    dDate = DateSerial(2006, 8, 12)    lDate = dDate    Range("A1").AutoFilter Field:=1, Criteria1:=">" & lDateEnd Sub

While the above works for dates greater or less than a date, or dates we use, it will often fail on filtering for an exact date. For example, the code below doesn't work on my PC with a European date format.

Sub FilterByExactDateNot()Dim dDate As DateDim strDate As StringDim lDate As Long    dDate = DateSerial(2006, 8, 12)    lDate = dDate    Range("A1").AutoFilter Field:=1, Criteria1:=lDateEnd Sub

It does apply and filter the list of dates in Column "A" but no data shows even though the date does exist.The only work-around I know is to use 2 criteria and make the second date criteria 1 day after the needed date. That is like below

Sub FilterByExactDate()Dim dDate As DateDim strDate As StringDim lDate As Long    dDate = DateSerial(2006, 8, 12)    lDate = dDate    Range("A1").AutoFilter Field:=1, Criteria1:=">=" & lDate, _                     Operator:=xlAnd, Criteria2:="<" & lDate + 1End Sub

If your data is a list of valid Excel dates & times you can use the DateSerial & TimeSerial Function combined and pass the date & time to a Double variable. We need a Double as a valid Excel date and time is seen as a decimal number, not a whole number like dates only are.

Sub FilterByDateTime()Dim dDate As DateDim dbDate As DoubleIf IsDate(Range("B1")) Then    dbDate = Range("B1")    dbDate = DateSerial(Year(dbDate), Month(dbDate), Day(dbDate)) + _         TimeSerial(Hour(dbDate), Minute(dbDate), Second(dbDate))       Range("A1").AutoFilter Field:=1, Criteria1:=">" & dbDateEnd IfEnd Sub