worksheet protect and macros

Hi all,

I am new to this forum and a novice with excel vba/macro. With the help of this forum and other websites I have managed to develop a code to automate some simple tasks.
To be specific I have 3 worksheets. One input worksheet, one output sheet and one sheet where I do all my intermediate calculations. User is required to input in the input sheet and clicking on some buttons (in the input sheet) will perform calculations and report output.
Now I would like the end user to not be able to mess with the calculation worksheet, so I protect the worksheet. After I protect with a password, my code errors out at "Sheets("CALC").Select" where CALC is the name of my worksheet where I do the intermediate calculation. So it appears as if password protecting the sheet prevents the sheet from even being accessed.

The error I get is Run-time error '1004': Select method of Worksheet class failed

Code runs fine if I leave everything unprotected.

Since my file containts some proprietary information I am unable to attach it as is. If however this is a specific problem, I will modify the worksheet and attach it. I thought this is a basic problem and there might be some simple fixes.

Thank you very much,.
newbie !

Hi,

Protecting a worksheet shouldn't make it inaccessible. What is the code you are using to protect the worksheet? It should be something like

Sheets("CALC").Protect Password:="YourPassword"

Also, can you manually click on the worksheet to select it after your VBA has protected it?

Hope this helps

thanks for the quick reply. Every little thing helps.
I am protecting it manually outside. I was not using vba code (like the one you show) to protect the sheet.

Hi TMS,

Sorry I don't understand how to use your suggestion (UserInterfaceOnly....)

Here is the code within my command button. Only if I unprotect and leave it visible am I able to run without any errors. Even hiding this worksheet causes run time error 1004. What am I missing...

Private Sub CommandButton2_Click()

Application.ScreenUpdating = False
    
    Sheets("CALC").Select
    ActiveSheet.Range("W3:W20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("OUTPUT").Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True

    Sheets("INPUT").Select
    ActiveSheet.Range("B5:B7").Select
    Selection.Copy
    Sheets("OUTPUT").Select
        ActiveSheet.Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Application.CutCopyMode = False
    a = ActiveCell.Row
    ActiveSheet.Range("A" & a) = a - 2
    
Application.ScreenUpdating = True

Sheets("INPUT").Select
ActiveSheet.Range("B14").Select
    
End Sub

Originally Posted by Richard Buttrey

Hi

You could either include as the first line of your macro

Sheets("Calc").Unprotect Password:= "your password"
then as the last line
Sheets("Calc").Protect Password:= "your password"

Whilst that will work it's always advisable to use the sheet code names rather than the tab name, Tab names can be changed with obvious problems. So if for instance the Calc sheet is Sheet3 then used
Sheet3.UnProtect
etc..

However have you considered hiding the Calc sheet and setting the Visible property of the Calc sheet to xlSheetVeryHidden leaving it unprotected. A casual user will not know it's there and won't see it in the Format Hide and Unhide area where you can usually see what sheets are hidden.

I pasted the code from within the commandbutton. I am noticing that even hiding (without protecting) causes the run time error and compilation to stop at "Sheets("CALC").Select. Setting it to visible allows it to run.

Thanks !

Originally Posted by TMShucks

You can't select a hidden worksheet.

In most cases, you don't have to select an object to manipulate it. And, for improved performance, you should avoid it.

I tried the following

With Worksheets("CALC")
    .Visible = xlSheetVeryHidden
    ActiveSheet.Range("W3:W20").Select
    
        'Sheets("CALC").Select
    
    Application.CutCopyMode = False
    Selection.Copy
    End With
    Sheets("OUTPUT").Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True

But it does not seem to paste the contents on to the output sheet. However it doesnt give me run time error. I also tried Visible=False. Still does the same.

Originally Posted by TMShucks

Try (untested):

Private Sub CommandButton2_Click()

Application.ScreenUpdating = False
    
    Sheets("CALC").Range("W3:W20").Copy
    Sheets("OUTPUT").Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    Sheets("INPUT").Range("B5:B7").Copy
    Sheets("OUTPUT").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    a = ActiveCell.Row
    ActiveSheet.Range("A" & a) = a - 2
    
Application.ScreenUpdating = True

Sheets("INPUT").Select
ActiveSheet.Range("B14").Select
    
End Sub

You'll need to adjust the

ActiveSheet.Range("A" & a) = a - 2

bit as the amended code doesn't select any cells so the ActiveCell might not be what it would have been.

Regards, TMS

Awesome. That worked ! Thanks a lot. As I understand now, "select" does not work with hidden sheets, so we need to club the code into one line.