CODEWITHSUNDEEP
excelvbaexcel-addins
Duraholiday
Duraholiday

Reputation: 111

Disabling VBA module after Save As function

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.

enter image description here

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.

Upvotes: 1

Views: 1667

Answers (1)

Zev Spitz
Zev Spitz

Reputation: 15317

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.)

Upvotes: 1

Related Questions