Prompt To Allow User To Edit VBA

[data808]

I'm not sure if this is possible but I have some VBA assigned to a button. If the user clicks the button it will auto name the file and save it to a specific location on a network drive. In the future if a folder name is changed, the file will not be able to auto save anymore. When the user runs into this scenario I would like a prompt in excel to let them know that the directory changed and if they can browse and redirect it to the new location. Then I would also like that new location to be updated in the VBA code so that it will continue to save there until this scenario happens again.

Is this possible? If so, how do you set it up to do this?

The reason why I want to do it this way too is because I also have some auto fill cells tied to VBA that will auto fill their name and profile details when they type into a cell. If the employee were to get married and last name changes, I would want the user to be able to click a button to create a prompt to change a line in the VBA code to reflect their last name change. That why the next time they type into the cell it will auto fill their new last name.

Thanks in advance.

 

[RoryA]

You would be much better off reading the path from a cell or defined name in the workbook. It is much easier to simply edit that than to automate editing your code.

 

[Joe4]

You would be much better off reading the path from a cell or defined name in the workbook. It is much easier to simply edit that than to automate editing your code.

I agree. I sometimes do this, and just store the values in cells somewhere.
You can even hide the sheets/cells if you do not want them visible to the user.

 

[data808]

I agree. I sometimes do this, and just store the values in cells somewhere.
You can even hide the sheets/cells if you do not want them visible to the user.

True. You guys do have a good point. I just wanted something that the user could fix themselves so I don't have to keep making changes to the VBA. I wanted to make it really user friendly with prompts that explain and tell the user what to type. I was going to even put a button for name changes if they get married kind of thing.

I guess I could hide a cell value on another sheet and also hide that sheet and create a button that they can click on to unhide it and bring the cursor to that cell with the name value so they can make the change. Then once they press enter to finalize their updated value it will hide the sheet and bring the cursor back to their original log sheet.

The one they might not understand how to do is the new directory location. Not all of the users would be good at that and a browse for location dialogue window would be easier. Is something like that possible to summon up for the user to click browse and choose the new location to enter that directory value into the cell for them?

 

[Joe4]

True. You guys do have a good point. I just wanted something that the user could fix themselves so I don't have to keep making changes to the VBA. I wanted to make it really user friendly with prompts that explain and tell the user what to type. I was going to even put a button for name changes if they get married kind of thing.

There is no reason you can't have VBA do all that. You can have VBA prompt them for whatever you need, and then have VBA write the needed values to the hidden cells. And then have your other VBA reference those stores values dynamically. So the VBA code would never need to be changed, because no hard-coded values are stored in it. They are all on the sheet.

The one they might not understand how to do is the new directory location. Not all of the users would be good at that and a browse for location dialogue window would be easier. Is something like that possible to summon up for the user to click browse and choose the new location to enter that directory value into the cell for them?

You could use something like what Fluff shows here: VBA Save As with User prompted to select folder path
Then, instead of using "Fldr" in a SaveAs command, like in the last line of the code, you could just store the value in a cell, i.e.

Range("M2").Value = Fldr

so you can reference that cell later in your other procedures.