vba dailog box

[pedie]

Hi, i need vba to pop up dailog box and let user select/type in the folder path and enter that in userform textbox1, just the the path...Is this possible?

Thanks in advance.

I found something close but it compiles me to select the file/something...

I just want the user to select the folder and then click OK/something to confirm the path...

[/FONT]
Sub Get_Data()
ChDrive "C:\"
ChDir "C:\"
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to import", _
FileFilter:="Excel Files *.xls (*.xls),")
If FileToOpen = False Then
MsgBox "No file specified.", vbExclamation, "Duh!!!"
Exit Sub
Else
Workbooks.Open Filename:=FileToOpen
End If
End Sub

 

[pedie]

Got one from Cpersons....
Please let me know if there is anything better...

[FONT=Courier New]Private Const BIF_RETURNONLYFSDIRS As Long = &H1[/FONT]
[FONT=Courier New]Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2[/FONT]
[FONT=Courier New]Private Const BIF_RETURNFSANCESTORS As Long = &H8[/FONT]
[FONT=Courier New]Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000[/FONT]
[FONT=Courier New]Private Const BIF_BROWSEFORPRINTER As Long = &H2000[/FONT]
[FONT=Courier New]Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000[/FONT]
[FONT=Courier New]Private Const MAX_PATH As Long = 260[/FONT]
[FONT=Courier New]Type BrowseInfo[/FONT]
[FONT=Courier New]   hOwner As Long[/FONT]
[FONT=Courier New]   pidlRoot As Long[/FONT]
[FONT=Courier New]   pszDisplayName As String[/FONT]
[FONT=Courier New]   lpszINSTRUCTIONS As String[/FONT]
[FONT=Courier New]   ulFlags As Long[/FONT]
[FONT=Courier New]   lpfn As Long[/FONT]
[FONT=Courier New]   lParam As Long[/FONT]
[FONT=Courier New]   iImage As Long[/FONT]
[FONT=Courier New]End Type[/FONT]
[FONT=Courier New]Type SHFILEOPSTRUCT[/FONT]
[FONT=Courier New]   hwnd As Long[/FONT]
[FONT=Courier New]   wFunc As Long[/FONT]
[FONT=Courier New]   pFrom As String[/FONT]
[FONT=Courier New]   pTo As String[/FONT]
[FONT=Courier New]   fFlags As Integer[/FONT]
[FONT=Courier New]   fAnyOperationsAborted As Boolean[/FONT]
[FONT=Courier New]   hNameMappings As Long[/FONT]
[FONT=Courier New]   lpszProgressTitle As String[/FONT]
[FONT=Courier New]End Type[/FONT]
[FONT=Courier New]Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _[/FONT]
[FONT=Courier New]   ByVal pidl As Long, _[/FONT]
[FONT=Courier New]   ByVal pszBuffer As String) As Long[/FONT]
[FONT=Courier New]Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _[/FONT]
[FONT=Courier New]   lpBrowseInfo As BrowseInfo) As Long[/FONT]
 
[FONT=Courier New]Function BrowseFolder(Optional Caption As String = "") As String[/FONT]
[FONT=Courier New]Dim BrowseInfo As BrowseInfo[/FONT]
[FONT=Courier New]Dim FolderName As String[/FONT]
[FONT=Courier New]Dim ID As Long[/FONT]
[FONT=Courier New]Dim Res As Long[/FONT]
[FONT=Courier New]With BrowseInfo[/FONT]
[FONT=Courier New]  .hOwner = 0[/FONT]
[FONT=Courier New]  .pidlRoot = 0[/FONT]
[FONT=Courier New]  .pszDisplayName = String$(MAX_PATH, vbNullChar)[/FONT]
[FONT=Courier New]  .lpszINSTRUCTIONS = Caption[/FONT]
[FONT=Courier New]  .ulFlags = BIF_RETURNONLYFSDIRS[/FONT]
[FONT=Courier New]  .lpfn = 0[/FONT]
[FONT=Courier New]End With[/FONT]
[FONT=Courier New]FolderName = String$(MAX_PATH, vbNullChar)[/FONT]
[FONT=Courier New]ID = SHBrowseForFolderA(BrowseInfo)[/FONT]
[FONT=Courier New]If ID Then[/FONT]
[FONT=Courier New]  Res = SHGetPathFromIDListA(ID, FolderName)[/FONT]
[FONT=Courier New]  If Res Then[/FONT]
[FONT=Courier New]      BrowseFolder = Left$(FolderName, InStr(FolderName, vbNullChar) - 1)[/FONT]
[FONT=Courier New]  End If[/FONT]
[FONT=Courier New]End If[/FONT]
[FONT=Courier New]End Function[/FONT]

 

[VoG]

Hi Pedie

One way that will work in all versions of Excel http://www.mrexcel.com/forum/showthread.php?t=564789

 

[pedie]

Peter, thanks...that worked perfect!