Question on Macros

[ashokpandianr]

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.

 

[Fishboy]

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:

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]

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:

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

 

[ashokpandianr]

The cell is not locking up and also it told to save as new file

 

[MARK858]

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

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)

 

[ashokpandianr]

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





which isn't what you have done below

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]

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

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

to

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

 

[ashokpandianr]

Ok Thanks for your prompt response. Thanks to everyone

 

[Fishboy]

Ok Thanks for your prompt response. Thanks to everyone

Happy to help.