One sheet Excel document...

[TonyU]

[tt] Hello all, I would like to know if it's possible to display a one sheet excel documents without any toolbars at all.

This is a documents I have for users to fill out and would like to display it without toolbars.

Thanks

[tt]"A Successful man is one who can build a firm foundation with the bricks that others throw at him"[/tt]

​

 

[rmikesmith]

TonyU,

Try the following

Sub HideToolbars()
Dim CBar As CommandBar

On Error Resume Next
For Each CBar In Application.CommandBars
  If (CBar.Name <> &quot;Worksheet Menu Bar&quot;) And CBar.Visible Then
    CBar.Visible = False
  End If
Next CBar

End Sub

HTH.
M. Smith

For

 

[TonyU]

[tt]That worked fine. But, how can I apply to an specific docuemtn when the doc is opened by the user.??

My document name is
Survey.xls

[tt]"A Successful man is one who can build a firm foundation with the bricks that others throw at him"[/tt]

​

 

[rmikesmith]

TonyU,

1. From the VB Editor, double-click on ThisWorkbook to activate its code module.
2. In the code pane, select Workbook from the left dropdown. This will automatically generate an empty procedure that represents the Open event handler for the workbook. Insert the HideToolbars procedure name, like this:

Private Sub Workbook_Open()
  HideToolbars
End Sub

Everytime the user opens this workbook, Sub HideToolbars will run.

Regards,
M. Smith

 

[TonyU]

[tt]I appreciate your assistance rmikesmith, but I'm having a problem making the code to only apply to the document I need.

I followed your last instructions and that worked great, but when I go to start/program files/excel, the workbook is opened without toolbars exept for the Worksheet menu bar of course.

[tt]"A Successful man is one who can build a firm foundation with the bricks that others throw at him"[/tt]

​

 

[rmikesmith]

TonyU,

I should have realized you would need to restore those toolbars.
Perhaps the safest way to do this is to save a list of visible toolbars on a worksheet, hide them, then restore them when the workbook is closed. Here's how: first insert a new worksheet and give it a name, say wksTB. Hide this worksheet. Modify the HideToolbars procedure to look like the following:

Sub HideToolbars()
Dim CBar As CommandBar
Dim Count As Integer

On Error Resume Next
Count = 0
With Thisworkbook.Worksheets("wksTB")
  For Each CBar In Application.CommandBars
    If (CBar.Name <> &quot;Worksheet Menu Bar&quot;) And CBar.Visible Then
      Count = Count + 1
      .Cells(Count,1).Value = CBars.Name
      CBar.Visible = False
    End If
  Next CBar
  .Cells(1,2).Value = Count
End With
End Sub

Create a new Procedure:

Sub RestoreToolbars()
Dim i As Integer

On Error Resume Next
With ThisWorkbook.Worksheets("wksTB")
  For i = 1 To .Cells(1,2).Value
    Application.CommandBars(.Cells(i,1).Text).Visible = true
  Next i
  .Cells.Clear
End With
End Sub

Call RestoreToolbars in the Workbook_BeforeClose event procedure. If there is a chance for other workbooks to be open at the same time as this one, you may want to put a call to RestoreToolbars in the Workbook_Deactivate handler and HideToolbars in the Workbook_Activate handler. All of these additional event handlers are also in the code module for ThisWorkbook. Good Luck!

Regards,
M. Smith