Folder Picker

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,424
Messages
5,658,721
Members
418,466
Latest member
lisamck

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top