add a print key only

  • Thread starter Thread starter mikegospo
  • Start date Start date
M

mikegospo

I asked a question earlier about restoring my command menu. I got it t
work. I am using this program. I know want to make it just leave
print icon of some type. Here is the code I am using

Private Sub Workbook_Activate()
Dim bar As CommandBar
For Each bar In Application.CommandBars
bar.Enabled = False
Next
End Sub

Private Sub Workbook_Deactivate()
Dim bar As CommandBar
For Each bar In Application.CommandBars
bar.Enabled = True
Next
End Sub


I guess I need one in between to add the print menu.
Mik
 
Hi mikegospo

You can use something like this or use the CTRL-P shortcut with your other code to print


Sub MakeToolBar()
Dim bar As CommandBar
On Error Resume Next
Application.CommandBars("MyToolBar").Delete
On Error GoTo 0
For Each bar In Application.CommandBars
bar.Enabled = False
Next
With Application.CommandBars.Add(Name:="myToolBar", Position:=msoBarTop, MenuBar:=False)
.Protection = msoBarNoCustomize
' Prevent users from accessing the Add or Remove Buttons menu

' this will add a default Excel button
.Controls.Add Type:=msoControlButton, ID:=4
.Visible = True
End With
End Sub


Sub DeleteToolBar()
Dim bar As CommandBar
On Error Resume Next
Application.CommandBars("MyToolBar").Delete
On Error GoTo 0
For Each bar In Application.CommandBars
bar.Enabled = True
Next
End Sub
 
see code inline:

--
Regards,
Tom Ogilvy
Private Sub Workbook_Activate()
Dim bar As CommandBar
For Each bar In Application.CommandBars
bar.Enabled = False
Next
Click to expand...
On Error Resume Next
Commandbars("PrintBar").Delete
On Error goto 0
Application.CommandBars.Add(Name:="PrintBar").Visible = True
Application.CommandBars("PrintBar").Controls.Add _
Type:=msoControlButton, ID:=4, Before:=1
End Sub

Private Sub Workbook_Deactivate()
Dim bar As CommandBar
For Each bar In Application.CommandBars
bar.Enabled = True
Next
Click to expand...
On Error Resume Next
Commandbars("PrintBar").Delete
On Error goto 0
End Sub
Click to expand...
--

Regards,
Tom Ogilvy
------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Didnt work Tom, this is what I put in.

Private Sub Workbook_Activate()
Dim bar As CommandBar
For Each bar In Application.CommandBars
bar.Enabled = False
Next
On Error Resume Next
CommandBars("PrintBar").Delete
On Error GoTo 0
Application.CommandBars.Add(Name:="PrintBar").Visible = True
Application.CommandBars("PrintBar").Controls.Add _
Type:=msoControlButton, ID:=4, Before:=1
End Sub

Private Sub Workbook_Deactivate()
Dim bar As CommandBar
For Each bar In Application.CommandBars
bar.Enabled = True
Next
On Error Resume Next
CommandBars("PrintBar").Delete
On Error GoTo 0
End Sub

It hangs on the last CommandBars command
CommandBars("PrintBar").Delete
 
Add Application before the Commandbars. Worked for me.

Private Sub Workbook_Activate()
Dim bar As CommandBar
For Each bar In Application.CommandBars
bar.Enabled = False
Next
On Error Resume Next
Application.CommandBars("PrintBar").Delete
On Error GoTo 0
Application.CommandBars.Add(Name:="PrintBar").Visible = True
Application.CommandBars("PrintBar").Controls.Add _
Type:=msoControlButton, ID:=4, Before:=1
End Sub

Private Sub Workbook_Deactivate()
Dim bar As CommandBar
For Each bar In Application.CommandBars
bar.Enabled = True
Next
On Error Resume Next
Application.CommandBars("PrintBar").Delete
On Error GoTo 0
End Sub

--
Regards,
Tom Ogilvy

mikegospo said:
Didnt work Tom, this is what I put in.

Private Sub Workbook_Activate()
Dim bar As CommandBar
For Each bar In Application.CommandBars
bar.Enabled = False
Next
On Error Resume Next
CommandBars("PrintBar").Delete
On Error GoTo 0
Application.CommandBars.Add(Name:="PrintBar").Visible = True
Application.CommandBars("PrintBar").Controls.Add _
Type:=msoControlButton, ID:=4, Before:=1
End Sub

Private Sub Workbook_Deactivate()
Dim bar As CommandBar
For Each bar In Application.CommandBars
bar.Enabled = True
Next
On Error Resume Next
CommandBars("PrintBar").Delete
On Error GoTo 0
End Sub

It hangs on the last CommandBars command
CommandBars("PrintBar").Delete


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Now it hangs where I have the asterisk?


Private Sub Workbook_Activate()
Dim bar As CommandBar
For Each bar In Application.CommandBars
bar.Enabled = False
Next
On Error Resume Next
**Application.CommandBars("PrintBar").Delete
On Error GoTo 0
Application.CommandBars.Add(Name:="PrintBar").Visible = True
Application.CommandBars("PrintBar").Controls.Add _
Type:=msoControlButton, ID:=4, Before:=1
End Sub

Private Sub Workbook_Deactivate()
Dim bar As CommandBar
For Each bar In Application.CommandBars
bar.Enabled = True
Next
On Error Resume Next
Application.CommandBars("PrintBar").Delete
On Error GoTo 0
End Sub

End Sub
 
As I said, the code works fine for me. Also, not sure what you mean by
hangs as the code is nested in an error handler.

--
Regards,
Tom Ogilvy

mikegospo said:
Now it hangs where I have the asterisk?


Private Sub Workbook_Activate()
Dim bar As CommandBar
For Each bar In Application.CommandBars
bar.Enabled = False
Next
On Error Resume Next
**Application.CommandBars("PrintBar").Delete
On Error GoTo 0
Application.CommandBars.Add(Name:="PrintBar").Visible = True
Application.CommandBars("PrintBar").Controls.Add _
Type:=msoControlButton, ID:=4, Before:=1
End Sub

Private Sub Workbook_Deactivate()
Dim bar As CommandBar
For Each bar In Application.CommandBars
bar.Enabled = True
Next
On Error Resume Next
Application.CommandBars("PrintBar").Delete
On Error GoTo 0
End Sub

End Sub


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
I get a VB error and it stops on that line. Sorry to keep bothering you
but now my excel is messed up and I lost all tool bars and cant get
them restored. Sorry I feel like a boob.
Mike
 
Do you have Break on unhandled errors selected in VBA in the tools menu
under the options, then under the General Tab?

If you have Break on all errors, that is your problem.

--
Regards,
Tom Ogilvy

mikegospo said:
I get a VB error and it stops on that line. Sorry to keep bothering you
but now my excel is messed up and I lost all tool bars and cant get
them restored. Sorry I feel like a boob.
Mike


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Back
Top