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.
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 🙂
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
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
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.
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.