Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Do not load if you dont enable macros
Hi, I have a marco I found from this discussion group, its about locking cells.
I need to lock the cells so they dont get tampered with. My problem is every time the document is opened you have to enable the macro to run. This is obviously not very helpful as they can just click no and delete what they want. My question is: Is there a way to NOT load the document if the macro is not enabled? (i.e nothing appears if you do not click enable macros) That way everything is safe. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Do not load if you dont enable macros
Hi,
You can't. But you can implement certain techniques to make the workbook pretty useless unless they are enabled. This can still be defeated but it's better than nothing. have a look at the sites below. http://www.j-walk.com/SS/excel/tips/tip100.htm http://www.cpearson.com/excel/EnableMacros.aspx Mike "Mr. Burton" wrote: Hi, I have a marco I found from this discussion group, its about locking cells. I need to lock the cells so they dont get tampered with. My problem is every time the document is opened you have to enable the macro to run. This is obviously not very helpful as they can just click no and delete what they want. My question is: Is there a way to NOT load the document if the macro is not enabled? (i.e nothing appears if you do not click enable macros) That way everything is safe. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Do not load if you dont enable macros
Another option too would be to add a digital signature to your macros, and
then add your digital signature to the users computers as approved. This will allow the macros to be 'enabled' even when security setting is set to HIGH. -- John C "Mr. Burton" wrote: Hi, I have a marco I found from this discussion group, its about locking cells. I need to lock the cells so they dont get tampered with. My problem is every time the document is opened you have to enable the macro to run. This is obviously not very helpful as they can just click no and delete what they want. My question is: Is there a way to NOT load the document if the macro is not enabled? (i.e nothing appears if you do not click enable macros) That way everything is safe. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Do not load if you dont enable macros
Why do you need a macro to set sheet protection?
Just lock the desired cells and protect the sheet(s) then save. If you do need code to lock cells.......e.g. maybe you have event code to lock cells as you fill them, then you have to provide a contingency plan for when users disable macros. One method is to hide the sheets when the workbook is closed. When users open the workbook with macros diabled, the workbook is useless to them. If they enable macros the code runs and sheets are available. Here is a sample................ Create a sheet named Dummy with a large message typed in the middle. "You have disabled Macros and this workbook is useless without them. Please close and re-open with macros enabled" Then add these two event codes to Thisworkbook module. Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim sht As Worksheet Application.ScreenUpdating = False Sheets("Dummy").Visible = xlSheetVisible For Each sht In ActiveWorkbook.Sheets If sht.Name < "Dummy" Then sht.Visible = xlSheetVeryHidden End If Next sht Application.ScreenUpdating = True ThisWorkbook.Save End Sub Private Sub Workbook_Open() Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In ActiveWorkbook.Sheets If sht.Name < "Dummy" Then sht.Visible = True Sheets("Dummy").Visible = xlSheetVeryHidden End If Next sht Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Mon, 6 Oct 2008 02:48:00 -0700, Mr. Burton wrote: Hi, I have a marco I found from this discussion group, its about locking cells. I need to lock the cells so they dont get tampered with. My problem is every time the document is opened you have to enable the macro to run. This is obviously not very helpful as they can just click no and delete what they want. My question is: Is there a way to NOT load the document if the macro is not enabled? (i.e nothing appears if you do not click enable macros) That way everything is safe. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Do not load if you dont enable macros
Thats perfect thanks alot.
The code works really well, Great stuff, Helped alot. "Mike H" wrote: Hi, You can't. But you can implement certain techniques to make the workbook pretty useless unless they are enabled. This can still be defeated but it's better than nothing. have a look at the sites below. http://www.j-walk.com/SS/excel/tips/tip100.htm http://www.cpearson.com/excel/EnableMacros.aspx Mike "Mr. Burton" wrote: Hi, I have a marco I found from this discussion group, its about locking cells. I need to lock the cells so they dont get tampered with. My problem is every time the document is opened you have to enable the macro to run. This is obviously not very helpful as they can just click no and delete what they want. My question is: Is there a way to NOT load the document if the macro is not enabled? (i.e nothing appears if you do not click enable macros) That way everything is safe. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Do not load if you dont enable macros
I need to lock cells after data has been entered into them so others cant
change it, thats what the macro is for. But if they dont enable the macros then they can delete what they want. But the code Mike H suggested works perfectly. Brillant, Job done. "Gord Dibben" wrote: Why do you need a macro to set sheet protection? Just lock the desired cells and protect the sheet(s) then save. If you do need code to lock cells.......e.g. maybe you have event code to lock cells as you fill them, then you have to provide a contingency plan for when users disable macros. One method is to hide the sheets when the workbook is closed. When users open the workbook with macros diabled, the workbook is useless to them. If they enable macros the code runs and sheets are available. Here is a sample................ Create a sheet named Dummy with a large message typed in the middle. "You have disabled Macros and this workbook is useless without them. Please close and re-open with macros enabled" Then add these two event codes to Thisworkbook module. Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim sht As Worksheet Application.ScreenUpdating = False Sheets("Dummy").Visible = xlSheetVisible For Each sht In ActiveWorkbook.Sheets If sht.Name < "Dummy" Then sht.Visible = xlSheetVeryHidden End If Next sht Application.ScreenUpdating = True ThisWorkbook.Save End Sub Private Sub Workbook_Open() Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In ActiveWorkbook.Sheets If sht.Name < "Dummy" Then sht.Visible = True Sheets("Dummy").Visible = xlSheetVeryHidden End If Next sht Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Mon, 6 Oct 2008 02:48:00 -0700, Mr. Burton wrote: Hi, I have a marco I found from this discussion group, its about locking cells. I need to lock the cells so they dont get tampered with. My problem is every time the document is opened you have to enable the macro to run. This is obviously not very helpful as they can just click no and delete what they want. My question is: Is there a way to NOT load the document if the macro is not enabled? (i.e nothing appears if you do not click enable macros) That way everything is safe. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enable Macros | Excel Discussion (Misc queries) | |||
how to enable macros | Excel Discussion (Misc queries) | |||
Enable macros? | Excel Worksheet Functions | |||
Enable Macros | Excel Worksheet Functions | |||
can not enable macros | Excel Worksheet Functions |