I am constantly adding hyperlinks to non-office files in a specific folder
other than the one the Excel workbook is in. Is there a way to set the
defaults of the Insert Hyperlink popup so that upon selecting a cell and
selecting Hyperlink a file window will popup with All Files in the Specific
Folder?

I appreciate any help you can offer.

Thanks Dave B

Dave,
I know of no way to preset the display in the Insert Hyperlinks box.
Somebody else may know how.
The following code achieves the same end by inserting a hyperlink
formula in the active cell instead of a hyperlink object.
You get the same result and hyperlink formulas use less memory.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub MakeEasyHyperlinks()
'Jim Cone - San Francisco, USA - November 2006
'Adds hyperlink formula in the active cell.
'The strNewPath variable specifies the path to the folder
'containing the file to be linked.
'Calls function "SomeplaceBetter"
'To make this work, user should add a custom button to a
'toolbar and assign this macro (MakeEasyHyperlinks) to it.

On Error GoTo BadPathToFollow
Dim strNewPath As String
Dim strOldPath As String
Dim varFilePath As Variant

strOldPath = Application.DefaultFilePath
strNewPath = "C:\Program Files\Microsoft Office\Office"

'Change to the new file path.
Call SomeplaceBetter(strNewPath)

'Displays box in which to select the file.
varFilePath = Application.GetOpenFilename(Title:="Select File to Hyperlink")
If varFilePath = False Then
Call SomeplaceBetter(strOldPath)
Exit Sub
End If
'Enters hyperlink formula in active cell.
ActiveCell.Formula = "=Hyperlink(""" & varFilePath & """)"

'Restores the original file path
Call SomeplaceBetter(strOldPath)

Exit Sub
BadPathToFollow:
MsgBox "Went down the wrong path. "
End Sub


Function SomeplaceBetter(ByRef strPath As String) As Byte
ChDrive strPath
ChDir strPath
End Function
'-------------------



"Dave B"
wrote in message
I am constantly adding hyperlinks to non-office files in a specific folder
other than the one the Excel workbook is in. Is there a way to set the
defaults of the Insert Hyperlink popup so that upon selecting a cell and
selecting Hyperlink a file window will popup with All Files in the Specific
Folder?
I appreciate any help you can offer.
Thanks Dave B

Jim,
This looks like just what I need, but I'm having trouble executing it. I
added a button to the sheet and changed the variable strNewPath to the
desired path. When I click on the button I do not get the window of files -
I get nothing. I looked at the properties of the button and did not find an
onclick property (I've done a little Delphi programming - no VBA
programming). Another clue is that when I run the mouse over the button I
get the Move Object cursor.
Do I need to do something to activate the button, or do you have another
suggestion?

Thanks for taking the time to work with me.

Dave
"Jim Cone" wrote in message
...
Dave,
I know of no way to preset the display in the Insert Hyperlinks box.
Somebody else may know how.
The following code achieves the same end by inserting a hyperlink
formula in the active cell instead of a hyperlink object.
You get the same result and hyperlink formulas use less memory.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub MakeEasyHyperlinks()
'Jim Cone - San Francisco, USA - November 2006
'Adds hyperlink formula in the active cell.
'The strNewPath variable specifies the path to the folder
'containing the file to be linked.
'Calls function "SomeplaceBetter"
'To make this work, user should add a custom button to a
'toolbar and assign this macro (MakeEasyHyperlinks) to it.

On Error GoTo BadPathToFollow
Dim strNewPath As String
Dim strOldPath As String
Dim varFilePath As Variant

strOldPath = Application.DefaultFilePath
strNewPath = "C:\Program Files\Microsoft Office\Office"

'Change to the new file path.
Call SomeplaceBetter(strNewPath)

'Displays box in which to select the file.
varFilePath = Application.GetOpenFilename(Title:="Select File to
Hyperlink")
If varFilePath = False Then
Call SomeplaceBetter(strOldPath)
Exit Sub
End If
'Enters hyperlink formula in active cell.
ActiveCell.Formula = "=Hyperlink(""" & varFilePath & """)"

'Restores the original file path
Call SomeplaceBetter(strOldPath)

Exit Sub
BadPathToFollow:
MsgBox "Went down the wrong path. "
End Sub


Function SomeplaceBetter(ByRef strPath As String) As Byte
ChDrive strPath
ChDir strPath
End Function
'-------------------



"Dave B"
wrote in message
I am constantly adding hyperlinks to non-office files in a specific folder
other than the one the Excel workbook is in. Is there a way to set the
defaults of the Insert Hyperlink popup so that upon selecting a cell and
selecting Hyperlink a file window will popup with All Files in the
Specific
Folder?
I appreciate any help you can offer.
Thanks Dave B

Dave,
Some things that could have been overlooked?...
The code has to be copied and pasted into a standard module,
not the ThisWorkbook module and not the module behind the
sheet.
There are two types of buttons that can added to a worksheet.
The simplest to work with is the button from the Forms toolbar
found on the View | Toolbars menu. A button from the Control Toolbox
(also found on the same menu) is the other type of button.

On my XL2002 setup, a Forms button is labeled "Button 1" and
the Control Toolbox button is labeled "CommandButton1".

Assuming you use the Forms toolbar button, when you add it to
the sheet, you are presented with a box listing the macros available
to assign to the button...select "MakeEasyHyperlinks".
If you already have the button on the sheet, right-click it and select
"Assign Macro".

Using a button from the Control Tool Box requires an entirely
different approach. You have to write one line of code in
the sheet module to call the macro. Use the Forms button.
--
Regards,
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"Dave B" wrote in message ...
Jim,
This looks like just what I need, but I'm having trouble executing it. I
added a button to the sheet and changed the variable strNewPath to the
desired path. When I click on the button I do not get the window of files -
I get nothing. I looked at the properties of the button and did not find an
onclick property (I've done a little Delphi programming - no VBA
programming). Another clue is that when I run the mouse over the button I
get the Move Object cursor.
Do I need to do something to activate the button, or do you have another
suggestion?

Thanks for taking the time to work with me.

Dave
"Jim Cone" wrote in message
...
Dave,
I know of no way to preset the display in the Insert Hyperlinks box.
Somebody else may know how.
The following code achieves the same end by inserting a hyperlink
formula in the active cell instead of a hyperlink object.
You get the same result and hyperlink formulas use less memory.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub MakeEasyHyperlinks()
'Jim Cone - San Francisco, USA - November 2006
'Adds hyperlink formula in the active cell.
'The strNewPath variable specifies the path to the folder
'containing the file to be linked.
'Calls function "SomeplaceBetter"
'To make this work, user should add a custom button to a
'toolbar and assign this macro (MakeEasyHyperlinks) to it.

On Error GoTo BadPathToFollow
Dim strNewPath As String
Dim strOldPath As String
Dim varFilePath As Variant

strOldPath = Application.DefaultFilePath
strNewPath = "C:\Program Files\Microsoft Office\Office"

'Change to the new file path.
Call SomeplaceBetter(strNewPath)

'Displays box in which to select the file.
varFilePath = Application.GetOpenFilename(Title:="Select File to
Hyperlink")
If varFilePath = False Then
Call SomeplaceBetter(strOldPath)
Exit Sub
End If
'Enters hyperlink formula in active cell.
ActiveCell.Formula = "=Hyperlink(""" & varFilePath & """)"

'Restores the original file path
Call SomeplaceBetter(strOldPath)

Exit Sub
BadPathToFollow:
MsgBox "Went down the wrong path. "
End Sub


Function SomeplaceBetter(ByRef strPath As String) As Byte
ChDrive strPath
ChDir strPath
End Function
'-------------------



"Dave B"
wrote in message
I am constantly adding hyperlinks to non-office files in a specific folder
other than the one the Excel workbook is in. Is there a way to set the
defaults of the Insert Hyperlink popup so that upon selecting a cell and
selecting Hyperlink a file window will popup with All Files in the
Specific
Folder?
I appreciate any help you can offer.
Thanks Dave B