Reem, one of the PHD readers, asks in e-mail,
Is there a way to prevent users from unhiding “hidden sheets” in an excel file – without using VBA?
or to put it in other words, can the “Format/Sheet/Unhide” be disabled for specific worksheets?
Here is a non-VBA way to do this. I am not sure if this is optimum, but it seems to produce results without much effort. And it doesn’t use VBA, just the VBA Editor.
Step 1: Right click on the tab you want to hide and select view code option
Step 2: In the properties window for that sheet, set “visibility” as 2 – xlSheetVeryHidden
Step 3: Now right click on the sheet name in project explorer area and select VBA Project properties
Step 4: Go to “Protection” tab and check “Lock” project
Step 5: and set password for protection, click ok
Step 6: when someone tries to open the VBA Code for that sheet to make the worksheet tab unhidden (visible), Excel prompts for a password
This trick is very handy when you are sharing workbooks with others and afraid that they may ruin the calculations or data.
2 Responses to “Top 10 Power BI Interview Questions & Answers”
Hello...
In Power BI I have data that includes months by name only (e.g. May, April, December...)
I need to build charts etc. but i need the months to go chronologically... not alphabetically... I cannot seem to find the fix to this.... once again, my data does NOT have an actual date attached to it (like 02/01/2023)....only month names... can i use a helper table wher i id the month names as numbers 1 thru 12? and if so, how do i manage this to work for me ?
Thank you.
~Keith
You need to setup an extra table to map each month name to a running number. A simple 12 row table like
Jan 1
Feb 2
Mar 3
..
Dec 12
Then create a relationship between this month table and your month column
Now, go to "table view" in Power BI and set the sort by column to month number for the month name column on this new table.
Finally, use the new table's month name whenever you need to refer to the month name in the visuals.
They will be chronologically arranged.