Dear friends,

In my many of the Excel Tools, wherever there is a need of file path to be entered from your local PC, I try to put a browse button to locate that file in your Windows PC through windows file explorer window. It is the same window which you might have seen in windows for selecting a file.

File Dialog - File Picker

File Dialog – File Picker

How to create your own button in a style whatever you like

You can say what is a big deal in it to create a button in Excel. Any one can add a button from developer tab. Yes, I agree but in this button you may not find options to given lot of effect and style which you want to give it to your button. So to do that here is the simple trick. In Microsoft office Power Point on even in your excel sheet, you can design your own button however you like. Here is some samples, I have designed for you. Download it and start making your macro buttons look like any stylish webpage buttons.
Now, though I have been mentioning above that you can create different types of Stylish buttons, actually I lied to you. What I mean is you can create an image / shape which will look like a button. But don’t worry, in the next step you will see that it will also work like a button 🙂

Design Buttons in Excel

Design Buttons in Excel

How to assign a macro to an Image button

Unlike a command button in excel you just can not double click and it will take you to the VBA code editor where you can write a code which you want to be executed on clicking on those images or buttons whatever you call them 🙂
To work like a button you need to create a Sub procedure i.e. in other words macro which you want to execute on clicking this image or shapes.

Now right click on the image/shape which you have added and click on Assign Macro as shown in below image

How to assign Macro to an Image

How to assign Macro to an Image

Code to make a browse button work

FileDialog is the object which is used for windows file explorer. There are 4 different types of dialogs which you can choose as shown below. Here we need to get the path of a file then we will use dialog type as msoFileDialogFilePicker

File Dialog in VBA

File Dialog in VBA

VBA to Select a File Path using Windows File Dialog

Sub browseFilePath()
    On Error GoTo err
    Dim fileExplorer As FileDialog
    Set fileExplorer = Application.FileDialog(msoFileDialogFilePicker)
    
    'To allow or disable to multi select
    fileExplorer.AllowMultiSelect = False
    
    With fileExplorer
        If .Show = -1 Then 'Any file is selected
            [filePath] = .SelectedItems.Item(1)
        Else ' else dialog is cancelled
            MsgBox "You have cancelled the dialogue"
            [filePath] = "" ' when cancelled set blank as file path.
        End If
    End With
err:
    Exit Sub
End Sub

VBA to Select a Folder Path using Windows File Dialog

All you need to change is the type of the Dialog in the FileDialog Object. To explore the folders ONLy, you can provide the Dialog type as msoFileDialogFolderPicker

Sub browseFolderPath()
    On Error GoTo err
    Dim fileExplorer As FileDialog
    Set fileExplorer = Application.FileDialog(msoFileDialogFolderPicker)
    
    'To allow or disable to multi select
    fileExplorer.AllowMultiSelect = False
    
    With fileExplorer
        If .Show = -1 Then 'Any folder is selected
            [folderPath] = .SelectedItems.Item(1)
        Else ' else dialog is cancelled
            MsgBox "You have cancelled the dialogue"
            [folderPath] = "" ' when cancelled set blank as file path.
        End If
    End With
err:
    Exit Sub
End Sub

In Above code, I am storing the selected path in a named range [filePath] or [FolderPath]. If you have a text box to store the selected file path you can replace it with YourTextBoxName.Text.

Download

Meanwhile over the weekend, you can download this workbook to play around with File explorer for selecting files or folders path. Have a fantastic weekend ahead.

Download this, use it and do not forget to provide me your feedback by typing your comment here or sending en email or you can twit me You can also share it with your friends colleagues.

Download Now