Routine to request a path from user RRS feed

  • Question

  • Hello friends,

    Can somebody help to make a routine which uses input box to ask user for a path to a required folder? I want to set path as a Public Variant Variable in my User form and use it later in other routines while this User Form is running.

    However, the challenge here is that I do not want to ask user to type in the path every time he/she opens user form. Meaning that at the first time I ask user to type in the path and when he/she closes my user form and opens it again, the path stays saved in the routine and does not bother user to type in the path every time user form is opened.

    Thank you very much!

    Public Path As Variant

    Path = InputBox(prompt:="Please, type in the path of a folder containing datalog files", Title:="Path settings")

    Wednesday, September 23, 2015 2:49 PM

All replies

  • Put the Public Path as Variant (should be String not Variant) statement at the top of a Module and its contents are maintained until you close the file or do a rest in the VBE.

    You can also use the SaveSetting and GetStting methods (see help) to safely save the path in the registry so the value survives until over-written and is available next time you open the project.

    Rod Gill
    Author of the one and only Project VBA Book

    Wednesday, September 23, 2015 9:15 PM
  • To supplement Rod's comments, you can use the BrowseforFolder function from to get the initial folder value, which will help eliminate user keying errors. The same link has functions to check whether a FolderExists and to CreateFolders if it doesn't.

    If you are using Rod's suggestion to use SaveSetting/GetSetting then when running your macro, check that the listed folder still exists and if it doesn't either reselect the folder or recreate it (or quit) as appropriate.

    Similarly you can check whether the FileExists before trying to use it and again introduce an exit strategy if it doesn't.

    Graham Mayor - Word MVP

    Thursday, September 24, 2015 4:27 AM
  • You could use the built-in FileDialog, which I've embedded into a function you can call from your code

    Function GetPath() As String
      Dim fdlg As FileDialog
      Dim vSelected As Variant
      Dim sPath As String
      Set fdlg = Application.FileDialog(msoFileDialogFolderPicker)
      With fdlg
        .InitialFileName = Environ("userprofile")
        If .Show = -1 Then
          GetPath = .SelectedItems(1)
        End If
      End With
    End Function

    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services, Inc.
    Peltier Tech Blog

    • Proposed as answer by ryguy72 Saturday, October 10, 2015 1:33 PM
    Monday, September 28, 2015 9:48 PM