there are 2 ways to open a path browse dialog in VB/VBA. and there is a little diffenent between VB and VBA.
check this out ~

Way No.1 : Legacy VB
1. Add a new module in you VB project .
2. Copy & Paste the code below:

Const BIF_RETURNONLYFSDIRS = &H1Private Type SHITEMID
    cb As Long
    abID As Byte
End Type
Private Type ITEMIDLIST
    mkid As SHITEMID
End Type
Private Type BROWSEINFO
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End TypePrivate Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As LongPrivate Function GetDir(ByVal szCaption As String) As String
    Dim bi As BROWSEINFO, idl As ITEMIDLIST, rtn As Long, pidl As Long, pos As Long, path As String
    bi.hOwner = Me.hwnd        (Change Me.hwnd to Application.hwnd when you currently use VBA)
    bi.lpszTitle = szCaption
    bi.ulFlags = BIF_RETURNONLYFSDIRS
    pidl = SHBrowseForFolder(bi)
    path = Space(512)
    rtn = SHGetPathFromIDList(ByVal pidl, path)
    If rtn Then pos = InStr(path$, Chr$(0)):   GetDir = Left(path, pos - 1)
End Function

Way No.2 : VB for Application

1. In the VB6 IDE pull down the Project menu and select References. In the resulting dialog box scroll down to "Microsoft Shell Controls and Automation" and check the box, then click [OK]. (This is "Adding a Reference".)

2. Now (after declaring a couple variables, and finding in the VisualC++ help that the constant BIF_RETURNONLYFSDIRS is 1) this code (similar to Peter's) does in fact return JUST the folder name. For "I:\Program Files\Microsoft Visual Studio\VB98\Wizards" it returns the string "Wizards". Not too useful.

3. Now, thanks to Simon Conway-Smith's post, above, we add the code to retrieve the full (drive and) path from shlFolder.Items.Item.Path.

Here's the code, in the on-click method for a button in a test program that actually works (I just now wrote it).

Private Sub GoButton_Click()
    Dim shlShell As Variant, shlFolder As Variant
    Set shlShell = New Shell32.Shell
    Set shlFolder = shlShell.BrowseForFolder(Me.hWnd, "Select a Folder", 1)
    FolderLabel.Caption = shlFolder.Title 
    PathLabel.Caption = shlFolder.Items.Item.Path 
End Sub

End.