Hello Excel, still keep the spirit of learning Ms. Excel? I hope you still continue to learn and find out about the science of excel. This time I will try to discuss how to protect or in the Indonesian term "Lock" a worksheet or an existing worksheet on Ms. Excel you use Macro / VBA.
Before I discuss the step-by-step from 3 ways to use VBA worskheet protection beforehand I would be a review of what actually seh that protection? then what purpose anyway so you require to protection? Generally in my language protection is a step to protect something from the things that are not cool. This thing you need to do because given the importance of the data as the primary basis for an information process, and in this case the data is data in the form of an excel file.There are several stages of protection in Microsoft Excel and I've discussed this for those who still do not know please read on How to Create a Password Ms. ExcelIf you have a worksheet (worksheet) that have certain formulas that do not want to be changed by the irresponsible people both in terms of format or other course this is important to you and how protection by using VBA / MacroHow to Protect Worksheet with VBA / MacroThere are several ways you can do to lock / protection using macros is no standard way there is also a more complex means a standard way this can be done only limited protection standard worksheet on Ms. Excel.Worksheet Protection Standard MethodWorksheet protection by means of this coding standard is quite simple and simple coding examples please see below:Function ProtectSheet_SecaraDefault ()
Worksheets ( "Sheet1"). Protectend FunctionWorksheet Protection PasswordTo use the worksheet protection passwords or keywords using VBA is a little more cool and if the user would change the worksheet then have to enter a password first in order to change the format and more restricted. The following examples of its VBA codingFunction ProtectSheetDenganPasswordt ()
Worksheets ( "Sheet1"). Protect Password: = "1234"end FunctionYou can add a password unprotect worksheet with keywords 1234Worksheet Protection with Full ParameterThe type of protection is much more violent than the first and second way above because we can set some parameters so that the things that should be locked can be set in such a way. Here's an example of coding.See Also:How to Create a Password Difficult Predictable in Microsoft ExcelFunction ProtectSheetLebihLengkap ()Worksheets ( "Sheet1"). Protect _Password: = "1234", _DrawingObjects: = False, _Contents: = True, _Scenarios: = True, _UserInterfaceOnly: = True, _AllowFormattingCells: = True, _AllowFormattingColumns: = True, _AllowFormattingRows: = True, _AllowInsertingColumns: = True, _AllowInsertingRows: = True, _AllowInsertingHyperlinks: = True, _AllowDeletingColumns: = True, _AllowDeletingRows: = True, _AllowSorting: = False, _AllowFiltering: = False, _AllowUsingPivotTables: = Falseend FunctionYou can change the settings True or False to limit in accordance with the desired conditions please testedOf the three examples above worksheet protection you can use one type of protection in accordance with his wishes.Until this stage you can create a worksheet using VBA coding protection, well since you've been able to make it and because you have made it you have to be responsible in order to make the worksheet protection (unprotect) using VBA anyway. But do not worry I will also include how to make coding for Unprotect below:Function BukaProtekWorksheet ()
Worksheets ( "Sheet1"). Unprotect ( "1234")end FunctionPretty simple coding for the Unprotect but if you want more cool example will add a message if password protection is wrong then there is little change in coding VBA / macro becomesFunction UnProtectSheet ()On Error GoTo JikaErrorWorksheets ( "Sheet1"). Unprotect ( "vishwa123")JikaError:MsgBox JikaError.Number & ":" & JikaError.Descriptionend FunctionIf there is a mistake or error, the error message will appear for the handling of the password is wrong.Thus, hopefully useful and good luck
Before I discuss the step-by-step from 3 ways to use VBA worskheet protection beforehand I would be a review of what actually seh that protection? then what purpose anyway so you require to protection? Generally in my language protection is a step to protect something from the things that are not cool. This thing you need to do because given the importance of the data as the primary basis for an information process, and in this case the data is data in the form of an excel file.There are several stages of protection in Microsoft Excel and I've discussed this for those who still do not know please read on How to Create a Password Ms. ExcelIf you have a worksheet (worksheet) that have certain formulas that do not want to be changed by the irresponsible people both in terms of format or other course this is important to you and how protection by using VBA / MacroHow to Protect Worksheet with VBA / MacroThere are several ways you can do to lock / protection using macros is no standard way there is also a more complex means a standard way this can be done only limited protection standard worksheet on Ms. Excel.Worksheet Protection Standard MethodWorksheet protection by means of this coding standard is quite simple and simple coding examples please see below:Function ProtectSheet_SecaraDefault ()
Worksheets ( "Sheet1"). Protectend FunctionWorksheet Protection PasswordTo use the worksheet protection passwords or keywords using VBA is a little more cool and if the user would change the worksheet then have to enter a password first in order to change the format and more restricted. The following examples of its VBA codingFunction ProtectSheetDenganPasswordt ()
Worksheets ( "Sheet1"). Protect Password: = "1234"end FunctionYou can add a password unprotect worksheet with keywords 1234Worksheet Protection with Full ParameterThe type of protection is much more violent than the first and second way above because we can set some parameters so that the things that should be locked can be set in such a way. Here's an example of coding.See Also:How to Create a Password Difficult Predictable in Microsoft ExcelFunction ProtectSheetLebihLengkap ()Worksheets ( "Sheet1"). Protect _Password: = "1234", _DrawingObjects: = False, _Contents: = True, _Scenarios: = True, _UserInterfaceOnly: = True, _AllowFormattingCells: = True, _AllowFormattingColumns: = True, _AllowFormattingRows: = True, _AllowInsertingColumns: = True, _AllowInsertingRows: = True, _AllowInsertingHyperlinks: = True, _AllowDeletingColumns: = True, _AllowDeletingRows: = True, _AllowSorting: = False, _AllowFiltering: = False, _AllowUsingPivotTables: = Falseend FunctionYou can change the settings True or False to limit in accordance with the desired conditions please testedOf the three examples above worksheet protection you can use one type of protection in accordance with his wishes.Until this stage you can create a worksheet using VBA coding protection, well since you've been able to make it and because you have made it you have to be responsible in order to make the worksheet protection (unprotect) using VBA anyway. But do not worry I will also include how to make coding for Unprotect below:Function BukaProtekWorksheet ()
Worksheets ( "Sheet1"). Unprotect ( "1234")end FunctionPretty simple coding for the Unprotect but if you want more cool example will add a message if password protection is wrong then there is little change in coding VBA / macro becomesFunction UnProtectSheet ()On Error GoTo JikaErrorWorksheets ( "Sheet1"). Unprotect ( "vishwa123")JikaError:MsgBox JikaError.Number & ":" & JikaError.Descriptionend FunctionIf there is a mistake or error, the error message will appear for the handling of the password is wrong.Thus, hopefully useful and good luck
No comments:
Post a Comment