Excel97 - Turn Off Project Protection

[SHardy]

I have an Excel97 file for which there is a function to create a summary file to send to Head Office.
At present this file replaces all formulas with values, and contains no code.

It has been requested that the print function is carried across to the copy file. However, the VBA project
in the main file is password protected to stop people playing, and this is stopping me from copying
the required code to the copy file.

Is there a way, via code, that I can export a module from a PASSWORD PROTECTED module? Or a way to unprotect
and then re-protect the project?

The code I was trying to use was:

Workbooks(ThisFile).VBProject.VBComponents("PrintUK&quot.Export "C:\Code.bas"
Workbooks(SaveUKFile).VBProject.VBComponents.Import("C:\Code.bas&quot.Name = "PrintUK"
Kill ("C:\Code.bas&quot

Thanks In Advance.

 

[DaleWatson123321]

Hi SHardy,

My offering: This is fairly simple example of code which could work for your application. It simply UnProtects the Sheet (or could be changed to Workbook) PRIOR to running a subroutine, and then Protects the Sheet (Workbook) AFTER.


Sub Hide_Rows_Start() 'hides rows
UnProtect 'unprotect sheet
Hide_Rows 'hide rows
Protect 'protect sheet
End Sub

Sub UnHide_Rows_Start() 'unhides rows
UnProtect 'unprotect sheet
UnHide_Rows 'unhide rows
Protect 'protect sheet
End Sub

Sub Protect()
ActiveSheet.Protect 123456 'unprotects sheet
End Sub

Sub UnProtect()
ActiveSheet.UnProtect 123456 'unprotects sheet
End Sub

Sub Hide_Rows() 'hides rows identified by range names
Range("range1&quot.EntireRow.Select 'selects "range1"
Selection.EntireRow.Hidden = True 'hides the range
Range("range2&quot.EntireRow.Select 'selects "range2"
Selection.EntireRow.Hidden = True 'hides the range
End Sub

Sub UnHide_Rows() 'unhides rows identified by range names
Range("range1&quot.EntireRow.Select 'selects "range1"
Selection.EntireRow.Hidden = False 'unhides the range
Range("range2&quot.EntireRow.Select 'selects "range2"
Selection.EntireRow.Hidden = False 'unhides the range
End Sub

I hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

Hi SHardy,

My offering: This is fairly simple example of code which could work for your application. It simply UnProtects the Sheet (or could be changed to Workbook) PRIOR to running a subroutine, and then Protects the Sheet (Workbook) AFTER.

Implicitly, this requires that you password-protect the Sheet/Workbook AND the VBA code


Sub Hide_Rows_Start() 'hides rows
UnProtect 'unprotect sheet
Hide_Rows 'hide rows
Protect 'protect sheet
End Sub

Sub UnHide_Rows_Start() 'unhides rows
UnProtect 'unprotect sheet
UnHide_Rows 'unhide rows
Protect 'protect sheet
End Sub

Sub Protect()
ActiveSheet.Protect 123456 'unprotects sheet
End Sub

Sub UnProtect()
ActiveSheet.UnProtect 123456 'unprotects sheet
End Sub

Sub Hide_Rows() 'hides rows identified by range names
Range("range1&quot.EntireRow.Select 'selects "range1"
Selection.EntireRow.Hidden = True 'hides the range
Range("range2&quot.EntireRow.Select 'selects "range2"
Selection.EntireRow.Hidden = True 'hides the range
End Sub

Sub UnHide_Rows() 'unhides rows identified by range names
Range("range1&quot.EntireRow.Select 'selects "range1"
Selection.EntireRow.Hidden = False 'unhides the range
Range("range2&quot.EntireRow.Select 'selects "range2"
Selection.EntireRow.Hidden = False 'unhides the range
End Sub

I hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

Footnote - for ALL Tek-Tips users...

I found out that the "Stop" option, which SHOULD work after selecting "Submit Post" DID NOT work properly. It DID "Stop", but STILL "SENT" the message.

You can see that I added the following:

Implicitly, this requires that you password-protect the Sheet/Workbook AND the VBA code

...Dale Watson

 

[SHardy]

Dale,

Thanks for your response. However, I think you may have misunderstood my question. It's not worksheets, or even the workbook, that I want to protect/unprotect. It is the VBA project, as I need to export one of the VBA modules. Your method, I expect, would've worked in previous versions of Excel where each module was inserted as a "Module Sheet", but I am using Excel97. Therefore the code can only be accessed through the "Visual Basic Editor". I had protected the project to stop any "playful" users making any changes to the code. It is only now that I have come across the requirement to export one of the modules.

Any thoughts on exporting a protected VBA module?

 

[SHardy]

As an addition, I have already been working with protected worksheets through my code. This got me to thinking:

You can hard code the password for worksheet & workbook protection into your VBA. Thus if your code isn't protected then it is possible for people to find out these passwords.

If you hard code a the VBA project protection password, then this can't be viewed by anyone as it is in protected code.

Therefore it would seem a bit strange to me that you couldn't access the code in this way, but I have not been able to find anything to suggest that it is possible.

 

[DaleWatson123321]

Hi SHardy,

...been a busy day for me - I'm finally getting back to this one.

What about the idea of creating a "template" file - into which you copy the "Print" code (& Print button), and then leave this in a "blank" condition.

Then, each time you need to send the summary data to Head Office, have your (modified) code transfer the data to the template file prior to send the template file to Head Office.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[SHardy]

Dale,

Thanks for getting back. I had thought of this solution, but it's not particularly viable in this situation. The main file is to be distributed to a large number of offices around the world. This would mean distributing the template also, ensuring that they ALL save the template to a file location that will exist on ALL their machines, and also ensuring that the template file is replaced after any hardware maintenance/ upgrades/ replacement within the office.

I think the way I might have to do it is to copy the code for the print function into a worksheet. When the save function runs it will create a blank module in the copyfile and copy the text from the worksheet to the module.

Any thoughts on this idea?

 

[DaleWatson123321]

Again a busy day, so I'm a little late on this response.

I believe your interpretation of my reference to a "template" file was NOT accurate. It seems you believed I was referring to a "template" file in the sense of a standard Excel or Word template file. This is NOT the case.

Instead, think of the "template" file as being a "copy" of the "same" file you would normally send - except that it would be BLANK - waiting for you to insert the data which you insert each time you send new data. And, of course, this template file will ALSO contain the required VBA code and Print-Button.

You should save the template file under a name which should ALWAYS remain the same - i.e. BLANK. Then, after opening this template file and inserting (copy/paste) the current data for emailing, save the file under a DIFFERENT name before emailing it. This way, the template file will ALWAYS remain BLANK.

I hope this clarifies my explanation. I always take the position that the onus is on the person providing the information to "explain it adequately" - NOT the person on the receiving end. So, I take responsibility for not having explained it more fully the first time. But the point is, IF you still have a misunderstanding of my description, DON'T hesitate to ask, as I still might not have explained it fully enough.

Please advise.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[SHardy]

Dale,

I understood what you meant. I have used this method before. However, regardless of the meaning the points I raised would be valid anyway. The offices that will be using the file are many and all over the world. There is no worldwide network. The offices are either stand alone or using there own office wide network. My point was that this would be, to the user, an "invisible" file. Therefore they would not know to make sure that it is ALWAYS present AND always in the same location. The main file would also need to know where to find this file. It would have to be in the same LOCAL location on ALL machines that the main file would be used on.

I have now put into place the other method that I suggested. I have copied the code text onto a hidden worksheet. When I create the copy file I then import this text as a new module. All seems to be working fine now with the main file's project protected again.

Thanks for all your help. Much appreciated.