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. HansV 460.5K Reputation points MVP Volunteer Moderator
    2014-10-19T16:44:51+00:00

    How do you create NewWb?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-10-19T15:42:21+00:00

    Thanks.

    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?

    Thanks

    Was this answer helpful?

    0 comments
  3. HansV 460.5K Reputation points MVP Volunteer Moderator
    2014-10-19T08:57:10+00:00

    I'd create a COPY of the master workbook like this:

    ThisWorkbook.SaveCopyAs "path+filename.xlsm"

    You can then open this copy, add or remove sheets and make other changes as needed, and finally save and close it again.

    Since the new workbook has been created as a direct copy of the master workbook, it will inherit the VBA code protection.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2014-10-19T08:29:04+00:00

    Hi Tobemaster,

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

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

    Is this correct?

    Was the workbook which you are saving previously created by saving the Master book?

    If not, then the mere act of copying one or more worksheets from the Master workbook to the second workbook will not confer the Master workbook's protection to the second workbook

    If the second workbook was not created as a SaveAs operation from the Master workbook, you will need to perform the VBA protection operation in the new workbook.

    ===

    Regards,

    Norman

    Was this answer helpful?

    0 comments