Code won't run for hidden sheet

[Reset]

I want to hide a sheet that holds raw data for crunching so the user can't easily click on it. However, when I run code I get "Run-time error '1004': Select method of Worksheet class failed". Is there a way to hide the sheet and still allow code to call it up?

 

[shg]

You can't select a hidden sheet, but you can operate on its contents without selecting, e.g.,

With Worksheets("myHiddenSheet")
    .Range("A1").Value = 1
End with

Post some code if you need help

 

[storm8]

It is generally recommended to avoid using select method while it dramatically slows down performance.

although sometimes painful, .select can almost always be avoided (unless you really want to select)

 

[Reset]

Darn. I did try this:

Sheets("Sheet4").Visible = True
'run needed code
Sheets("Sheet4").Visible = False

works ok but you get screen blinks while it executes. How about a way to keep it visible but the user cannot select it or at least change anything on it and still run code?
Thanks so far.

 

[taurean]

Either use, worksheet protect option as it is available in Excel

Or suppose if you have to protect Sheet1 and block the user only then use something like:

Private Sub Workbook_Open()
Sheets("Sheet1").Protect Userinterfaceonly = True
End Sub

Note: You can change the data on this sheet programmatically.

 

[Reset]

Taurean,

I tried your code and it did not recognize "Userinterfaceonly". I took that out and got back to the original issue, but now the sheet was protected and I could not unprotect. Manually protecting the sheet won't allow changes either.

 

[taurean]

That is very strange. Create a new workbook and then its ThisWorkbook Module insert following code:

Private Sub Workbook_Open()
'Run this first!
Sheets(1).Protect UserInterfaceOnly:=True
End Sub

Sub Test()
'Run this code after running code to protect userinterface.
Sheets(1).Range("D1").Value = "Somebody"
End Sub

Sub Test2()
'To unprotect sheet protected above!
Sheets(1).Unprotect
End Sub

And then test them one by one. Let me know if you still have difficulty.