Protecting Specific Worksheets in an Excel Workbook using VBA

Protecting Specific Worksheets in an Excel Workbook using VBA
Excel 2007	Excel 2003	Macros Excel 2007	Macros Excel 2003	Excel 2010

	Microsoft VBA question I have 5 worksheets in my workbook. I wish to password protect the sheets four and five when the workbook is opened. That is, the sheets 4 and 5 are not visible to somebody who doesn't have a password. How can I implement this? Thanks for your help in advance! Andrea from Goa wants to do the follwing: Create a user-form that loads when the workbook is opened The user is required to enter a password in a textbox The password should not be visible during entry (obviously!) On clicking on the login command button the password is authenticated and the user can see all the worksheets In case the password entered is incorrect the user has an opportunitry to retry. If she doesn't want to retry or doesn't have a password, she can select 'no' and proceed to open the workbook without the protected worksheets. How to implement the process step by step: Click on the developer tab Select 'Visual Basic' Double-click on the item 'this workbook' on the left-side of the window Enter the code below by selecting 'workbook' in the right-hand side window under 'General' and under declarations select 'Open'. Also select 'workbook' under 'General'and 'Before close' under 'Declarations' and write the relevant code as shown below. VBA coding when the user form loads Private Sub Workbook_Open() UserForm1.Show End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Sheets("Sheet4").Visible = False Sheets("Sheet5").Visible = False End Sub Next click on the 'insert' menu, select 'userform' and on the form create your controls using standard methods to provide a label (enter password), a textbox and a command button (login). Then write code for the command button as shown below: VBA code for the command button: Private Sub CommandButton1_Click() If Me.TextBox1.Value = "azby,,1029" Then Unload Me Sheets("Sheet4").Visible = True Sheets("Sheet5").Visible = True Sheets("Sheet1").Select Else Me.Hide Retry = MsgBox("The password is incorrect. Do you wish to try again?", vbYesNo, "Retry?") Select Case Retry Case Is = vbYes Me.TextBox1.Value = "" Me.TextBox1.SetFocus Me.Show Case Is = vbNo Unload Me End Select End If End Sub You'll nee the windows media player to tatch the video (15 MB) below to see how the complete process can be implemented easily: More Tips (Solutions): How do I view and edit Microsoft Excel files on an iPad? Inventory Soluton How to rename and color worksheet tabs Copy Worksheet Quickly Criteria Range Animations in Microsoft Excel Create two charts at once How to avoid errors while working in Excel How to benefit from Microsoft Excel Templates How to use Microsoft Excel 2007 with Word 2007 - create mail merge labels Landscape Oriented Worksheet Template How to-make a worksheet fit a printed page How to make your charts more impressive Perpetual Calendar from 1900 to 9999 How to create your own custom add-in How to clear conditional formattng in Excel worksheets How to speed up data entry of decimal numbers How to change the color of the gridlines of an Excel worksheet or hide them How to use Autosum in Multiple Worksheet Cells Quickly How to have your free personal assistant in Mcrosoft Excel who reads out the data to you How to use the status bar in Microsoft Excel to do a quick data analysis How to calculate equal monthly payments or instalments using a mathematical equation Data Forms to Enter and Edit Data View 2 worksheets in same workbook side by side Accessing a specific worksheet in large workbooks with multiple worksheets Referencing a cell in another worksheet How to perform a what-if analysis using a scrollbar form control An interesting payroll solution Future Value Solution in Microsoft Excel Another Interesting Conditional Formatting Solution DSUM and Array Formulas for addition solutions IF Function Question How to use command buttons on a splash screen in MS-Excel Copy Paste Data from one Excel Worksheet to Another How to separate comma separated values (csv) in a worksheet cell in Microsoft Excel into rows or columns Another Interesting Solver solution in Microsoft Excel Find duplicates in two different Excel worksheets using a macro How to count data based on multiple criteria - countifs function How to convert 6/5/2011 into Sunday, 6/5/2011 User Form to perform calculations An interesting solution using conditional formatting in Microsoft Excel 2007 Show/Hide Controls on a User-Form using a Checkbox Populate Listbox and Textboxes with Excel data using VBA and Vlookup
Quickly find the topic of your interest. Example: Compound Interest.
Excel 2007	Excel 2003	Macros Excel 2007	Macros Excel 2003	Excel 2010

Take a two click survey and help us improve our services