Back to Excel Newsletter Archives
EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS
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.
If you want to select a file from the current folder, use this option and click the file you want to link to.
If you want to select a web page, use this option to select from a list of web pages you have browsed.
If you want to link to a recently used file, use this option.
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 ifOr, if you are typing the date into your code, simply use;
dDate = DateSerial(2006, 8, 12)PASSING THE DATE TO A LONG VARIABLE
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 = dDateFinally, we use the Long integer that represents our date in the AutoFilter criteria like;
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 SubFILTER BY EXACT DATE
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 SubFILTER BY DATE & TIME
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
Software Categories Search Software
Excel Add-ins || Excel Training || Excel Templates || Employee Scheduling Software || Excel Password Recovery and Access & Word etc|| Excel Recovery and Access & Word etc || Financial Software || Financial Calculators || Conversion Software || Construction Estimating Software || Drilling Software || Real Estate Investment Software || Time Management Software || Database Software || Neural Network Software || Trading Software || Charting Software || Windows & Internet Software || Barcodes Fonts, ActiveX, Labels and DLL's || Code Printing Software || Outlook Add-ins
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.