Update userform when user hides sheets

There is no event that fires when a sheet is hidden to my knowleadge.

The only thing that I can think of would getting the amount of worksheets that aren't hidden and store the value somewhere (a helper sheet or cell) when workbook is opened.
Then I would use the Workbook_SheetActivate event that fires each time a new worksheet is selected by the user. When it is fired -> loop all worksheets and crossreference if the amount is changed. If it is different run the second macro and store to new value to the helper cell.

Please Login or Register  to view this content.

An Event trigger in a file requires VBA code to be added and I do not know if that is an option
The worksheet counter is the best option, since the useform 'belongs' to another file as well as the code, you would need a refresh action when the form gets the focus again that checks the active file for visible/hidden worksheets, this also makes in unnecessary to add macro code to non-macro files.

You can probably use the Me.Activecontrol method:

https://www.ozgrid.com/forum/forum/h...-control-focus

Thanks!
but this (after visiting the link) relies on checking particular controls being in focus, which does not seem to help catch the moment the userform itself regains focus.
This would be the equivalent of placing a 'refresh button' in the useform, by which the user would have to click in order to refresh the userform (which I basically would like done automatically).
I have also read some more on this issue online (about checking if userform is in focus) and turns out it's quite a headache... Maybe with no conventional / straightforward solution... at least that's what I take from what I have read in several forums.
Shoot...

and have you tried Userform_Click ? The moment a mocus click is done on the userform ?

We've been posting answers and assumptions but what if you first tell us what the userform shows?
The file containing the userform what does it do except show the userform?
What type of Excel file? xlam (Addin type)
Maybe a sample of this file will help to see what you're trying to do and might lead to workable answers.

Sure...
I will also post the file (can't at the moment). here is how this this should work (just to give you the big picture) :

File info:
Excel office 2007, xlsm
The file contains 7 sheets (1 through 7 - just to work with something.. )
In sheet1 I have a rectangular shape with a macro assigned to show Userform1.

Userform1's module is 0 when loaded (moduless).
This form only has one button captiond "Sheets...(1)"
This button lets the user:
1. Know how many sheets are currently "in scope" by the number in brackets (by default there has to be at least 1 sheet in scope )
2. Edit the scope of sheets (by clicking the button, userform3 gets loaded).

Userform3's module is 1 when loaded.
In it I have two listboxes.
Listbox1 (a multiselect) , lists all of the currently visible sheets.
Listbox2 (a single select) , lists all of the currently hidden sheets.

The user can then selects or deselect sheets to define the scope of sheets (so that another macro can run on only the selected ones).
The user can also unhide a sheet by double clicking a sheet's name in listbox2 (which unhides it and adds it to listbox1 so it's available for selection).
Userform3 also has an OK button to save the new scope or Cancel to ignore.
Once the user clicks the OK button, userform3 is hidden and the caption of the button in Userform1 ["Sheets...(1)"] gets updated to reflect the number of the sheets the user has selected (so that the user can easily tell how many sheets will be affected, aka "the scope").

But, since userform1 is moduless, the user can still mess a round with the workbook. If they happen to hide sheets, some of those sheets might have been selected earlier (when they were still visible..).

So if say, the user selected 3 sheets in userform3 (sheets2, sheet3 and sheet6), the 'sheets' button will read "Sheets...(3)".
But, if the user suddenly deceides to hide sheet3 (the user has his /her reasons for doing so...), Sheet3 should no longer be part of the scope, and so the 'sheets' button should read
"Sheets...(2)" to indecate the change (by a macro that's triggered of course, or as soon as Userform1 regains focus).

I did not discribe the entire structure of the code of course, which has some variables and arrays and other stuff going on, to save and define alot of the processes that undergo.. I will soon post the file for that.

Here's the way

Let's see if I understand.
The userform should be activated at all times, you could have a workbook_open event to start if so you do not need to to start it.
If the form needs to be active at all times you can use a activate and deactivate to update the form's data.
Is that an idea?

I played around some with your code, added Option Explicit to force dimension of variables corrected some missing dim statements.
The form is load on open and will force unload on closing or deactivation
I added a label with the caption showing the hidden sheet count and when you hide a worksheet or unhide a sheet the counter will update
I don't know why you did not include a double click even to hide the selected record in Listbox 1 but well, you'll have your reason for that.
I renamed Module1 and you do not need to mention the Module name when invoking a macro

Thank you so much keebellah for your comments!
I will look into it when I get the chance

A few things about what you wrote in your reply:

I don't need the userform activated/showing at all times. This form should be loaded only when pressing another button, perhaps in the ribbon, where I will put it.

The "sheets" button is just another setting option before the user can run a "reverse columns" macro which is aimed at reversing certain ranges' column order (while keeping every formatting aspect intact in them), in the sheets the user has selected (so that he/she doesn't have to run the "reverse column" macro manually for multiple sheets.


I only stated the moduel name cause it makes readability easier. When a string of code stands on it own, I may confuse it with a variable when it is not.
This also helps to immediately identify where it's coming from.. instead of using 'definition' which throws you off the scope of code that you're in). Is this bad practice or merely stating redundancy?

I should probably have a hide sheet option too.. Didn't think about that..

Again thank you, much appreciated!

First thing is give the modules a fitting name, defaults Module1 Module2 etc are non-descriptive.
A variable of a macro is not important, but if you add the module's name it makes it very maintenance unfriendly
Happy coding
Don'r forget to mark is SOLVED

Hey,
I looked into the changes you have made, and most are welcome! Thank u!

One thing I do need to ask.
I have noticed you added macros to these events: This Workbook (open) and Sheet1 (Worksheet Changed).

My question is this:
Once this file gets turned into an addin, how are these factored in?
I mean, at the moment, these work as expected. They get fired up 'cause it's all still relative to the file they reside in.

But what happens when the file is converted into an addin? How will these specific event function? Will they ever get triggered? I assum that the Open even will be triggered whenever a user opens any excel file ('cause an instence of the addin will get loaded as well every time), but what about the Worksheet Changed event of Sheet1? It works wonderfully well only if Sheet1 of the original file (with the emmbeded macros in it) is changed.
Will it get triggered by an indirect file? Can it?

This Will NOT work if the file is converted to an add in because worksheets are no longer visible
The strategy is all together different

You will have to create buttons in the Custom Ribbon of the AddIn to open a a file and to navigate the file only then will you be able to include a trigger (in the AddIn) to do the worksheet count
The Userform as you have now would have to be part of the addin and open at all times and place the buttons there.

I see.
It seems like I need to figure some stuff out before I can use this as an AddIn.
Thank you so much for all your help !

Am putting a small sample together to help you