Hide VBA code in new workbook

Anonymous

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

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

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

0 comments

37 additional answers

Anonymous

2014-10-20T16:29:18+00:00

Sorry.

In the beginning of code, I used ;

fname = Application.GetSaveAsFilename(InitialFileName:=Thisworkbook.Sheets("Sheet1").Range("A1").Value)

If fname <> False Then

'my code

Response = MsgBox ("Do you want to open the new workbook?", vbYesNo)

If Response = vbYes Then

Workbooks.Open (fname)

End IF

End if

I got the error on the bold row. The new workbook is opened but it says "Subscription out of range"

Thanks
Was this answer helpful?

0 comments No comments
Anonymous

2014-10-20T16:15:58+00:00

Hi Tobemaster.

Thanks again!!!

I asked Hans too, but when I tried to open the new workbook, I got error "Subcription out of"

I think this means some name is not correct, but the new workbook is anyway opened.

Can I ignore this error just adding 'On Error resume next'?

Or is my cord wrong?

When you encounter the error, what is the full name of the file (i.e path and filename) and what is the value of fname variable? To get the fname value, type ?fnam e in the Immediate window and then tap the Enter key.

===

Regards,

Norman
Was this answer helpful?

0 comments No comments
Anonymous

2014-10-20T15:57:14+00:00

Thanks again!!!

I asked Hans too, but when I tried to open the new workbook, I got error "Subcription out of"

I think this means some name is not correct, but the new workbook is anyway opened.

Can I ignore this error just adding 'On Error resume next'?

Or is my cord wrong?

Thanks
Was this answer helpful?

0 comments No comments
Anonymous

2014-10-20T15:41:28+00:00

Thanks you very much!!!

I can hide macros in View tab.:)

I found when I want to debug, I have to erase that argument then, I can debug.

At the end of my code, I want to open the new workbook to make sure everything is correct, how can I open the new workbook?

(I'm sorry I ask again.)

I added;

Workbooks.Open Filename:=fname

But it says "Subscription our of".

Thanks
Was this answer helpful?

0 comments No comments