?
Solved

Floating Dialog Box

Posted on 2014-02-01
16
Medium Priority
?
326 Views
Last Modified: 2014-02-06
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
0
Comment
Question by:Bright01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
  • +1
16 Comments
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 39827220
Do you mean you want to see the buttons even when you scrolldown? If yes, how about freezing the top 2 rows?
0
 
LVL 81

Accepted Solution

by:
byundt earned 1200 total points
ID: 39828735
Another approach is to use a modeless userform (one in which the ShowModal property is False. These can contain your command buttons, and are repositionable on the screen. You can scroll the worksheet underneath the userform, and the userform will always remain visible.

The attached file has a button to display the modeless userform. That userform has a button that runs a macro to display Hello World.

Yet another approach is to put buttons on the ribbon to launch your macros. This will look more professional, but also involves more work on your part. Microsoft Excel MVP Ron de Bruin shows how at http://www.rondebruin.nl/win/s2/win013.htm  "Enable or Disable custom Ribbon buttons in Excel 2007-2013"

There are a number of webpages on Mr. de Bruin's website that discuss different aspects of manipulating the ribbon. The index page is at http://www.rondebruin.nl/win/section2.htm
Floating-command-buttonQ28354226.xlsm
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39829009
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Bright01
ID: 39829211
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.
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 800 total points
ID: 39829293
Without coding - No, I don't believe so, I certainly didn't find a way.

Fairly simple coding though, right click on the Worksheet Tab and select ViewCode. Copy and paste the following into the sheet that appears.

Private Sub Worksheet_Activate()
  UserForm.Show
End Sub

Private Sub Worksheet_Deactivate()
  UserForm.Hide
End Sub

Change the UserForm to the name of the Form.

Thanks
Rob H
0
 

Author Comment

by:Bright01
ID: 39829453
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.
0
 

Author Closing Comment

by:Bright01
ID: 39829513
Got it!  Needed to re-read your directions about putting it in the sheet!

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

B.
0
 

Author Comment

by:Bright01
ID: 39830144
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.
0
 
LVL 81

Expert Comment

by:byundt
ID: 39830246
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?
0
 

Author Comment

by:Bright01
ID: 39830356
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.
0
 

Author Comment

by:Bright01
ID: 39830360
here's the file.
Floating-Command.xlsm
0
 
LVL 81

Expert Comment

by:byundt
ID: 39831156
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
0
 

Author Comment

by:Bright01
ID: 39833886
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.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28356471.html

Thank you,  J.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39833933
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).
0
 

Author Comment

by:Bright01
ID: 39840081
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.
0
 
LVL 81

Expert Comment

by:byundt
ID: 39840402
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question