Link to home
Start Free TrialLog in
Avatar of Bright01
Bright01Flag for United States of America

asked on

Floating Dialog Box

EE Pros,

Here's a challenge.  I have a WS that has an outline form for 4 levels.  As it expands, I need to access the macros to collapse and expand the outline form.  Unfortunately, this ability is at the top of the WS.  Is there a way to create a floating Box that has the buttons that activate the Macro as the outline expands and contracts?

Seems simple...... completely beyond my talents!  Can you help?

Thank you in advance,

B.
Floating-Dialog-Box-v1.xlsm
Avatar of Peter Kwan
Peter Kwan
Flag of Hong Kong image

Do you mean you want to see the buttons even when you scrolldown? If yes, how about freezing the top 2 rows?
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I had a file some time ago that used a modeless userform but had issues with it. It stayed visible when changing between worksheets and even between workbooks.

I was able to put in programming on individual sheets that needed the form that opened the form when the sheet was activated and closed it when not required; likewise had code to show/hide the form when activating / deactivating the workbook.

Thanks
Rob H
Avatar of Bright01

ASKER

B.  --- >  Brilliant!  

However, Rob is right!  It stays on each WS.  Which is interesting and helpful.  Can this be limited to the specific WS without coding?

Thank you,

B.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Rob,

I tried it but couldn't get it to work.  Do I keep the original Macro also or do I delete it?  The button is tied to the Macro.

B.
Got it!  Needed to re-read your directions about putting it in the sheet!

Thank you both..... this will be very helpful.

B.
B. & R.,

One minor issue; when you fire the macro that shows the dialog box, your scrolling is locked for some reason.  Can you comment on how to fix the ability to scroll once you bring up the Form?

Thank you,

B.
I have no trouble scrolling with Excel 2013, 32-bit when I use Rob's code to display and hide the userform. Which version of Excel are you using?
Brad,

I'm running Excel 2010.  I must be doing something stupid.  Here is the file; I put Rob's code in and I can use the scroll on my mouse.  It freezes the scrolling capability (the Form works).

I've attached the modified spreadsheet if you want to take a look.

B.
here's the file.
Floating-Command.xlsm
Jim,
Instead of my usual trackpad, I installed a Dynex wired optical mouse (brand new). I then opened your workbook in Excel 2010 32-bit in Windows 7 Pro.

I switched between Sheet1 and Sheet2. On Sheet1, the scrollwheel did nothing until I clicked on a worksheet cell. It then scrolled the worksheet as desired.

I believe that when you switch from Sheet2 to Sheet1, the userform has the focus. That's why the scrollwheel doesn't do anything--because the userform isn't large enough to need scrolling. By clicking on a cell in the worksheet, the worksheet receives focus. That's why the scrollwheel now works.

Brad
B.,

I had to author another question.  Your code works great!  My production version however has a 4 level outline which may be "tripping" me up.

Here's the question link if you're game.

https://www.experts-exchange.com/questions/28356471/Problem-with-Floating-Form.html

Thank you,  J.
Now you come to mention it, that's one if the issues I also had; even though the sheet seemed to have focus ie not the form, I had to click a cell to get full navigation back (scroll or cursor keys).
B.

I'm going to author another question around this.  Here's the issue.  Once you launch the form (and it's really nice to have it on the screen when going through a lot of material) there is an issue.

The form stays on the screen even if you change worksheets or workbooks.  How do you get it to simply adhere to the WS it is tagged to?

j.
Does the code in my Comment in http:/Q_28356471.html#a39833959 not take care of this problem?

The code in the sample workbook in that comment is a variation on robhenson's code from this thread for the Worksheet_Activate sub, and exactly his code for the Worksheet_Deactivate. It displays the userform when the worksheet is active, but switches focus to the worksheet. It hides the userform when the worksheet is not active.

A userform continues to display until you dismiss it, either by closing (or hiding) it programmatically, or by clicking the X in the top right corner of its window. robhenson's code is hiding the userform when you leave the worksheet. MartinLiss showed how to close a userform in that other thread with his Unload statement.

The difference between hiding and closing has to do with whether the userform_initialize sub runs each succeeding time you show the userform. It also differs if you want to keep any user selections (option buttons, checkboxes, comboboxes, textboxes) when you display the userform a second time. If the userform only contains command buttons, the two approaches achieve the same result.