Ask user where to store file one time

[Pookiemeister]

I am wanting to ask the user where they would want to store a file, but I only want to ask this question one time, and this would be when they first use this program. Any help would be greatly appreciated. I thought about using a time and date stamp and store it in a variable. So, each time the user opens this form, the vba code will check to see if there is a value in that variable and if so, don't ask that question, otherwise ask where they want to store this file. Is this a good approach or is there a better and hopefully not to complicated way. Thank you.

 

[Tetra201]

Maybe

...
If GetSetting("MyApp", "Startup", "AlreadyUsed") <> "Yes" Then
'   your file-saving section
    If '***saving operation is successful***' Then
        SaveSetting "MyApp", "Startup", "AlreadyUsed", "Yes"
    End If
End If
...

 

[Jeffrey Mahoney]

Storing something in a variable only lasts until the VBA project has been reset. Creating a very hidden sheet and and putting in either a named range or just cell A1 is more permanent

You could also have a table that saves each user's response.
Environ("UserName") gets the user's Login ID

Sub AskUSerForPathName()
  Debug.Print GetSaveLocation
End Sub

Function GetSaveLocation() As String
  Dim v As Variant
  Dim Fltr As String
  Dim PathName As String
  Dim Cel As Range
  Dim Rng As Range
  Dim UID As String
  
  UID = Environ("UserName")
  Set Cel = Sheets("Hidden").Range("UserChoice").Resize(1, 1).Offset(1, 0)
  Set Rng = Sheets("Hidden").Range(Cel, Cel.Offset(1000000, 0).End(xlUp))
  For Each Cel In Rng
    If Cel.Value = UID Then
      PathName = Cel.Offset(0, 1).Value
      Exit For
    End If
  Next Cel
  
  If PathName <> "" Then
    GetSaveLocation = PathName
    Exit Function
  End If
  
  
  Fltr = "Excel Files (*.xlsx;*.xlsm), *.xlsx;*.xlsm"
  v = Application.GetSaveAsFilename(, Fltr, 1, "Choose location and filename")
  If v <> False Then
    PathName = v
    Set Cel = Sheets("Hidden").Range("UserChoice").Resize(1, 1).Offset(1000000, 0).End(xlUp).Offset(1, 0)
    Cel.Value = UID
    Cel.Offset(0, 1).Value = PathName
    GetSaveLocation = PathName
  End If
    
  
End Function

Book3
	E	F
1	User ID	PathFile
2	x12477	C:\Users\x12477\OneDrive - ALLWK\Documents\Book3.xlsx
Hidden