PDA

View Full Version : Sleeper: Securing workbook as an add-in



Jacko_B
09-03-2005, 09:27 PM
I have read that the best way of securing a workbook is to save it as an add-in, then load it into Excel. I do not wish users to access my underlying macros ( by using Alt-F8) and accessing my hidden worksheets.

My workbook has several worksheets, and the user input is on the first "Input" sheet. It then proceeds to other sheets to calculate output after a Commandbutton on the "Input" Sheet is clicked.

I have password protected the VBA Project, and also the hidden worksheets, and save the workbook as an .xla file.

I then re-started Excel, and loaded the new add-in via Tools/Macro.

Then there is nothing in Excel. I cannot see any of the worksheets in the add-in.

Can anyone please explain where I have gone wrong?:help

MOS MASTER
09-04-2005, 06:50 AM
Welcome to VBAX! :hi:

You're questione is in the Access forum but I think this is a Excel question so I'll move it to the Excel forum. :whistle:

Justinlabenne
09-04-2005, 08:58 AM
Helpful Links:
Creating an Add-in (http://www.bettersolutions.com/excel/6122425999101012119000118771245/3152320182124192081520918771245.htm)
Facts about add-ins (http://www.bettersolutions.com/excel/6122425999101012119000118771245/1223221312122271002023202415205.htm)

Jacko_B
09-04-2005, 02:03 PM
Thanks Joost. I am sorry about the wrong posting area -my apologies.

Justin, the links you provided gave me the clue ast o what I was doing wrong. When you are installing an addin as a wrokbook, you need to change the IsAddin property of ThisWorkbook to False. Many thanks. That was also a great site for a lot of good add-ins.

royUK
09-05-2005, 11:10 AM
Not sure if I understand your problem, but

Then there is nothing in Excel. I cannot see any of the worksheets in the add-in. This is how an addin works. It makes the workbook "invisible", but the code is usable in any workbook when the addin is installed.
If it is security that is the problem, then remember that Excel is not a secure environment. However, it is sufficient to deter most people by making Sheets VeryHidden and then protect your VBA Code by using Tools | VBAProject Properties | Protection. Make sure you check Lock Project for Viewing and add a password. Close Excel and re-open. Now your VBA is hidden without the password. This is not 100% secure, but is usually sufficient and more secure than WorkSheet protection. If you haven't protected your addin's code it will still be accessible.

MOS MASTER
09-05-2005, 11:12 AM
Thanks Joost. I am sorry about the wrong posting area -my apologies.


No apolgies needed. You're welcome! :yes

VickyB
09-05-2005, 01:39 PM
Thanks RoyUK, I appreciate this information. I have done what you suggested, and even found some code that disabled the Tools/Macro menu bar, but hitting Alt-F8 still lets the user get into my macros which are all Public.

As you said, Excel is not really a secure environment.

I also found a MS Knowledge Base articel which shows how to convert an Add-in file to a workbook. You still have to unprotect the VBA code to do this :( However, the add-in remains safe, but users can access everything in the workbook, so you still have to take your recommended steps after the add-in is converted to a workbook. Why bother?

http://support.microsoft.com/?kbid=211561

Thanks for all your help

My regards,
Vicky

XL-Dennis
09-05-2005, 01:50 PM
Hi folks :)

# We can consider to use MS VB 6.0 to create COM add-ins, which are more secure then the native Excel add-ins:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoxpta/html/odc_comaddinvb6.asp

# If the tools only consist of user defined functions then we may consider to create XLL add-ins:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q178474

# If we want to be in line with "state of the art" we may consider to create COM add-ins with VB.NET and use Stronge Names:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q302896

# If we want to have 100 % security and a total guarante for full protection then don't use any computer should be a good start ;)

Kind regards,
Dennis

royUK
09-05-2005, 03:06 PM
Vicky, I don't think you have protected your addin correctly, if you have done as i suggested & saved the changes the VBA should not be accessible.