Dialog Box To Select Folder Only

  • Hi there! I have code below which brings up a dialogue box and allows me to sect an excel file. Is there a way to change this code so the dialogue box will ask me to select a folder instead? I want to be able to select a folder and the rest of the code will open the files in the folder.



    I hope you can help!


    Thanks

  • Re: Change Message Box To Select Folder Not File


    Use :

    Code
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Show
        myFolder = .SelectedItems(1)
        End With
  • Re: Change Message Box To Select Folder Not File


    If that's the case, paste the following code at the beginning of a module and execute the "test" macro. The result will be displayed in a msgbox :

  • Re: Change Message Box To Select Folder Not File


    Thanks Daniel,


    I tried what you said (I have XL2000) but got a compile error "Cannot define a Public User-defined type within object module"


    Do you know what I might be missing?

  • Re: Change Message Box To Select Folder Not File


    Oops! Thanks!
    Is it possible to make the Get Folder a variable so I can use the result later in the script?

  • Re: Change Message Box To Select Folder Not File


    Just put it :

    Code
    Sub Test()
        Dim Msg As String, myFolder As String
        'retrieve folder name in myFolder variable
        myFolder = GetDirectory(Msg)
        '*** subsequent code
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!