I need a coding solution to the following: I have a command button on a sheet
which is to open a Windows Explorer Directory. I have previously used
hyperlinks to open and show the user with the following code (with button).

Range("V8").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Range("A1").Select
Application.ScreenUpdating = False

I was wondering if there is a better way to do without having the hyperlink.

Damien,
Here's one way:
Private Sub CommandButton2_Click()
Dim SysRoot As String
SysRoot = Split(Environ(20), "=")(1)
Shell SysRoot & "\explorer.exe /e, G:", vbNormalFocus
End Sub

NickHK

"Damien" wrote in message
...
I need a coding solution to the following: I have a command button on a
sheet
which is to open a Windows Explorer Directory. I have previously used
hyperlinks to open and show the user with the following code (with
button).

Range("V8").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Range("A1").Select
Application.ScreenUpdating = False

I was wondering if there is a better way to do without having the
hyperlink.

Thanks Nick, I will try it out, I suppose my other alternative is to
dynamically change the hyperlink via a selection value from a combo box. My
reasoning is that the user may wish to see a particular file belonging to a
project name located in the combo box. Is that at all possible?

Cheers

"NickHK" wrote:

Damien,
Here's one way:
Private Sub CommandButton2_Click()
Dim SysRoot As String
SysRoot = Split(Environ(20), "=")(1)
Shell SysRoot & "\explorer.exe /e, G:", vbNormalFocus
End Sub

NickHK

"Damien" wrote in message
...
I need a coding solution to the following: I have a command button on a
sheet
which is to open a Windows Explorer Directory. I have previously used
hyperlinks to open and show the user with the following code (with
button).

Range("V8").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Range("A1").Select
Application.ScreenUpdating = False

I was wondering if there is a better way to do without having the
hyperlink.

Damien,
Just build the shell string from a variable or range value. e.g.

Private Sub CommandButton2_Click()
Dim SysRoot As String
Dim FilePath as String

SysRoot = Split(Environ(20), "=")(1)
FilePath = Range("a1").Text 'Or cboFile.Text
'Assumes FilePath is the full path to the file
Shell SysRoot & "\explorer.exe /e, " & FilePath, vbNormalFocus
End Sub

You should add some error trapping and/or checking that FilePath is valid.

NickHK

"Damien" wrote in message
...
Thanks Nick, I will try it out, I suppose my other alternative is to
dynamically change the hyperlink via a selection value from a combo box.
My
reasoning is that the user may wish to see a particular file belonging to
a
project name located in the combo box. Is that at all possible?

Cheers

"NickHK" wrote:

Damien,
Here's one way:
Private Sub CommandButton2_Click()
Dim SysRoot As String
SysRoot = Split(Environ(20), "=")(1)
Shell SysRoot & "\explorer.exe /e, G:", vbNormalFocus
End Sub

NickHK

"Damien" wrote in message
...
I need a coding solution to the following: I have a command button on
a
sheet
which is to open a Windows Explorer Directory. I have previously used
hyperlinks to open and show the user with the following code (with
button).

Range("V8").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Range("A1").Select
Application.ScreenUpdating = False

I was wondering if there is a better way to do without having the
hyperlink.

SysRoot = Split(Environ(20), "=")(1)

You can't rely on the system root folder being the 20th item in
the Environment variables. On my machine, Environ(20) is the
Processor Identifier. Instead, use

SysRoot = Environ("SystemRoot")



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"NickHK" wrote in message
...
Damien,
Here's one way:
Private Sub CommandButton2_Click()
Dim SysRoot As String
SysRoot = Split(Environ(20), "=")(1)
Shell SysRoot & "\explorer.exe /e, G:", vbNormalFocus
End Sub

NickHK

"Damien" wrote in message
...
I need a coding solution to the following: I have a command
button on a
sheet
which is to open a Windows Explorer Directory. I have
previously used
hyperlinks to open and show the user with the following code
(with
button).

Range("V8").Select
Selection.Hyperlinks(1).Follow NewWindow:=False,
AddHistory:=True
Range("A1").Select
Application.ScreenUpdating = False

I was wondering if there is a better way to do without having
the
hyperlink.

Chip you are sheer genius. SysRoot = Environ("SystemRoot") done the trick
perfectly, Cheers Guys, D.

"Chip Pearson" wrote:

SysRoot = Split(Environ(20), "=")(1)

You can't rely on the system root folder being the 20th item in
the Environment variables. On my machine, Environ(20) is the
Processor Identifier. Instead, use

SysRoot = Environ("SystemRoot")



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"NickHK" wrote in message
...
Damien,
Here's one way:
Private Sub CommandButton2_Click()
Dim SysRoot As String
SysRoot = Split(Environ(20), "=")(1)
Shell SysRoot & "\explorer.exe /e, G:", vbNormalFocus
End Sub

NickHK

"Damien" wrote in message
...
I need a coding solution to the following: I have a command
button on a
sheet
which is to open a Windows Explorer Directory. I have
previously used
hyperlinks to open and show the user with the following code
(with
button).

Range("V8").Select
Selection.Hyperlinks(1).Follow NewWindow:=False,
AddHistory:=True
Range("A1").Select
Application.ScreenUpdating = False

I was wondering if there is a better way to do without having
the
hyperlink.