Running Macros on Hidden Worksheets

I've been programming for about 10 years now and the one thing that really sticks out are the people that think outside the box with straight forward solutions and codes, the problem solving methods and thought patterns employed here is genius, absolutely brilliant. Thank you Allen.

Thank you for the tips. Worked beautifully.

@Fawad
See https://excelribbon.tips.net/T013589_Finding_a_Worksheet_to_Unhide_among_Many_Hidden_Sheets.html
and my comment dated 2018-11-25.

Hi, I am working on a model where I have a front page and attached sheets. On front page I have buttons hyperlinked with the attached individual sheets. I want these sheets hidden and only to be shown when click on the button on the front page. Please guide.

Clayton

The macros:

Private Sub FormatHidden()
Dim wks As Worksheet, rng As range

Set wks = Sheets("sheet4")
With wks
.Visible = xlSheetVeryHidden
Set rng = .range("A1:B5")
rng.Font.Bold = True
.Visible = xlSheetVeryHidden = False
.Visible = xlSheetVisible
End With
End Sub
Sub HideSheet()
ActiveSheet.Visible = xlSheetVeryHidden
End Sub

Clayton

Excel is not very secure and the VB Editor can be activated with ALT + F11 if the developers tab is hidden. once in the VB editor each sheet is listed in the Project Explorer (CTRL + R). If the sheet is password protected then this offers a level of protection but this can be removed, see the link below. Using the keyword Private before a function or Macro will hide it from the Macro form (ALT + F8) so the macro will not be able to run from there unless the user knows the macro/ function name before hand. In short Excel is fairly secure against accidental usage but malicious damage.

http://www.mcgimpsey.com/excel/removepwords.html

Here are a couple of macros to play around with that you can use to test things for your self.

Can someone explain to me how any worksheet can be truly hidden in a macro-enabled workbook if a macro can Activate the hidden sheet, a sheet whose name is known by looking inside the code of the macros the user is permitted to run. Seems the user can write a simple macro that activates the hidden sheet and starts upper-left and print out every cell's formulas and values for the sheet? It seems to me the user must be denied access to the VBE to truly "hide" the guts of the sheet and/or sensitive data. If so, is Hiding the Developer tab sufficient for keeping users out of the source code?

Thanks! This helped me fix my issues and learning how to stop the screen updating really sped up my macro as it used less processing power. Helped alot!

I have a different problem. Excel "Very well hidden" sheets complete with password protection can be viewed without any problem over a smartphone. Does anyone know a way around this?

@Ramanathan

Hiding the worksheet is easy you can just create a button on each worksheet you want to be able to hide and assign it the macro:

Sub HideSheet()
Activesheet.visible=xlSheetVeryHidden
End Sub
put this macro in a code module.

Unhiding is not so easy as you cannot see the hidden sheet (duh!!), this will need to be done via a control worksheet which cannot be hidden with a button for each other worksheet which when clicked will execute a short macro to unhide that selected sheet and make it active.

Sub UnhideSheet()
With Thisworkbook.Worksheets("SheetName") 'use the actual worksheet name instead of SheetName
.Visible = xlSheetVisible
.Activate
End With
End Sub

A more elegant way might be to list the hidden worksheets in a ListBox so the User selects the Worksheets from the drop-down list then clicks a button to activate the select worksheet(s). This could be on a master/control worksheet, in a menu, or floating in modeless Userform.

I Want New codings to Hide or un hide the sheets by clicking in the box which is im going to set in excel sheet.

Thank you!!!

@Dee

First and foremost make sure that you are saving the workbook as a macro-enabled workbook (with the extension ".xlsm") if not then the macros will not be saved. Excel does give a warning when saving but it is too easy to just select the default of saving without macros.

If this is not then case then look at when recording a macro you are initially required to name the macro, any shortcut key, AND crucially where it is to be stored. The storage choices are:

a. Personal Macro Workbook
b. New Workbook
c. This Workbook

It is likely that you've stored it in the wrong place. Which is the best place to store it depends on how you will be using the workbook/distributing the workbook, and is a whole discussion in its own right. As a newbie to macros I suggest you select "This Workbook" for simplicity.

The other possiblity is that it the macro is being stored in a different module. Re-opening a workbook and creating a new macro will create a new module, and although the macro is still there it is in a different module.

Hi,

I am also very new to macros, though I have been using excel for over a decade - I just felt that I never needed macros and have been dodging the bullet for awhile. However, I realise that I need it and Im stuck at the 1st huddle.

I have recorded a macro, which works fine. My issue is every time I close and reopen the file and try to run the macro, its no where to be found.

How do I ensure that the macro is visible to be used in the workbook all the time, please help....

Great help, today prepared my first VBA macro based dashboard and got soe help from here.. thanks

@M3NT4L

You can replace the code:

Sheets("Repair Evaluation Data").Select
Sheets("Repair Evaluation Data").Columns("C:C").Select
Selection.Copy
Sheets("Repair Roster").Select
Sheets("Repair Roster").Range("L1").Select
ActiveSheet.Paste

With a single line of code:

Sheets("Repair Evaluation Data").Columns("C:C").Copy _
Destination:=Sheets("Repair Roster").Range("L1")

(this can be all on one line but I broke the line for display purposes)

As this does not use the Selection object or method the display will not change.

I"m new to macros in excel and finding that they are extremely helpful, but have run into a knowledge issue.. (my lack of it :) )

Below is a macro that I had running perfectly, then I tried to add to it using the 'record macro' function hence all of the 'selects' The first portion is filter a table based on a cell value 'c4:e4' Runs fine....

The second portion I'm trying to run is a copy of a range and paste it to another sheet... It works, but it does not hide the screen switches and I need to hide the sheets.

Any help is appreciated! Thanks



Private Sub Worksheet_Change(ByVal Target As Range)

Const DropDown = "c4"

Const TableSheet = "Repair Evaluation Data"

Const TableRange = "A1"
If Not Intersect(Range(DropDown), Target) Is Nothing Then
Application.EnableEvents = False
If Range(DropDown).Value = "" Then
Worksheets(TableSheet).ShowAllData
Else
Worksheets(TableSheet).Range(TableRange).AutoFilter _
Field:=8, Criteria1:=Range(DropDown).Value
End If
End If
Application.EnableEvents = False
Range("C4:E4").Select
Sheets("Repair Evaluation Data").Select
Sheets("Repair Evaluation Data").Columns("C:C").Select
Selection.Copy
Sheets("Repair Roster").Select
Sheets("Repair Roster").Range("L1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("$L$1:$L$1041020").RemoveDuplicates Columns:=1, Header:= _
xlYes
Sheets("Repair Center Dashboard").Select
Application.EnableEvents = True

End Sub

As chuck mentioned that we can run the macro with hidden sheets, I tried it an the macro ran perfectly well.However when I tried to run the macro line by line using the debugger it gave me error on the line which accessed hidden sheet.
Does any one know why is this so

I ran a small test to verify: In fact, you can select cells on a hidden sheet... if you activate the sheet first.

So, apparently the error does not come from trying to select a cell on a sheet that is hidden, but rather, from trying to select a cell on a sheet that is not the active sheet.

Therefore, if you really want to select cells on a hidden sheet for some reason, then you can activate that sheet (but leave it hidden).

I do this in a case where I run a large query and subsequent calculations in the background (on another sheet), but I don't want it to affect the user's screen. Now, I normally don't use the select method at any time on this hidden sheet, but I ran a small test to prove that it works without setting an error.

@Bryan: Thanks for confirming what I thought, and explaining why it works!

@JMJ: You actually don't need to reset Application.ScreenUpdating to True at the end, because it is automatically set to True when code stops executing (a few other settings reset as well... Application.DisplayAlerts comes to mind).

The same can be said of local object variables (ranges, worksheets, etc); some people consider it "good coding practice" to clear out and reset everything at the end of the macro, but other people point out the VBA garbage cleaner is going to do this for you anyway!

Same here, I have no problems running my macros on hidden sheets. I almost never select any ranges, so it's not a problem.

Regards

You said "Make sure that before ending the macro, however, you set the ScreenUpdating property back to True."

In fact, I never, ever, noticed any difference when doing so or omitting this instruction: When the macro ends, the screen can be refreshed anyway!

You can even perform the equivalent of pressing a button on a hidden sheet (i.e., run the button's macro) without ever unhiding the sheet. To "press a button" on a hidden sheet, use something like:
Set OriginalWorksheet = ActiveSheet
Sheet3.Activate
Application.Run ("'" & ThisWorkbook.Name & "'!" & "Sheet3.CommandButton1_Click")
OriginalWorksheet.Activate

Note that in the Application.Run command, you must use the sheet's code name that Excel gave it, not it's visible name that you gave it.

Interstingly, although you can't select cells in a hidden sheet, you can activate the sheet while it is still hidden. Many of my subroutines are written generically, so that the same code can run on different sheets. To do this, I rely heavily on the "Activesheet" object. So, for me it was important to activate the sheet before running the code so that it runs on the correct sheet. For simpler tasks, that may not be necessary. However, remember that if you don't explicitly specify the sheet, VBA often defaults to the Activesheet.

The macro recorder is excellent for figuring out how to do things you didn't know how to do, but one of the greatest downsides is you think you need to select everything to use it!

I, too, have macros that access cell values on hidden worksheets without any problems. I often have a web query that returns results to a hidden calculation page before then extracting the retrieved data.

BUT as Rik says your macros/VBA code must NOT use the "Select" method for accessing a cell or range of cells. Otherwise this will resulting in a run-time error.

My macros work with very hidden sheets with no problems but I don's select
these sheets or cells on in.
I use statements like:
var1=range(c1,c2).value
for i=lbound(var,1) to ubound(var,2)
var(i,1)=sqr(var(i,1))
next i
range(c1,c2).formula=var1
range(c1,c2).sort ...
Kind regards