VBA script not working when VBAProject Password set

[72clg]

I'm creating a user account request form which enables you to create a script to create the accounts. Everything is working fine. Now I want to protect the document so people can not see hidden sheets, click on cells, hide macros etc.


Protecting the sheets and workbook seems to be working fine but you can still see the macros in Developer > Visual Basic.

I've been told you can protect this by going into Visual Basic > Tools > VBAProject Properties > Protection and select Lock project for viewing (and adding a password). When I do this it does what I want by hiding the scripts, but it generates an error when running one of the macros.

3 of the macros work fine. When the button is clicked to create the script, it prompts me for the password as expected. It then creates the page with the script copied to it and opens up a save as window, I input the name and press save and I get:-

Run-time error '1004';
Method 'SaveAs' of object' _Workbook' failed​

The macro is below, is there anyway around this?

Sub CreateScript()
    Dim Password As String
    Do Until Password = "CREATESCRIPTPASSWORDHERE"
        Password = InputBox("Please enter password to enable you to save the script", "Password Required", "")
        If Password = "" Then
            Exit Sub
        End If
    Loop
Dim myFolder As String
'By Joe Was.
'Save Range as Text File.
    'ActiveSheet.Activate * This for button on same worksheet
    ActiveWorkbook.Unprotect ("PROTECTPASSWORDHERE")
    Worksheets("Script").Activate
    Range("A1:A1426").Select
    Selection.Copy
    'This temporarily adds a sheet named "CreatingScript"
    Sheets.Add.Name = "CreatingScript"
    Sheets("CreatingScript").Select
    ActiveSheet.Paste
    'Ask user for folder to save text file to.
    myFolder = Application.GetSaveAsFilename(fileFilter:="Text Files (*.bat), *.bat")
    'Save selected data as text file in users selected folder.
    ActiveWorkbook.SaveAs fileName:=myFolder, FileFormat:=xlTextPrinter, CreateBackup:=False
    'Remove temporary sheet.
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    'Indicate save action.
    MsgBox "Batch File: " & myFolder & " Saved!"
    'Go to top of sheet.
    Range("D6").Select
    ActiveWorkbook.Protect ("PROTECTPASSWORDHERE")
End Sub

Is there anyway I can hide the VBA scripts and still have them working?

 

[RoryA]

Have you tried adding a new workbook and pasting the script there (or just writing it straight to a text file), instead of adding a sheet to the protected workbook?

 

[72clg]

Have you tried adding a new workbook and pasting the script there (or just writing it straight to a text file), instead of adding a sheet to the protected workbook?

No I haven't, but that could because I'm a newbee and wouldn't know how to export the sheet straight into a text file (batch file)?

 

[hippiehacker]

you can use

Option Private Module

to hide the macros from the list

 

[72clg]

Maybe I'm doing it wrong, but I've put Option Private Module at the top of all my modules, but you can still see the macros by going into Developer > Visual Basic