How to open windows folder using double click on a cell.

[nathandavies]

Hi all,
i'm trying to create some code so if i double click on a cell ("Open_Project") i will open the project folder.

I have tried to create the code but i'm having trouble finishing it can anyone help at all?

Sub OpenProjectFolder()Shell explorer.exe,ProjectRootFolder & " \ " & CCompanyName & " \ " & JobNumber & " - " & CSiteName", vbNormalFocus
End Sub

Thanks ND

[snb]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    With Application.FileDialog(4)
        .InitialFileName = Environ("temp") & "\"
        If .Show = -1 Then c00 = .SelectedItems(1)
    End With
    Cancel = True
End Sub

[Kenneth Hobs]

Change the value of fn to suit. Right click the sheet's tab, View Code, and paste.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)  
  Dim r As Range, ir As Range, fn$
  On Error Resume Next
  Set r = Range("Open_Project")
  If Err.Number = 1004 Then Exit Sub
  Set ir = Intersect(r, Target)
  If ir Is Nothing Then Exit Sub
  
  Cancel = True
  fn = ThisWorkbook.Path
  Shell "explorer " & """" & fn & """", vbNormalFocus
End Sub

[nathandavies]

snb,
I have tried your code but it doesn't show any files have i missed something out?

If Not Application.Intersect(Target, Range("Open_Project")) Is Nothing ThenPrivate Sub OpenProjectFolder(ByVal Target As Range, Cancel As Boolean)
    With Application.FileDialog(4)
        .InitialFileName = Environ(ProjectRootFolder & " \ " & CCompanyName & " \ " & JobNumber & " - " & CSiteName)
        If .Show = -1 Then c00 = .SelectedItems(1)
    End With
    Cancel = True
End If

[nathandavies]

Kenneth,

I seem to be getting an error, Ambiguous name detected. any reason?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)    Dim r As Range, ir As Range, fn$
    On Error Resume Next
    Set r = Range("Open_Project")
    If Err.Number = 1004 Then Exit Sub
    Set ir = Intersect(r, Target)
    If ir Is Nothing Then Exit Sub
     
    Cancel = True
    fn = ProjectRootFolder & " \ " & CCompanyName & " \ " & JobNumber & " - " & CSiteName.Path
    Shell "explorer " & """" & fn & """", vbNormalFocus
End Sub

[Kenneth Hobs]

You copied the code too fast. The forum likes to concatenate the 2nd line of code to the 1st when code is first posted. I most always edit and correct right after my post.

In the Visual Basic Editor (VBE) menu Debug, click Compile before a Run. It will should find where errors like that are.

[nathandavies]

Kenneth,
I have corrected my mistake, but it only takes me to the C drive is doesn't use the cells to find the location on the server. am i missing something

    Dim r As Range, ir As Range, fn$    
On Error Resume Next
    Set r = Range("Open_Project")
    If Err.Number = 1004 Then Exit Sub
    Set ir = Intersect(r, Target)
    If ir Is Nothing Then Exit Sub
     
    Cancel = True
    fn = ProjectRootFolder & " \ " & CCompanyName & " \ " & JobNumber & " - " & CSiteName.Path
    Shell "explorer " & """" & fn & """", vbNormalFocus




End Sub

[snb]

You asked for folder, not for files.
You asked for doubleclick.
Test the code before adapting anything.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
    With Application.FileDialog(3) 
        .InitialFileName = Environ("temp") & "\" 
        If .Show = -1 Then c00 = .SelectedItems(1) 
    End With 
    Cancel = True 
End Sub

[Kenneth Hobs]

I have no idea what your fn resolves to. If you Compile, it should have shown that as an error. Are those global variables? You say cells, are those named cells? If so, what is CSiteName.Path? If a workbook, then that would not make sense.

Use Debug.Print to see the value in the VBE Immediate window. If VBE's Immediate window is not in view, set it in VBE's menu View.

e.g.

Sub Test()  Dim fn$
  fn = fn = ProjectRootFolder & " \ " & CCompanyName & _
    " \ " & JobNumber & " - " & CSiteName.Path
  Debug.Print fn
End Sub

If some of those variables are named ranges, then add brackets ([]'s) around the named range variable or use Range() as I did.

[nathandavies]

Kenneth,
correct fn are cells which make up the folder location. (\\root path\company name\job number - site name)
I have removed the ".path" which was an error in the code, but it only opens to My Documents

[Kenneth Hobs]

If it is not opening to the correct folder, your fn value is not what you think it is. I guess you added the []'s?

After a run, the Immediate Window will show Debug.Print results. If not selected in View menu, press Ctrl+G.

fn = [ProjectRootFolder] & " \ " & [CCompanyName] & " \ " & _  [JobNumber] & " - " & [CSiteName]
Debug.Print fn

[nathandavies]

Kenneth,
thanks for your help, i have gone down another root.