I’m finished with my project and I need to secure it with something. I want to use XSL Padlock but I’m getting flustered with so many options. I’ve spent a week going over the best option and I still don’t know of any complete solutions.
I have a fully automated workbook. It relies on data connections, the data connections can be changed and new information will be provided. It is important for users to be able to edit and add connections to pages if they need in the future. However this means that the workbook is more or less a dashboard style template. Without specific protection the workbook would easily be stolen and copied. Even with VBA and formula protection it could be copied and work without a problem, just by changing connections. Locking the sheets won’t allow updating or adding connections, so sheet protection isn’t really an option. Hardware locking is an option, but is really inconvenient for users and myself, since I don’t have a webserver to host hardware locked keys. I’d have to complete a sale, update connections, send workbook, get customer to give system ID, manually generate key, and send it out every time. One time activation does not work because an activated file can be copied and connections could be updated to work normally. Is hardware locking my only option here? I would also like to send the .exe through email but that does not seem to be an option either. Is drobox the only delivery option? I would have to make a new drop box submission every time. That would be a pain vs. email. These are custom workbooks tailored for each individual. Not just a one copy for all workbook.
I’m also having difficulties with the VBA compiler. My VBA is mostly based on a Microsoft application library and other simple macro recordings, however nothing compiles. The directions are ok but do not go into detail about how to make it work. I’m not too good with VBA. I googled everything and pieced it together one command at a time, for like a month. I don’t know what changes to make to get it to compile without specific step by step instructions in layman’s terms. I keep getting Syntax error on basically every line. Is it ok to not use the VBA compiler and just restrict the access to the VBA? Am I doing something wrong?
I also have the workbook save after every refresh using (ActiveWorkbook.save). However this doesn’t seem to work. The customer should never have to save themselves, and they shouldn’t be making other save files outside the original .exe. So saving should be done every refresh and when closing to the original .exe . I want it to save every update in case of a crash so automated entries don’t get out of place. I need something between “Save changes automatically and load them without prompt next time” and auto saves. Is this possible? What would I change the code to? I would want it to auto save to wherever the customer has the workbook located and not a specific location like (MyApp.exe "D:\My Documents\123.xlsc"). I would assume most customers would keep this workbook on their desktop but you never know what they will do, if there files are under drive C or D or what not. I assume id use the get current workbook save file path, but how could that be applied to a function that looks up and saves?
I will include formula protection, and enable “do not allow loading/saving other workbooks (even with VBA Code). Is there anything else I’m missing?
I’m really lost here. I’ve worked on this for over a month now and I’m getting discouraged by these options and finalizing. Everything works great as is, but I really need to get some protection set up properly.