Folder Picker

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,367
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,102,692
Messages
5,488,301
Members
407,634
Latest member
ps01

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top