Opening folder from selected cell

[Theglyde]

Code I have to open a folder in relation to range being always the same cell. So that specifict cell we call roll. I enter a roll number 123456. It automatically opens the path directory to that roll specifically. When done we save it. Then we can put in another number in that same cell and it will open this new roll folder. What I want to do is be able to have one button on top

then enter a number in cell A1 then another number in cell A2 and so on. From the click on cell A1 and then click the button and it opens that roll folder. After click on Cell A2 that has a different roll number and it would open up that folder.

I am guessing where it says in my code bellow JobNo = Range("Roll").value I would need "roll" to be the actual cell I clicked on before pressing that Button to run the macro. the number in those cell are always actual numbers

Really hope someone can help with this. Probably silly but tired of looking for it
thank you


Sub OpenProjectFolder()

Dim JobNo As Long
JobNo = Range("Roll").Value

IncomingPath = "J:\Paper\VPHW\Shipping\INCOMING\"



IncomingPath1 = IncomingPath & ((JobNo \ 10000) * 10000) & "\"



IncomingPath2 = IncomingPath1 & ((JobNo \ 1000) * 1000) & "-" & ((JobNo \ 1000) * 1000) + 999 & "\" & JobNo & "\" & Range("FormDir4").Value



If Dir(IncomingPath2, vbDirectory) <> "" Then

ret = Shell("explorer " & IncomingPath2, vbMaximizedFocus)

Else

ret = Shell("explorer " & IncomingPath, vbMaximizedFocus)

End If
End Sub

 

[GWteB]

Perhaps this might do what you are looking for

Sub OpenProjectFolder()

    Dim JobNo   As Long
    Dim sPath   As String
    Dim sPath1  As String
    Dim sPath2  As String
    Dim Ret     As Long
    
    sPath = "J:\Paper\VPHW\Shipping\INCOMING\"
    
    With Selection
        If Not .Count > 1 Then
        
            JobNo = .Value
            sPath1 = sPath & ((JobNo \ 10000) * 10000) & "\"
            sPath2 = sPath1 & ((JobNo \ 1000) * 1000) & "-" & ((JobNo \ 1000) * 1000) + 999 & "\" & JobNo & "\" & Range("FormDir4").Value
            
            If Dir(sPath, vbDirectory) <> "" Then
                Ret = Shell("explorer " & sPath2, vbMaximizedFocus)
            Else
                Ret = Shell("explorer " & sPath, vbMaximizedFocus)
            End If
            
        End If
    End With
End Sub

 

[Theglyde]

so funny I just found it

Sub OpenProjectFolder()

Dim JobNo As Long
JobNo = ActiveCell.Value

IncomingPath = "J:\Paper\VPHW\Shipping\INCOMING\"



IncomingPath1 = IncomingPath & ((JobNo \ 10000) * 10000) & "\"



IncomingPath2 = IncomingPath1 & ((JobNo \ 1000) * 1000) & "-" & ((JobNo \ 1000) * 1000) + 999 & "\" & JobNo & "\" & Range("FormDir4").Value



If Dir(IncomingPath2, vbDirectory) <> "" Then

ret = Shell("explorer " & IncomingPath2, vbMaximizedFocus)

Else

ret = Shell("explorer " & IncomingPath, vbMaximizedFocus)

End If
End Sub

 

[Theglyde]

Perhaps this might do what you are looking for

Sub OpenProjectFolder()

    Dim JobNo   As Long
    Dim sPath   As String
    Dim sPath1  As String
    Dim sPath2  As String
    Dim Ret     As Long
   
    sPath = "J:\Paper\VPHW\Shipping\INCOMING\"
   
    With Selection
        If Not .Count > 1 Then
       
            JobNo = .Value
            sPath1 = sPath & ((JobNo \ 10000) * 10000) & "\"
            sPath2 = sPath1 & ((JobNo \ 1000) * 1000) & "-" & ((JobNo \ 1000) * 1000) + 999 & "\" & JobNo & "\" & Range("FormDir4").Value
           
            If Dir(sPath, vbDirectory) <> "" Then
                Ret = Shell("explorer " & sPath2, vbMaximizedFocus)
            Else
                Ret = Shell("explorer " & sPath, vbMaximizedFocus)
            End If
           
        End If
    End With
End Sub

not sure it would have worked, it is very possible but found it just before... Thank you so much

 

[GWteB]

You are welcome. Glad you sorted it.