Solved

How to make floating worksheet using VBA

Posted on 2014-04-12
11
2,704 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 27

Assisted Solution

by:MacroShadow
MacroShadow earned 100 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
 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 100 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 205 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 81

Assisted Solution

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

937 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

6 Experts available now in Live!

Get 1:1 Help Now