Is there anyway to protect a sheet 1

[bnageshrao]

Is there anyway to protect a sheet for which I have written lot of vbas. I cannot use Tools-Protection and use that option because when i click the buttons to run the macros some errors come.
Also Is there any way to make the user to enter only specified number of digits or character lengths in a cell without making the Error box or message box popping up. It shouldnot allow them to type more than what they were suppose to type and if they try then it should freeze at that point.

 

[dsi]

Do you want to protect the worksheet or the VBA code? If you want to protect the worksheet use Tools > Protection > Protect Worksheet. There are several levels of protection you can use. If you want to protect your code, go to the VB Editor, select Tools > Project Properties. Then, on the Protection tab, lock the project for viewing. You should make sure that you eliminate or handle all errors in the project before you lock the project. If you get an error and need to debug it, just unlock the project and rerun your program. This will allow it to go into debug mode, assuming you disable your error handlers.

As far as validating inputs, look into the Conditional Formatting options.

Hope this helps!

 

[bnageshrao]

I want to protect the sheet. On the sheet I have put few command buttons and corresponding vba for those buttons. When I protect the sheet and click on this buttons to execute the vba scripts I get a error " You cannot use this command on a protected sheet.To unprotect the sheet, use the unprotect sheet command(Tools menu, protection sub menu).You may be prompted for a password".

 

[dsi]

It seems as though your program is modifying cells on the worksheet. This cannot be done when protected. If there are specific cells, columns or rows that the program needs to update:

1. Unprotect the worksheet
2. Select the cells, columns or rows
that the program modifies
3. Format > Cells > Protection Tab
4. Uncheck the Locked option
5. Protect the worksheet

This will protect all of the remaining cells on the worksheet.

 

[bnageshrao]

I tried all those and still no luck i am stuck with same error.

 

[dsi]

What command is it erroring on? If you have error handling routines, comment them out so you can pinpoint the problem code.

 

[LoNeRaVeR]

dsi:

At the beginning of each macro that is going to make changes on a sheet or just before you make changes put:
[tt]
Sheets(1).Unprotect Password:="dsi"
[/tt]
At the end of your code or after you are done changing the sheet put:
[tt]
Sheets(1).Protect Password:="dsi"
[/tt]
You'll have to replace [tt]Sheets(1)[/tt] with the correct Sheet and replace [tt]"dsi"[/tt] with the password of your choice.

Regards,

LoNeRaVeR

 

[bnageshrao]

That command worked great, thanks.

 

[dsi]

I don't like putting hardcoded passwords in the code. Even if the project is protected from viewing, you can still find the password with a little time and effort. This really only matters if it is a distributed application and you really want it locked down.

 

[dsi]

If you are going to use that method, make sure that you add the Protect method to your error handling routine.

 

[bnageshrao]

What is that error handling routine, I am new to vba and i never wrote one, could you explain it or send me a sample code. Thanks.

 

[dsi]

Something like this:

Sub YourSubroutine()

    On Error Goto ErrHndlr

    'Unprotect the Sheet
    Sheets(1).Unprotect Password:="dsi"

    '
    'Do all of your work here
    '
    
    'Protect the Sheet
    Sheets(1).Protect Password:="dsi"

    Exit Sub

ErrHndlr:
    MsgBox "Error No: " & Err.Number & vbCrLf & _
           "Description: " & Err.Description
    Err.Clear

    'Protect the Sheet
    Sheets(1).Protect Password:="dsi"
End Sub

If your program crashes before the sheet is reprotected, the sheet will remain unprotected. You should have a failsafe in case there is an error encountered while running your code. The On Error statement will tell your program what to do when an error is encountered. In this case, the built in Err object is used to display the error information, the error is cleared, and the sheet is protected again.

Hope this helps!

 

[LoNeRaVeR]

dsi:

Very valid points and good suggestions! This is just an easy way to prevent the average user from making changes when you don't want them to.


bnageshrao:

Don't forget to password protect your module, too.

1) Press ALT+F11
2) Press Ctrl+R if the Project Explorer isn't displayed
3) Right-Click on the any item in that workbook
4) Select the VBA Properties... menu item
5) Select the Protection tab
6) Click the Lock project for viewing checkbox
7) Enter your password
8) Confirm your password by entering it again
9) Click the OK button

Bonne chance,

LoNeRaVeR

 

[dsi]

LoNeRaVeR:

Your method can make it much easier than formatting individual cells, which also makes modifications much easier. In addition, this method will also protect the entire contents of the sheet, including the cells the program has modified.

I thought of one more hole in the error handling routine posted above. The user can still Ctrl-Break the program, which may leave the worksheet unprotected. Just set the UserControl Property as added below.

Sub YourSubroutine()

    On Error Goto ErrHndlr

    Application.UserControl = False

    'Unprotect the Sheet
    Sheets(1).Unprotect Password:="dsi"

    '
    'Do all of your work here
    '
    
    'Protect the Sheet
    Sheets(1).Protect Password:="dsi"

    Application.UserControl = True

    Exit Sub

ErrHndlr:
    MsgBox "Error No: " & Err.Number & vbCrLf & _
           "Description: " & Err.Description
    Err.Clear

    'Protect the Sheet
    Sheets(1).Protect Password:="dsi"
    Application.UserControl = True
End Sub

 

[LoNeRaVeR]

dsi:

Just like me... you're always thinkin'! ;-) That's a great suggestion for any code utilizing error trapping with On Error.

LoNeRaVeR