Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot lock project
In XL2K, after closing all module windows, I "lock project for viewing" and
password protect my addin. Save from the vbe, close XL. When I reopen XL and my addin, the project is not locked. I go back into Project properties / Protection and the lock check box and password dialogs are empty. If I now tick the lock checkbox I'm asked for my password (although no asterisks appear in the password textbox's). Enter password, everything seems normal. Save, close, reopen and back to the same situation. The project is "clean" and correctly compiled. Any ideas how to resolve without rebuilding the addin appreciated. Regards, Peter T |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot lock project
My bet is that you're opening a different file than you're saving.
Make sure that the addin's project is active before saving. And as a workaround (I've never had the trouble you describe), you can change the workbook's .addin property to false. (Select your project, hit F4 to see properties and look for IsAddin.) Then you can go back to excel and save that workbook as an addin (remember to change the filetype) via that interface. ===== Another way while in the VBE: hit ctrl-g (to see the immediate window): type this and hit enter: workbooks("youraddinname.xla").save Maybe this would help, too (also in the immediate window): ?workbooks("youraddinname.xla").fullname (Just to verify that you're where you think you are.) Peter T wrote: In XL2K, after closing all module windows, I "lock project for viewing" and password protect my addin. Save from the vbe, close XL. When I reopen XL and my addin, the project is not locked. I go back into Project properties / Protection and the lock check box and password dialogs are empty. If I now tick the lock checkbox I'm asked for my password (although no asterisks appear in the password textbox's). Enter password, everything seems normal. Save, close, reopen and back to the same situation. The project is "clean" and correctly compiled. Any ideas how to resolve without rebuilding the addin appreciated. Regards, Peter T -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot lock project
Hi Dave, thanks for responding. Comments in line -
My bet is that you're opening a different file than you're saving. No, definately working with the same file. But I'd better not be that dogmatic (see later). Make sure that the addin's project is active before saving. Yes always active, selected in vbe project's panel, then save from vbe. And as a workaround (I've never had the trouble you describe), you can change the workbook's .addin property to false. (Select your project, hit F4 to see properties and look for IsAddin.) I've tried locking & saving with IsAddin True (from vbe) and False (from both Excel & vbe) Then you can go back to excel and save that workbook as an addin (remember to change the filetype) via that interface. Similar to one step above. But also locked what was an xls with SaveAs xla while still a visible workbook. I've also tried copying the file to different folders, renaming, trying each with different passwords (only one open at a time). Each time it/they re-open unlocked. A password obviously exists though no asteriks, after entering the correct pw for that file I can tick the Lock checkbox. Check Fullname each time. Repeat the save, all to no avail. Also emptied temporary folders and repeated in new sessions of Excel and even Windows. Another way while in the VBE: hit ctrl-g (to see the immediate window): type this and hit enter: workbooks("youraddinname.xla").save Well blow me down! that's just worked !! Bit of a mystery but thank you very much. Regards, Peter T Peter T wrote: In XL2K, after closing all module windows, I "lock project for viewing" and password protect my addin. Save from the vbe, close XL. When I reopen XL and my addin, the project is not locked. I go back into Project properties / Protection and the lock check box and password dialogs are empty. If I now tick the lock checkbox I'm asked for my password (although no asterisks appear in the password textbox's). Enter password, everything seems normal. Save, close, reopen and back to the same situation. The project is "clean" and correctly compiled. Any ideas how to resolve without rebuilding the addin appreciated. Regards, Peter T -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot lock project
I'm glad the workaround worked ok for you.
I've never had a problem saving from the VBE, but I've read a few posts where the authors say they avoid it because of problems they've had. In a different forum, Thomas Ramel posted some code that he adds to an addin. You may like it: ========================= From Thomas Ramel: Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.Saved = True Then Exit Sub If MsgBox("Save changes in Add-In" & Chr(13) & Chr(13) & _ UCase(ThisWorkbook.VBProject.Name) & Chr(13) & Chr(13) & _ "?", vbYesNo) = vbYes Then Application.DisplayAlerts = False ThisWorkbook.Save Application.DisplayAlerts = True End If End Sub Now every time I changed something in the Code and forgot to save it manually I get a reminder, wehe I close Excel. Regards Thomas Ramel Peter T wrote: Hi Dave, thanks for responding. Comments in line - My bet is that you're opening a different file than you're saving. No, definately working with the same file. But I'd better not be that dogmatic (see later). Make sure that the addin's project is active before saving. Yes always active, selected in vbe project's panel, then save from vbe. And as a workaround (I've never had the trouble you describe), you can change the workbook's .addin property to false. (Select your project, hit F4 to see properties and look for IsAddin.) I've tried locking & saving with IsAddin True (from vbe) and False (from both Excel & vbe) Then you can go back to excel and save that workbook as an addin (remember to change the filetype) via that interface. Similar to one step above. But also locked what was an xls with SaveAs xla while still a visible workbook. I've also tried copying the file to different folders, renaming, trying each with different passwords (only one open at a time). Each time it/they re-open unlocked. A password obviously exists though no asteriks, after entering the correct pw for that file I can tick the Lock checkbox. Check Fullname each time. Repeat the save, all to no avail. Also emptied temporary folders and repeated in new sessions of Excel and even Windows. Another way while in the VBE: hit ctrl-g (to see the immediate window): type this and hit enter: workbooks("youraddinname.xla").save Well blow me down! that's just worked !! Bit of a mystery but thank you very much. Regards, Peter T Peter T wrote: In XL2K, after closing all module windows, I "lock project for viewing" and password protect my addin. Save from the vbe, close XL. When I reopen XL and my addin, the project is not locked. I go back into Project properties / Protection and the lock check box and password dialogs are empty. If I now tick the lock checkbox I'm asked for my password (although no asterisks appear in the password textbox's). Enter password, everything seems normal. Save, close, reopen and back to the same situation. The project is "clean" and correctly compiled. Any ideas how to resolve without rebuilding the addin appreciated. Regards, Peter T -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot lock project
"Dave Peterson" wrote in message
I'm glad the workaround worked ok for you. Not as glad as I am, thanks again! I've never had a problem saving from the VBE, but I've read a few posts where the authors say they avoid it because of problems they've had. Yes I've also read similar and don't normally save from the VBE. The problem though couldn't be fixed by saving changing IsAddin to false, locking, then saving in Excel. However the problem commenced having saved in the VBE, from then on couldn't be fixed. In my development project I already have something similar to Thomas Ramel's code (below), it does other things to. But I had removed pending distribution. ============== FWIW, an unexpected observation - The normal size of my old project varied from 1.4mb (no code ever run) to 2.2mb. In both cases just after using Rob Bovey's Codecleaner to strip and clean, the larger file size if I then do a full compile and save. But after saving both types of files from the immediate window the respective file sizes reduced by about 300kb. Seemed odd so I built a brand new identical file with similar sheets and dragged modules from the old file to the new. The new saves to the same lower file sizes respectively. Me thought - must be some garbage built up in the old worksheets, even though the project is mainly code and little in the way of worksheet stuff. I removed all modules from the old file and looked at file size. Very small and just as expected to account for the sheets, about 100kb. Bearing in mind the old project was freshly stripped and cleaned, I'm curious as to what that phantom 300kb accounted for. Even more curious as to why this 300Kb suddenly disappeared ONLY because I saved from the immediate window. Replicates with back-ups of old non-problematic files. Regards, Peter T In a different forum, Thomas Ramel posted some code that he adds to an addin. You may like it: ========================= From Thomas Ramel: Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.Saved = True Then Exit Sub If MsgBox("Save changes in Add-In" & Chr(13) & Chr(13) & _ UCase(ThisWorkbook.VBProject.Name) & Chr(13) & Chr(13) & _ "?", vbYesNo) = vbYes Then Application.DisplayAlerts = False ThisWorkbook.Save Application.DisplayAlerts = True End If End Sub Now every time I changed something in the Code and forgot to save it manually I get a reminder, wehe I close Excel. Regards Thomas Ramel Peter T wrote: Hi Dave, thanks for responding. Comments in line - My bet is that you're opening a different file than you're saving. No, definately working with the same file. But I'd better not be that dogmatic (see later). Make sure that the addin's project is active before saving. Yes always active, selected in vbe project's panel, then save from vbe. And as a workaround (I've never had the trouble you describe), you can change the workbook's .addin property to false. (Select your project, hit F4 to see properties and look for IsAddin.) I've tried locking & saving with IsAddin True (from vbe) and False (from both Excel & vbe) Then you can go back to excel and save that workbook as an addin (remember to change the filetype) via that interface. Similar to one step above. But also locked what was an xls with SaveAs xla while still a visible workbook. I've also tried copying the file to different folders, renaming, trying each with different passwords (only one open at a time). Each time it/they re-open unlocked. A password obviously exists though no asteriks, after entering the correct pw for that file I can tick the Lock checkbox. Check Fullname each time. Repeat the save, all to no avail. Also emptied temporary folders and repeated in new sessions of Excel and even Windows. Another way while in the VBE: hit ctrl-g (to see the immediate window): type this and hit enter: workbooks("youraddinname.xla").save Well blow me down! that's just worked !! Bit of a mystery but thank you very much. Regards, Peter T Peter T wrote: In XL2K, after closing all module windows, I "lock project for viewing" and password protect my addin. Save from the vbe, close XL. When I reopen XL and my addin, the project is not locked. I go back into Project properties / Protection and the lock check box and password dialogs are empty. If I now tick the lock checkbox I'm asked for my password (although no asterisks appear in the password textbox's). Enter password, everything seems normal. Save, close, reopen and back to the same situation. The project is "clean" and correctly compiled. Any ideas how to resolve without rebuilding the addin appreciated. Regards, Peter T -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot lock project
You mentioned that you had changed versions of Excel in you testing. I
notice file size changes in different versions of excel. I assume you have eliminated this as a possible cause. another thought is if you are comparing protected to unprotected. -- Regards, Tom Ogilvy "Peter T" <peter_t@discussions wrote in message ... "Dave Peterson" wrote in message I'm glad the workaround worked ok for you. Not as glad as I am, thanks again! I've never had a problem saving from the VBE, but I've read a few posts where the authors say they avoid it because of problems they've had. Yes I've also read similar and don't normally save from the VBE. The problem though couldn't be fixed by saving changing IsAddin to false, locking, then saving in Excel. However the problem commenced having saved in the VBE, from then on couldn't be fixed. In my development project I already have something similar to Thomas Ramel's code (below), it does other things to. But I had removed pending distribution. ============== FWIW, an unexpected observation - The normal size of my old project varied from 1.4mb (no code ever run) to 2.2mb. In both cases just after using Rob Bovey's Codecleaner to strip and clean, the larger file size if I then do a full compile and save. But after saving both types of files from the immediate window the respective file sizes reduced by about 300kb. Seemed odd so I built a brand new identical file with similar sheets and dragged modules from the old file to the new. The new saves to the same lower file sizes respectively. Me thought - must be some garbage built up in the old worksheets, even though the project is mainly code and little in the way of worksheet stuff. I removed all modules from the old file and looked at file size. Very small and just as expected to account for the sheets, about 100kb. Bearing in mind the old project was freshly stripped and cleaned, I'm curious as to what that phantom 300kb accounted for. Even more curious as to why this 300Kb suddenly disappeared ONLY because I saved from the immediate window. Replicates with back-ups of old non-problematic files. Regards, Peter T In a different forum, Thomas Ramel posted some code that he adds to an addin. You may like it: ========================= From Thomas Ramel: Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.Saved = True Then Exit Sub If MsgBox("Save changes in Add-In" & Chr(13) & Chr(13) & _ UCase(ThisWorkbook.VBProject.Name) & Chr(13) & Chr(13) & _ "?", vbYesNo) = vbYes Then Application.DisplayAlerts = False ThisWorkbook.Save Application.DisplayAlerts = True End If End Sub Now every time I changed something in the Code and forgot to save it manually I get a reminder, wehe I close Excel. Regards Thomas Ramel Peter T wrote: Hi Dave, thanks for responding. Comments in line - My bet is that you're opening a different file than you're saving. No, definately working with the same file. But I'd better not be that dogmatic (see later). Make sure that the addin's project is active before saving. Yes always active, selected in vbe project's panel, then save from vbe. And as a workaround (I've never had the trouble you describe), you can change the workbook's .addin property to false. (Select your project, hit F4 to see properties and look for IsAddin.) I've tried locking & saving with IsAddin True (from vbe) and False (from both Excel & vbe) Then you can go back to excel and save that workbook as an addin (remember to change the filetype) via that interface. Similar to one step above. But also locked what was an xls with SaveAs xla while still a visible workbook. I've also tried copying the file to different folders, renaming, trying each with different passwords (only one open at a time). Each time it/they re-open unlocked. A password obviously exists though no asteriks, after entering the correct pw for that file I can tick the Lock checkbox. Check Fullname each time. Repeat the save, all to no avail. Also emptied temporary folders and repeated in new sessions of Excel and even Windows. Another way while in the VBE: hit ctrl-g (to see the immediate window): type this and hit enter: workbooks("youraddinname.xla").save Well blow me down! that's just worked !! Bit of a mystery but thank you very much. Regards, Peter T Peter T wrote: In XL2K, after closing all module windows, I "lock project for viewing" and password protect my addin. Save from the vbe, close XL. When I reopen XL and my addin, the project is not locked. I go back into Project properties / Protection and the lock check box and password dialogs are empty. If I now tick the lock checkbox I'm asked for my password (although no asterisks appear in the password textbox's). Enter password, everything seems normal. Save, close, reopen and back to the same situation. The project is "clean" and correctly compiled. Any ideas how to resolve without rebuilding the addin appreciated. Regards, Peter T -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot lock project
I've seen changes in filesizes (sometimes minor changes make the filesize go up,
other times, the filesize goes down). I just figured that that's the way excel works! But after you've run Rob's codecleaner, if you saved one version with the code compiled (either by running/testing code or by debug|compile VBAProject) and another without this extra step, then that could add to the difference in size. (Although, I don't think I've seen the file change size by as much as 300k.) I figure there are only a couple of things that I can do to keep the filesize down--make sure the lastusedcell is correct on all the sheets and run Rob's codecleaner). Everything that happens to the workbook after that ain't my fault <vbg. Peter T wrote: "Dave Peterson" wrote in message I'm glad the workaround worked ok for you. Not as glad as I am, thanks again! I've never had a problem saving from the VBE, but I've read a few posts where the authors say they avoid it because of problems they've had. Yes I've also read similar and don't normally save from the VBE. The problem though couldn't be fixed by saving changing IsAddin to false, locking, then saving in Excel. However the problem commenced having saved in the VBE, from then on couldn't be fixed. In my development project I already have something similar to Thomas Ramel's code (below), it does other things to. But I had removed pending distribution. ============== FWIW, an unexpected observation - The normal size of my old project varied from 1.4mb (no code ever run) to 2.2mb. In both cases just after using Rob Bovey's Codecleaner to strip and clean, the larger file size if I then do a full compile and save. But after saving both types of files from the immediate window the respective file sizes reduced by about 300kb. Seemed odd so I built a brand new identical file with similar sheets and dragged modules from the old file to the new. The new saves to the same lower file sizes respectively. Me thought - must be some garbage built up in the old worksheets, even though the project is mainly code and little in the way of worksheet stuff. I removed all modules from the old file and looked at file size. Very small and just as expected to account for the sheets, about 100kb. Bearing in mind the old project was freshly stripped and cleaned, I'm curious as to what that phantom 300kb accounted for. Even more curious as to why this 300Kb suddenly disappeared ONLY because I saved from the immediate window. Replicates with back-ups of old non-problematic files. Regards, Peter T In a different forum, Thomas Ramel posted some code that he adds to an addin. You may like it: ========================= From Thomas Ramel: Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.Saved = True Then Exit Sub If MsgBox("Save changes in Add-In" & Chr(13) & Chr(13) & _ UCase(ThisWorkbook.VBProject.Name) & Chr(13) & Chr(13) & _ "?", vbYesNo) = vbYes Then Application.DisplayAlerts = False ThisWorkbook.Save Application.DisplayAlerts = True End If End Sub Now every time I changed something in the Code and forgot to save it manually I get a reminder, wehe I close Excel. Regards Thomas Ramel Peter T wrote: Hi Dave, thanks for responding. Comments in line - My bet is that you're opening a different file than you're saving. No, definately working with the same file. But I'd better not be that dogmatic (see later). Make sure that the addin's project is active before saving. Yes always active, selected in vbe project's panel, then save from vbe. And as a workaround (I've never had the trouble you describe), you can change the workbook's .addin property to false. (Select your project, hit F4 to see properties and look for IsAddin.) I've tried locking & saving with IsAddin True (from vbe) and False (from both Excel & vbe) Then you can go back to excel and save that workbook as an addin (remember to change the filetype) via that interface. Similar to one step above. But also locked what was an xls with SaveAs xla while still a visible workbook. I've also tried copying the file to different folders, renaming, trying each with different passwords (only one open at a time). Each time it/they re-open unlocked. A password obviously exists though no asteriks, after entering the correct pw for that file I can tick the Lock checkbox. Check Fullname each time. Repeat the save, all to no avail. Also emptied temporary folders and repeated in new sessions of Excel and even Windows. Another way while in the VBE: hit ctrl-g (to see the immediate window): type this and hit enter: workbooks("youraddinname.xla").save Well blow me down! that's just worked !! Bit of a mystery but thank you very much. Regards, Peter T Peter T wrote: In XL2K, after closing all module windows, I "lock project for viewing" and password protect my addin. Save from the vbe, close XL. When I reopen XL and my addin, the project is not locked. I go back into Project properties / Protection and the lock check box and password dialogs are empty. If I now tick the lock checkbox I'm asked for my password (although no asterisks appear in the password textbox's). Enter password, everything seems normal. Save, close, reopen and back to the same situation. The project is "clean" and correctly compiled. Any ideas how to resolve without rebuilding the addin appreciated. Regards, Peter T -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to lock the Ctrl key? (as locking the Shift key w/Caps Lock) | Excel Discussion (Misc queries) | |||
how do I undo the scroll lock, thscroll lock button does not work | Excel Discussion (Misc queries) | |||
Lock Project | Excel Discussion (Misc queries) | |||
Assigning the Help 4, *.HLP file for a project programmatically in a protected Project | Excel Programming | |||
Accesing vba project from wb that has vba project password protected | Excel Programming |