Solved

Frameless Excel Spreadhseet?

Posted on 2010-08-31
22
528 Views
Last Modified: 2013-11-15
Hi Chaps,

I have a spreadsheet which pulls data from our CRM, Im wondering if its possible to launch the spreadsheet so its frameless? I only want to show the sheet itself and not the buttons/toolbars.

Thanks
0
Comment
Question by:daiwhyte
[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
  • 7
  • 6
  • 5
  • +1
22 Comments
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 33565972
Use this in the Workbook_Open event....

    Application.DisplayFullScreen = True

Be sure to set it back to false in the BeforeClose event.

Wayne
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33565988
Without the toolbars, how is the user going to close the workbook?

Patrick
0
 

Author Comment

by:daiwhyte
ID: 33566135
All I want is the frame around the workbook which will have the standard X in the top right corner. The excel sheet is quite small so I dont want it to go full screen, just the size of the worksheet.
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 33566275
Paste the below into a regular module. Then call the "HideToolbars" sub from the Workbook_Open event, and the "ShowToolbars" sub from the Workbook_BeforeClose event.

Wayne
Public OrigCommandBars As Collection

Sub HideToolbars()
    Set OrigCommandBars = New Collection
    Dim cb As CommandBar
    For Each cb In Application.CommandBars
        If cb.Visible Then
            If cb.Name <> "Worksheet Menu Bar" Then
                cb.Visible = False
                OrigCommandBars.Add cb.Name
            End If
        End If
    Next
    Application.DisplayFormulaBar = False
End Sub

Sub ShowToolbars()
    Dim cb
    For Each cb In OrigCommandBars
        Application.CommandBars(cb).Visible = True
    Next
    Application.DisplayFormulaBar = True
End Sub

Open in new window

0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33566280
In the ribbon, just go to View, Full Screen?
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 33566314
cyberkiwi, check the tags - XL 2003 doesn't have a ribbon. Plus I had already mentioned FullScreen, which apparently isn't desirable.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33566348
@webtubbs > check the tags - XL 2003

Thanks, I missed that.
There is still Menu->View->Full Screen.  For what it's worth, I am wondering if the author is just after a simple solution and all I see before my post are some VBA solutions.
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 33566443
You may be right, but the only way to "launch the spreadsheet so its frameless" is to use VBA. Full screen only applies to the Application window and is not remembered by a workbook. Plus it has already been eliminated as an option- http:#a33566135
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33566561
I stand to be corrected, but I read that as:

All I want is the frame around the workbook which will have the standard X in the top right corner. => bare minimum.  The X is on the same row as the menu (not toolbar/buttons), so that has to remain.

The excel sheet is quite small so I dont want it to go full screen, just the size of the worksheet. => one of two things
(1) Full screen as in video, frameless borderless, not even the X (see above)
(2) Full screen as in zoomed in to fill full screen size (don't want). Just want worksheet (area) expanded.
Could be a misunderstanding of the term "full screen"

It really depends on your experience as to how you read it, and I could well be barking up the wrong tree.
0
 

Author Comment

by:daiwhyte
ID: 33566765
Ok, a little lost here guys. Im guessing I need to do something with the VB editor but Im not sure what needs inserting where? Can you give me a bit more help guys?
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33566851
Do you just want the borders and toolbars removed, leaving the Excel window with just Menu and [X] at the top and worksheet area filling the rest of the screen?  If this is one-off, you can go to Menu->View->Full Screen.

Otherwise webtubbs can give you the VBA method.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33566872
daiwhyte,

The problem with doing that is that once the command bars are hidden then that applies to all workbooks that are open on that machine. If however only that one workbook is open then you could use the code below to hide or show all the command bars. Adapt the amcros to workbook_open() sub if needed.

Patrick

Sub a_hides_commandbars()
Dim cbar As CommandBar

For Each cbar In CommandBars
    If cbar.Enabled = True Then
        cbar.Enabled = False
    End If
Next cbar

End Sub

Sub a_shows_commandbars()
Dim cbar As CommandBar

For Each cbar In CommandBars
    If cbar.Enabled = False Then
        cbar.Enabled = True
    End If
Next cbar

End Sub

Open in new window

0
 

Author Comment

by:daiwhyte
ID: 33566875
cyberwiki, your solution is not what Im after (just tried it). Looks like its the VB method.
0
 

Author Comment

by:daiwhyte
ID: 33567088
Is it possible to set the window location of this screen within the vb also?
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33567157
Wayne,

Apologies - I never even saw your contribution until just now. Much better than mine anyhow.

Patrick
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33567184
>Ok, a little lost here guys. Im guessing I need to do something with the VB editor but Im not sure what needs inserting where? Can you give me a bit more help guys?

To install a sub in a module:

1) Press ALT+F11
2) Find the workbook name in the VBAProject window (CTRL+R if it's not visible), right-click on the file name
3) Select Insert/Module
4) Double click on the new Module
5) Paste the code into the Module
6) ALT + F11 to return to the worksheet
7) ALT + F8 to select and run the macro
0
 

Author Comment

by:daiwhyte
ID: 33567522
Ok, Ive inserted the sub but its removed the toolbars from all instances of excel. This will affect some users who use the toolbar for day to day functions. Is it not possible to assign the vb just to the spreadsheet Im opening?

0
 
LVL 45

Expert Comment

by:patrickab
ID: 33567742
>Ok, Ive inserted the sub but its removed the toolbars from all instances of excel. This will affect some users who use the toolbar for day to day functions. Is it not possible to assign the vb just to the spreadsheet Im opening?

I told you earlier that would happen, so it should have come as no surprise to you. There is no way of overcoming that problem.
0
 

Author Comment

by:daiwhyte
ID: 33567962
Mmm, ok fair enough - in that case I think we have probably solved this problem the best we can.

I think if we split points between Patrick and Wayne?
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33567981
>I think if we split points between Patrick and Wayne?

Wayne really as he provided the good answer.
0
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 total points
ID: 33575813
You can handle the WindowActivate and WindowDeactivate events to hide a show the toolbars depending on the active workbook.

The below code will go in the ThisWorkbook module. You would still have my previously posted code in a regular module.

Wayne
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ShowToolbars
End Sub

Private Sub Workbook_Open()
    HideToolbars
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    HideToolbars
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    ShowToolbars
End Sub

Open in new window

0
 

Author Closing Comment

by:daiwhyte
ID: 33585316
Thanks Wayne.
0

Featured Post

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Let’s face it: one of the reasons your organization chose a SaaS solution (whether Microsoft Dynamics 365, Netsuite or SAP) is that it is subscription-based. The upkeep is done. Or so you think.
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

636 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