Solved

Floating Dialog Box

Posted on 2014-02-01
16
282 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
  • 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 80

Accepted Solution

by:
byundt earned 300 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 31

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
 

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 31

Assisted Solution

by:Rob Henson
Rob Henson earned 200 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 80

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 80

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 31

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 80

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now