Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to make floating worksheet using VBA

Posted on 2014-04-12
11
Medium Priority
?
3,804 Views
Last Modified: 2014-04-15
Hi Team,
I want to make floating worksheet. Floating worksheet means that It should appear like popup.
Lets say on worksheet 1 there is button control and when user clicks on that button worksheet 2 appears like a popup.

Is there any way to achieve that ?

Hopefully looking forward to hearing from you.
0
Comment
Question by:satmisha
  • 5
  • 4
  • 2
11 Comments
 

Author Comment

by:satmisha
ID: 39996679
Just to add I am not looking for userform/ribben control to achieve this.
0
 
LVL 28

Assisted Solution

by:MacroShadow
MacroShadow earned 400 total points
ID: 39996724
Not really. You can hide all worksheets besides the one you want to appear as a popup and then set the WindowState to xlNormal, that is the closest you can get.
0
 

Author Comment

by:satmisha
ID: 39997518
Thanks for your reply.

WindowState to xlNormal actually  change the size of entire workbook. Can I change the size of only Active worksheet only rather entire workbook ?
0
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!

 
LVL 28

Assisted Solution

by:MacroShadow
MacroShadow earned 400 total points
ID: 39997639
No. That's why I suggested to hide all the other worksheets.
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 820 total points
ID: 39998247
One alternative is to take a picture of the cells on the other worksheet, then paste that picture in the first worksheet.

Here is code to take a picture, paste it in the first worksheet, name it, and then delete it when desired.
Sub SheetAsPopup()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim iShp As Long
Dim shp As Shape
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
ws2.Range("A1:G11").CopyPicture Appearance:=xlScreen, Format:=xlPicture
ws1.Range("E4").Select
iShp = ActiveSheet.Shapes.Count
ActiveSheet.Paste
Set shp = ActiveSheet.Shapes(iShp + 1)
shp.Name = "Sheet2Popup"
End Sub

Sub ClearSheetPopup()
ActiveSheet.Shapes("Sheet2Popup").Delete
End Sub

Open in new window

0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 820 total points
ID: 39998270
If you don't want the gridlines on the picture, change the CopyPicture statement to use Appearance:=xlPrint

If you want to hide the cells underneath the picture, then just set the fill color of the shape to white.

Both alternatives are shown in the snippet below.
Sub SheetAsPopup()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim iShp As Long
Dim shp As Shape
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
ws2.Range("A1:G11").CopyPicture Appearance:=xlScreen, Format:=xlPicture     'Has gridlines
'ws2.Range("A1:G11").CopyPicture Appearance:=xlPrinter, Format:=xlPicture    'No gridlines
ws1.Range("E4").Select
iShp = ActiveSheet.Shapes.Count
ActiveSheet.Paste
Set shp = ActiveSheet.Shapes(iShp + 1)
shp.Name = "Sheet2Popup"
With shp.Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorBackground1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Transparency = 0
    .Solid
End With
End Sub

Sub ClearSheetPopup()
ActiveSheet.Shapes("Sheet2Popup").Delete
End Sub

Open in new window

0
 

Author Comment

by:satmisha
ID: 39998367
Thanks a lot for your code.

But issue with that user needs to input few values in that sheet.

for example.
- On sheet1 user clicks on button 1
- sheet2  appears we hide sheet1
- User provide some input on sheet2 and clicks OK button
- On sheet1 sum of user inputs appears, and we hide sheet2.

So if we take sheet2 as picture on sheet1 then user would not able to provide his input.

Hope this explains what is required.
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 820 total points
ID: 39999065
The functionality that you describe (push a button and Sheet2 appears instead of Sheet1, do stuff, then push another button and Sheet1 is restored) is provided by the sheet tabs already. No code is required.

A different approach would be to display two windows, the original one in the background and a smaller one on top of it in the foreground. You could then make your modifications to Sheet2, then click in Sheet1 and thereby dismiss Sheet2. To make this work:
1.  Use the View...New Window menu item to create a second window for the workbook
2.  Resize the second window so it is smaller than the first
3.  Use the sheet tabs to switch the second window so it is showing Sheet2
4.  Click a cell in the first window to activate it, thereby hiding the second window
5.  Save the workbook

Having made the above preparations, you could then use the following code to activate the second window (and thereby display it floating on top of the first window):
Sub ToggleSecondWindow()
If ActiveWorkbook.Windows.Count > 1 Then ActiveWorkbook.Windows(2).Activate
End Sub

Open in new window


Note: You will want to use the File...Close menu item to close the workbook instead of clicking the "X" at upper right corner of the window.

Excel 2010 will remember the different sizes of the two windows, but Excel 2013 doesn't. Any user with Excel 2013 will need to resize the second window each time the workbook is opened so the macro works with a floating worksheet on top of the first one. There may be a way to resize the second window programmatically, but I don't know it.
TwoWindowsToggleQ28411277.xlsm
0
 

Author Comment

by:satmisha
ID: 40000345
Thanks a lot for your valuable input. But appears like purpose is not getting solved. Let me try to explain little bit more.

I want worksheet2 appears like a popup though it is not popup it is simple worksheet.

Requirement: I have dynamic table like on worksheet2

E.Name      Strength            Percentage(Input field)  
A                      2                          10%
B                      3                          20%
C                      4                          15%
    Total            9                           35%  

This table will appears as worksheet2  when user click on button on excel worksheet1. User will provide input in third column and click on OK button of worksheet2. Sum of value of second column will go to the worksheet1-->Cell1.

With the code/sample that you sent I am in doubt whether I could achieve this ? Please suggest.

I hope this gives you better understanding.

Looking forward to hearing from you
0
 
LVL 81

Accepted Solution

by:
byundt earned 820 total points
ID: 40000433
MacroShadow has already told you that you cannot treat a worksheet like a popup. Excel doesn't work that way.

The best we can do is offer workarounds. If you don't like them, remember that MacroShadow has already said it can't be done.

Given your purpose, your choices are:
1.  Switch to worksheet2 using the one-line macro
Worksheets("Sheet2").Select

When you are done, return to the first worksheet with another one-line macro
Worksheets("Sheet1").Select

2.  Set up your workbook with two windows, a larger one showing Sheet1 and a smaller one showing Sheet2. The macro would then select the second window, letting you see both worksheets. You return by clicking on a cell in Sheet1.

3.  Set up a userform that contains your input table. When the userform is dismissed, any changes would be written to Sheet2. It isn't at all what you asked for, but this would be a more robust way of handling the matter than option 2. A userform is somewhat fiddly to set up, so I prefer to mock one up after I have a copy of your actual workbook. But you may like seeing what userforms can do on Microsoft Excel MVP Debra Dalgleish's webpage on them http://contextures.com/xlUserForm02.html
0
 

Author Closing Comment

by:satmisha
ID: 40002586
Happy to receive multiple gr8 solution for my prob. So divided the points.

Thanks again.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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.

772 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