Solved

Frameless Excel Spreadhseet?

Posted on 2010-08-31
22
475 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
  • 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
 
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
IT, Stop Being Called Into Every Meeting

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 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Note: You must have administrative privileges in order to create/edit Roles. Salesforce.com (http://www.salesforce.com/) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales and marke…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

746 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

13 Experts available now in Live!

Get 1:1 Help Now