Solved

How to make floating worksheet using VBA

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

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 26

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 80

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 80

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 80

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 80

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
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;…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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.

706 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

19 Experts available now in Live!

Get 1:1 Help Now