Folder Picker

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,100
Office Version
365
Platform
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,100,181
Messages
5,472,984
Members
406,842
Latest member
Nay83

This Week's Hot Topics

Top