Corrected question. Input box to allow user to change The path: where Worksheet is saved

[Paulo H]

Hi

I have a sheet that is used by colleagues and has a module that saves the sheet to a set path By clicking on button for each person but to do that I have to change/add manually their Save path, then resend them a new template Each. I do not want to give Them access to the code

So i am trying to workout away of creating an input box(maybe via macro) that each user can use the master template and via input box enter their path to reset the path line to theirs, then they can Resave the template for each user.

ie open input box write in it their path where to save eg. C:\xxx\xxx the when they click ok it changes the path line in the code. Once this has been done they can save the sheet template and it keeps the new path line for them.

The Code is


the line I want to change via input box is underlined

Private Sub Saveonly_Click()

Dim dlgFile As FileDialog

Dim I As Integer

Dim ext As String

Dim fileName As String



Set dlgFile = Application.FileDialog(msoFileDialogSaveAs)

ext = "*."

fileName = "MyFileNameHere"

For I = 1 To dlgFile.Filters.Count

If dlgFile.Filters(I).Extensions = "*.xlsm" Then

dlgFile.FilterIndex = I

Exit For

End If

Next

With dlgFile

.InitialFileName = "C:\Users\freddy\OneDrive - Myshop Limited\Quote 2020\" & Range("B8").Value & " " & Range("B1").Value & " " & Range("C1").Value

If .Show = 0 Then

MsgBox "Quotation not saved!.", vbCritical

Exit Sub

End If

.Execute

End With

Tools.Hide

End Sub


thanks