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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

Frameless Excel Spreadhseet?

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
daiwhyte
Asked:
daiwhyte
  • 7
  • 6
  • 5
  • +1
1 Solution
 
Wayne Taylor (webtubbs)Commented:
Use this in the Workbook_Open event....

    Application.DisplayFullScreen = True

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

Wayne
0
 
patrickabCommented:
Without the toolbars, how is the user going to close the workbook?

Patrick
0
 
daiwhyteAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Wayne Taylor (webtubbs)Commented:
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
 
cyberkiwiCommented:
In the ribbon, just go to View, Full Screen?
0
 
Wayne Taylor (webtubbs)Commented:
cyberkiwi, check the tags - XL 2003 doesn't have a ribbon. Plus I had already mentioned FullScreen, which apparently isn't desirable.
0
 
cyberkiwiCommented:
@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
 
Wayne Taylor (webtubbs)Commented:
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
 
cyberkiwiCommented:
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
 
daiwhyteAuthor Commented:
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
 
cyberkiwiCommented:
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
 
patrickabCommented:
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
 
daiwhyteAuthor Commented:
cyberwiki, your solution is not what Im after (just tried it). Looks like its the VB method.
0
 
daiwhyteAuthor Commented:
Is it possible to set the window location of this screen within the vb also?
0
 
patrickabCommented:
Wayne,

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

Patrick
0
 
patrickabCommented:
>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
 
daiwhyteAuthor Commented:
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
 
patrickabCommented:
>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
 
daiwhyteAuthor Commented:
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
 
patrickabCommented:
>I think if we split points between Patrick and Wayne?

Wayne really as he provided the good answer.
0
 
Wayne Taylor (webtubbs)Commented:
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
 
daiwhyteAuthor Commented:
Thanks Wayne.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now