Folder Picker

[AlexanderBB]

This works very nicely

https://stackoverflow.com/questions...o-select-folder-and-return-the-path-as-string

Does anyone know if there's any way to disable the Tools Button ?

 

[Yongle]

Not aware of any way to do that, but here is a way to browse for folder where Tools option is not available

Credit for this code : http://www.cpearson.com/excel/BrowseFolder.aspx

You must set a reference to the "Microsoft Shell Controls And Automation" library
In VBA, go to the Tools menu, choose References, and scroll down in the list to "Microsoft Shell Controls And Automation" and check the checkbox.
Then, copy the following code into a new standard code module
Then, run FldrBrowser

Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260

Function BrowseFolder(Optional Caption As String, Optional InitialFolder As String) As String
    Dim SH As Shell32.Shell
    Dim F As Shell32.Folder
    Set SH = New Shell32.Shell
    Set F = SH.BrowseForFolder(0&, Caption, BIF_RETURNONLYFSDIRS, InitialFolder)
    If Not F Is Nothing Then
        BrowseFolder = F.Items.Item.Path
    End If
End Function

Sub [COLOR=#006400][I]FldrBrowser[/I][/COLOR]()
    Dim F As String, I As String
    I = ThisWorkbook.Path
    F = BrowseFolder(Caption:="Select A Folder", InitialFolder:=I)
    If F = vbNullString Then MsgBox "No folder selected." Else MsgBox "Folder Selected: " & F
End Sub

 

[AlexanderBB]

Thanks Yongle. Installed Ref and its working well