Question on Macros

ashokpandianr

New Member
Joined
Feb 18, 2016
Messages
7
Hi

I am creating one review record in this record I want to lock the particular cell of the data once the user was close the file (Save & close it ) .

Example : There are column A B C in the table once the user enter the information in A1 B1 C1 and he saves then if he want to edit it he can do so but once he closed the file again if he opened it he should not edit it again.

Please help me with the macros.

PS: I want to share it to 100plus ppl so IDK if they ll save the MAcros properly Is it possible to enable the Macros once they open the file. I mean circulate the file as normal excel file and while open it the Macro should work automatically .

Please guide Urgent help.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
Hi

I am creating one review record in this record I want to lock the particular cell of the data once the user was close the file (Save & close it ) .

Example : There are column A B C in the table once the user enter the information in A1 B1 C1 and he saves then if he want to edit it he can do so but once he closed the file again if he opened it he should not edit it again.

Please help me with the macros.

PS: I want to share it to 100plus ppl so IDK if they ll save the MAcros properly Is it possible to enable the Macros once they open the file. I mean circulate the file as normal excel file and while open it the Macro should work automatically .

Please guide Urgent help.
Hi ashokpandianr, welcome to the boards.

Unfortunately this is no simple way of enforcing macros to be enabled (mainly for security reasons to prevent malicious code from running). You will pretty much rely on your users agree to enable macros. It is possible to Google ways around this but generally forcing users to enable macros without their knowledge or consent is frowned upon.

With regards to locking data once the workbook has been closed and reopened, the following Workbook_BeforeClose macro sounds like it should do what you want. To try it out make a COPY of your workbook to test in. Next, press ALT+F11 to open the VBA developer window. Find the name of your workbook in the project window on the left-hand side, right-click on ThisWorkbook and select View Code. In the new window that opens you can copy / paste in the following:

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    MsgResponse = MsgBox("Are you sure you are ready to save and exit?", vbYesNo, "Are you sure?")
        If MsgResponse = vbNo Then
            Cancel = True
                Exit Sub
        Else
            Worksheets("Sheet1").Unprotect "Password"
                Worksheets("Sheet1").Cells.Locked = False
                    Worksheets("Sheet1").UsedRange.Locked = True
                        Worksheets("Sheet1").Protect "Password"
                            If Me.Saved = False Then Me.Save
        End If
End Sub

It basically asks if the user is sure they want to save and exit. If they say no it cancels. If they say yes it unprotects the sheet, disables the lock on all cells (in case this is the first time it is run), then locks only the used cells before reprotecting the sheet. Finally it saves the document and closes.

Is that any use to you?
 

ashokpandianr

New Member
Joined
Feb 18, 2016
Messages
7
Hi ashokpandianr, welcome to the boards.

Unfortunately this is no simple way of enforcing macros to be enabled (mainly for security reasons to prevent malicious code from running). You will pretty much rely on your users agree to enable macros. It is possible to Google ways around this but generally forcing users to enable macros without their knowledge or consent is frowned upon.

With regards to locking data once the workbook has been closed and reopened, the following Workbook_BeforeClose macro sounds like it should do what you want. To try it out make a COPY of your workbook to test in. Next, press ALT+F11 to open the VBA developer window. Find the name of your workbook in the project window on the left-hand side, right-click on ThisWorkbook and select View Code. In the new window that opens you can copy / paste in the following:

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    MsgResponse = MsgBox("Are you sure you are ready to save and exit?", vbYesNo, "Are you sure?")
        If MsgResponse = vbNo Then
            Cancel = True
                Exit Sub
        Else
            Worksheets("Sheet1").Unprotect "Password"
                Worksheets("Sheet1").Cells.Locked = False
                    Worksheets("Sheet1").UsedRange.Locked = True
                        Worksheets("Sheet1").Protect "Password"
                            If Me.Saved = False Then Me.Save
        End If
End Sub

It basically asks if the user is sure they want to save and exit. If they say no it cancels. If they say yes it unprotects the sheet, disables the lock on all cells (in case this is the first time it is run), then locks only the used cells before reprotecting the sheet. Finally it saves the document and closes.

Is that any use to you?


Thanks for the help. I tried to use it but I am not getting any message like that.......... I have opened the view code for the particular sheet and then saved your code then I have saved it as a macro file and then I tested it but its not working.


could you please help me to resolve this Please
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,061
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

From the instructions Fishboy gave you on where to put the code....


Next, press ALT+F11 to open the VBA developer window. Find the name of your workbook in the project window on the left-hand side, right-click on ThisWorkbook and select View Code


which isn't what you have done below


Code:
I have opened the view code for the particular sheet

and the workbook needs to be saved as a macro enabled workbook which is probably the message you are getting if your file was an xlsx (which can't hold macro's)
 
Last edited:

ashokpandianr

New Member
Joined
Feb 18, 2016
Messages
7
From the instructions Fishboy gave you on where to put the code....





which isn't what you have done below


Code:
I have opened the view code for the particular sheet

and the workbook needs to be saved as a macro enabled workbook which is probably the message you are getting if your file was an xlsx (which can't hold macro's)


Sorry I meant the workbook only. Do I have to change any any code for the range?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,061
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

You need to make sure that "Password" is the password that you are using in both places in the macro.

You need to make sure that Sheet1 is the name of your sheet in Worksheets("Sheet1") in all places.

If you need for example only A1:C1 locked then change

Code:
 Worksheets("Sheet1").UsedRange.Locked = True

to

Code:
Worksheets("Sheet1").Range("A1:C1").Locked = True
 

Watch MrExcel Video

Forum statistics

Threads
1,130,047
Messages
5,639,766
Members
417,109
Latest member
996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top