Hide VBA code in new workbook

Anonymous
2014-10-18T22:34:54+00:00

Hi,

I'd like to hide VBA code in new workbook.

I have Master workbook and this is protected by password.

When I protect the Master workbook, then the new workbook that I copy from Master workbook is protected too, but not VBA code in new workbook.

I can see view code when I click the Tab name on the bottom.

How can I hide the VBA or Macro in Developer Tab and View Code in Tab name on the bottom of excel?

What should I do for those buttons to be gray out?

I want to apply password in the Master workbook before making the new workbook because the new workbook from Master workbook can't be controlled by me.

Thanks

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2014-10-19T17:54:10+00:00

Hi Tobemaster,

Set mywb = Thisworkbook

Set NewWb = Activeworkbook

 fname = Application.GetSaveAsFilename

' long codes here

mywb.Save

NewWb.SaveAs fName,FileFormat:=FileFormatValue, CreateBackup:=False

I used above code.

Is this the wrong way?

If you create the NewWb  directly from the Master work book, then it will inherit the VBA protection which you  previously applied to the Master workbook. As an example, if the new workbook (destWB) is created as shown in the following code, then it will inherit the VBA protection status of the Master workbook (srcWB):

'=========>>

Option Explicit

'--------->>

Public Sub Tester()

    Dim srcWB As Workbook, destWB As Workbook

    Dim sFilename As String, sStr As String

    Const sPath As String = "**C:\MyFolder**"                                  '<<=== Change

    Const srcWbName As String = "ToBeMaster.xlsm"                 '<<=== Change

    Const destWbName As String = "Distribution.xlsm"              '<<=== Change

    Set srcWB = Workbooks(srcWbName)

    sStr = Application.PathSeparator

    If Right(sPath, 1) = sStr Then

        sFilename = sPath & destWbName

    Else

        sFilename = sPath & sStr & destWbName

    End If

    srcWB.SaveCopyAs (sFilename)

    Set destWB = Workbooks.Open(sFilename)

    With destWB

        '\ your code

        .Close SaveChanges:=True

    End With

End Sub

'<<=========

If, however, the NewWb already exists and was not created in analogous manner from the Master workbook, then you will have to open it and apply the VBA protection manually as shown by Hans. 

===

Regards,

Norman

Was this answer helpful?

0 comments

37 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-10-19T07:22:44+00:00

    I copied from the Master and paste some sheets , then use code below;

    ActiveWorkbook.Save As Filename:= "special file name".xlsm

    Is this correct?

    Thanks

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-10-19T05:53:40+00:00

    Hi Tobemaster.

    I must be wrong, but I can still see the code.

    This is driving me crazy.

    I made 3 command buttons(Active X) in Develop tab in Master.

    I copy and paste some worksheets , then made new workbook.

    These 3 command buttons copied from Master.

    In VBA editor, some sheets in  Microsoft Excel objects, I can see codes for these buttons.

    Your description is not clear but, for the new workbook to inherit the VBA protection, it must be created from the Master workbook using the SaveAs command. If you are instead copying individual sheets from the Master workbook to a new workbook, this will not automatically add the protection which you seek.

    ===

    Regards,

    Norman

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-10-19T03:57:24+00:00

    Thank you for your kind answer.

    I must be wrong, but I can still see the code.

    This is driving me crazy.

    I made 3 command buttons(Active X) in Develop tab in Master.

    I copy and paste some worksheets , then made new workbook.

    These 3 command buttons copied from Master.

    In VBA editor, some sheets in  Microsoft Excel objects, I can see codes for these buttons.

    Thanks

    Was this answer helpful?

    0 comments
  4. Anonymous
    2014-10-19T02:22:01+00:00

    The VBA code will remain visible during the session that you go through the steps that Hans V provided.  But when you close it and reopen it later and try to access the VBA code, you should be prompted to enter the password before it is made visible to you.

    I just double-checked and it all works as advertised so far: even a copy of the workbook I made with the VBA project "exposed" via entering the password was locked when I opened it later.

    Was this answer helpful?

    0 comments