+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 17

Update userform when user hides sheets

  1. #1
    Registered User
    Join Date
    12-09-2017
    Location
    israel
    MS-Off Ver
    windows 10
    Posts
    15

    Update userform when user hides sheets

    Hi,
    I have a macro that loads a userform (moduless). As long as this userform is loaded, I need to trigger another macro whenever the user hides sheets manually. But I need this macro to run not on a specific worksheet. The macros should eventually reside in an addin so that they are exposed to all excel files.

    Can I 'catch' the event when the user manually hides sheets so I can update interface of the userform accordingly?

    Thanks!

  2. #2
    Forum Contributor banaanas's Avatar
    Join Date
    08-26-2014
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    187

    Re: 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.
    Tuomas "Banaanas" Savonius
    Trying to give back now when I actually can do some VBA

  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    4,374

    Re: Update userform when user hides sheets

    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.
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you ( It doesn't hurt )

  4. #4
    Registered User
    Join Date
    12-09-2017
    Location
    israel
    MS-Off Ver
    windows 10
    Posts
    15

    Re: Update userform when user hides sheets

    Thanks for your input!
    Yes, I am aware of the fact that there is no actual event for this. And I do not want to put any VBA code in files.

    I, too think the only check i can do is to count the visible sheets and then check the number against a previously saved one (which will be the condition for triggering the update in the userform).

    Another approach I was thinking of was using a timer to check the visible sheets in the active workbook, every second or so, but I don't think it's a good idea (it would solve the problem, but in a clumsy way, in my opinion).

    I really like the idea of triggering the macro as soon as the userform regains focus. But i don't know how to go about doing that sort of check..

  5. #5
    Forum Contributor banaanas's Avatar
    Join Date
    08-26-2014
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    187

    Re: Update userform when user hides sheets

    You can probably use the Me.Activecontrol method:

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

  6. #6
    Registered User
    Join Date
    12-09-2017
    Location
    israel
    MS-Off Ver
    windows 10
    Posts
    15

    Re: Update userform when user hides sheets

    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...
    Last edited by guyglk; 04-16-2019 at 07:52 AM.

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    4,374

    Re: Update userform when user hides sheets

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

  8. #8
    Registered User
    Join Date
    12-09-2017
    Location
    israel
    MS-Off Ver
    windows 10
    Posts
    15

    Re: Update userform when user hides sheets

    This would require the user to refresh the form. I'm looking to have the user do nothing (except for regaining focus to it manually as one would with a 'regular' window that's outta focus) that would also refresh the form.

    I don't wanna make the user remember that they have to refresh the form themselves.

    But thanks anyway!

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    4,374

    Re: Update userform when user hides sheets

    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.

  10. #10
    Registered User
    Join Date
    12-09-2017
    Location
    israel
    MS-Off Ver
    windows 10
    Posts
    15

    Re: Update userform when user hides sheets

    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.
    Last edited by guyglk; 04-17-2019 at 12:50 PM.

  11. #11
    Registered User
    Join Date
    12-09-2017
    Location
    israel
    MS-Off Ver
    windows 10
    Posts
    15

    Re: Update userform when user hides sheets

    Hey guys,
    I'm trying to attach the file...but when I click the attach icon it shows a thin menu (i guess...) with nothing in it. How can I attach the file then, please?

  12. #12
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    4,374

    Re: Update userform when user hides sheets

    Here's the way
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    12-09-2017
    Location
    israel
    MS-Off Ver
    windows 10
    Posts
    15
    here you go..
    Attached Files Attached Files

  14. #14
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    4,374

    Re: Update userform when user hides sheets

    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?

  15. #15
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    4,374

    Re: Update userform when user hides sheets

    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
    Attached Files Attached Files

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1