• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automatic protection after input

JITHESH

New Member
Hi,
Anyone please help to do automatic protection in excel after the input. i have uploaded the file.in uploaded file A to J column must me protected after the input and also the rows corresponding to that column.
I have tried macro.but i don't know how to do it properly.
plz help.
 

Attachments

  • New Item creation file.xlsx
    116.9 KB · Views: 8
Assumes that columns A to J should not be blank for that row

Code:
Sub Protector()

ActiveSheet.Unprotect Password:=1

Dim lstrow As Integer
lstrow = Cells(Rows.Count, "A").End(xlUp).Row

'Countif formula to check blanks
Range("K2:K" & lstrow).Formula = "=COUNTIF(A2:J2,"""")"

Range("K2").Select
Do Until IsEmpty(ActiveCell)
    If ActiveCell.Value = 0 Then
        Range("A" & ActiveCell.Row & ":J" & ActiveCell.Row).Locked = True
        ActiveCell.Offset(1, 0).Select
    Else
        Range("A" & ActiveCell.Row & ":J" & ActiveCell.Row).Locked = False
        ActiveCell.Offset(1, 0).Select
    End If
Loop

Range("K2:K" & lstrow).ClearContents
Range("A1").Select

ActiveSheet.Protect Password:=1

MsgBox "Macro Completed", vbInformation, ""

End Sub
 
Assumes that columns A to J should not be blank for that row

Code:
Sub Protector()

ActiveSheet.Unprotect Password:=1

Dim lstrow As Integer
lstrow = Cells(Rows.Count, "A").End(xlUp).Row

'Countif formula to check blanks
Range("K2:K" & lstrow).Formula = "=COUNTIF(A2:J2,"""")"

Range("K2").Select
Do Until IsEmpty(ActiveCell)
    If ActiveCell.Value = 0 Then
        Range("A" & ActiveCell.Row & ":J" & ActiveCell.Row).Locked = True
        ActiveCell.Offset(1, 0).Select
    Else
        Range("A" & ActiveCell.Row & ":J" & ActiveCell.Row).Locked = False
        ActiveCell.Offset(1, 0).Select
    End If
Loop

Range("K2:K" & lstrow).ClearContents
Range("A1").Select

ActiveSheet.Protect Password:=1

MsgBox "Macro Completed", vbInformation, ""

End Sub


Thank you for your valuable reply.but this code is not working.i just open that corresponding excel and applied this code.but still data can be edited after input. in attached excel u can able to see 10 columns from A to J. in that all cells will be blank except 1st raw of that work book.1st raw contains respective captions for the column.my need is that except this which all cells we have adding inputs,that must be automatically to be locked after the input.user must dnt have an option to edit that after the input. can u please help me to solve this. bcz i basics of excel.but this vb like thing...its difficult for me.
 
Which column, always has data? As macro would need to find last row to be able to work.
 
Last edited:
Which column, always has data? As macro would need to find last row to be able to work
in that excel except that caption all cells will be blank...the people which am going to share this excel need to fill that cell.but once it filled that cells must be locked.
 
You'll need a Serial number column e.g. 1,2,3 etc. otherwise macro can't work out last row to do the locking of cells. I assume Column A is that columns? example attached. There are 2 pieces of code 1 in Sheet1 & the other in Module1.
 

Attachments

  • New Item creation file.xlsm
    121.1 KB · Views: 11
You'll need a Serial number column e.g. 1,2,3 etc. otherwise macro can't work out last row to do the locking of cells. I assume Column A is that columns? example attached. There are 2 pieces of code 1 in Sheet1 & the other in Module1.


yes its working..that what i want...u r awesome man..
 
how to change password of this file...and i need to remove the data which u filled...

and there is one more issue.when i open first time it shows an option to enable editing.and when i enabled editing people can add data and can save also.and can delete also.but there is one more option that is macro is disabled and for enable that a msg comes.but people not enabled it that excel is editable.how to solve this?
 
and when i go to the option view and select macro ,there is an option to view macro and by selecting that and edit it and there is shows that password is 1.so others can find this very easily.
 
To change password change the bits that say "Password:=1". You will need to unprotect it first so the next time the code runs, the password works.

"Enable Editing" - allows users to use the file. Fill in blank cells. Users cannot edit locked cells.

"Enable Macro" - must be enabled or cells cannot be protected as code will not run.

To hide the macro code, go into VBA menu, click on tools, options, vba project properties and set the password you want to protect the code with
 
no..its not working..i tried a password AJV and using same password for vba code protection...but its not working
 
this shows when i change the password
Did you first unprotect the sheet with 1 before changing & running code as I said in my earlier post? Also if your new password is a text password rather than number, then it needs to be in double quotation marks
 
I TRIED...AND ALL PROBLEMS SOLVED EXCEPT THAT MACRO MUST BE ENABLE MANUALLY WHILE OPENING THIS EXCEL.OTHER WISE PEOPLE CAN ENTER DATA AND SAVE AND CAN EDIT ALSO.SO HOW CAN ENABLE THIS AUTOMATICALLY.
 

HI !
YOU DO NOT CHECK "SECURITY" EXCEL OPTIONS !
WORKBOOK MUST BE IN A FOLDER AUTHORIZED FOR MACROS
OR ALLOW ANY MACRO
 
I TRIED...AND ALL PROBLEMS SOLVED EXCEPT THAT MACRO MUST BE ENABLE MANUALLY WHILE OPENING THIS EXCEL.OTHER WISE PEOPLE CAN ENTER DATA AND SAVE AND CAN EDIT ALSO.SO HOW CAN ENABLE THIS AUTOMATICALLY.

I have prepared final excel for ma work.and only one problem to be solved,the macro containing in this excel to be enable automatically when i open this excel file in any system. because i need to pass it to different system for data input. so if macro not enabled automatically users can edit data. i have protected macro editing using password ajv and workbook using1777.plz help me to solve this.
PFA
 

Attachments

  • NEW ITEM CREATION FILE TEMPLATE.xlsm
    130.3 KB · Views: 1
Back
Top