Macro to Hide or unhide excel sheet but formulas should work-CodePudding

I have used this method before but i do not know why after 6 months when i am doing this again i cannot do it.

I have created a data entry fields and through which i am moving the data from Sheet1 to Sheet2 using a Button.

However, i do not want users to see the Sheet2 so that they cannot change anything from it. for this i was using the formulas mentioned below

Sheets("Data Sheet").Visible = True


Sheets("Data Sheet").Visible = False

after hiding the sheet i protect them with the password. the main advantage of above-mentioned code is that still i can post the data from Sheet1 to sheet2 after hiding as well.

Upon research i found one more formula to hide the sheet but after hiding sheet2 whatever i am posting via Sheet1 to Sheet2(which is hidden) it gives error.

ThisWorkbook.Worksheets(Array("Data Sheet")).Visible = xlSheetHidden

Can anyone help please, so that i can hide the sheet2 and can continue posting the entries via sheet1 as well

highly appreciated

CodePudding user response：

You can manipulate data on a hidden sheet, no need to unhide it.
Unless you are trying to select cells, but you shouldn't have to do that.

You do however need to unlock a sheet before editing it if the cells are protected. You can do this as part of the process.

Sheets("Data Sheet").Unprotect Password:="pass"
----
whatever code you have
----
Sheets("Data Sheet").Protect Password:="pass"

You do might want to have some error handling in there however, since if the code throws an error, the sheet will be left unlocked.

CodePudding user response：

a) The statements Sheets("Data Sheet").Visible = False and ThisWorkbook.Worksheets(Array("Data Sheet")).Visible = xlSheetHidden do basically the same. However, you will need to understand why it is important to qualify the Sheets: ThisWorkbook.Worksheets will access the worksheets from the workbook where the code lives in, just writing Sheets (or Worksheets) will access the sheets of the ActiveWorkbook - and that may be a different workbook. The Array("Data Sheet")-part is unnecessary in your case (you can pass an array of sheet names to hide more than a sheet at once). Setting visibility to xlSheetHidden or to False is identical.

b) Hiding a sheet and protecting a sheet are two different, independent things. You can protect a sheet but not hide it, you can hide a sheet but let it unprotected.

c) The main idea of protecting a sheet is to allow user input only at specific cells. The user can change the content of the sheet, but only to cells which are not formatted as "Locked".

d) If you protect a sheet via Excel (no matter if hidden or not) and want to modify something via code, you will need to unprotect it (and protect it again after the code is done). However, when protecting the sheet via code, you can specify that you want to allow the code to make modifications by setting the UserInterfaceOnly-parameter:

Thisworkbook.Sheets("Data Sheet").Protect Password = "IWontTellYou", UserInterfaceOnly:=True

e) If you never want to show the sheet, set the visibility not to hidden, but to veryHidden. With that, the sheet cannot be made visible by the use from within Excel: It will not be listed under "Unhide..." - in that case there is not need to protect it.

Thisworkbook.Sheets("Data Sheet").Visible = xlSheetVeryHidden

(Note that in that case you can make the sheet visible again only via code, but a one-liner in the immediate window is sufficient)

CodePudding user response：

You will have to write a code to first unprotect sheet2, then paste the data and protect sheet2 again.