Disabling VBA module after Save As function

Question

When the template is opened it runs an Auto_open module.

 Sub Auto_Open()

 SaveAsUserForm.Show

 End Sub

This then brings up a userform that says please save as and a Ok command button.

When Ok is clicked it runs this code.

Private Sub SaveAs_Click()

Dim bFileSaveAs As Boolean
bFileSaveAs = Application.Dialogs(xlDialogSaveAs).Show

If Not bFileSaveAs Then Unload Me

If bFileSaveAs Then    
    Dim x As Object
    Set x = Application.VBE.ActiveVBProject.VBComponents
    x.Remove VBComponent:=x.Item("Auto_Open")

    Unload Me

End Sub

This deletes the Auto_Open module so the save as user form doesn't pop up in the new file that was just saved. But I only want it disabled if the save as function is used (both using the auto open module or in the file tab

I also need it so if the user cancels the box it will disable the save function completely and only allow save as. But if the save function is used it ask for a password, so i can edit the template.

So basically when the user opens the template he cant do or change anything unless he SaveAs first, Then once its Saved As and the file name changes it will disable the Auto_Open module so it doesn't ask to save as every time the new file is opened.

Have you considered checking the filename and path and only showing the message if it's the "oginial" path? Or if you mark the template as "read only" then your users will always need to "save as"... — Tim Williams
– Tim Williams, Commented Aug 19, 2015 at 17:00
Marking it read as will ask them to enable macros, Simply need a statement that says If file saved As Then remove the auto_open macro and if save function is used Then ask for password — Duraholiday
– Duraholiday, Commented Aug 19, 2015 at 18:48
Some code here you could use: mrexcel.com/forum/excel-questions/… — Tim Williams
– Tim Williams, Commented Aug 19, 2015 at 18:52
I don't think that helps me any, I've actually used alot of that code with the other modules — Duraholiday
– Duraholiday, Commented Aug 19, 2015 at 19:11
Yes - my bad: did not read your code completely. You're not checking the value of bFileSaveAs before deleting the code though ? — Tim Williams
– Tim Williams, Commented Aug 19, 2015 at 19:22

user20416 · Accepted Answer · 2015-08-19 17:23:30Z

1

You can modify the code at runtime. You can use the ReplaceLine method to replace the line calling SaveAsUserForm.Show with a comment:

Dim mdl As CodeModule
Set mdl = x.Item("Auto_Open")
mdl.ReplaceLine(3, "'SaveAsUserForm.Show")

Caveat: If the line number to replace ever changes, this code will overwrite the new line. I suggest finding the line number of the text in question, and then using ReplaceLine with that line number.

(This should be straightforward, but it isn't. Apparently, you have to get the count of lines in the module, read all the lines in the module by passing in the count of lines, split the text by line, and find the line with the matching text.)

edited Aug 19, 2015 at 17:23

answered Aug 19, 2015 at 17:07

user20416

15.5k9 gold badges76 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Duraholiday Over a year ago

I have nothing to replace the Auto_Open module with, and its only job is to prompt the userform. I looked into using the before save but that would try to remove the Auto_Open every time someone saved the new file and it would error out because it already removed it the first time it SavedAs. This did work for another excel project so thank you for that!

user20416 Over a year ago

@Duraholiday I don't mean to replace the entire module, just comment out the line that calls SaveAsUserForm.Show.

Collectives™ on Stack Overflow

Disabling VBA module after Save As function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related