Disable print icon in toolbar??

[StargateFanFromWork]

I've read many posts in the archives and I've managed to figure out how to
disable shortcuts in various spots, but disabling all regular XL2K print
options is eluding me. I'm only being partially successful. I also can't
seem to pinpoint a reference for any code to this in the archives so far.

My question is how do I grey out the regular print icon from the toolbar as
well as the print reference in the FILE menu. The second problem deals with
my customs icons on the toolbar, as well. I do the ^p covered; it's calling
a print macro for this workbook.

Here is what I've managed to make up. I've put this in the workbook module
with the referenced macros in a regular module (though not shown here):

*************************************************************
Private Sub Workbook_Activate()
On Error Resume Next
'Disable shortcut(s) on STANDARD toolbar, EDIT menu and cell SHORTCUT menu
With Application
.CommandBars("Standard").Controls("PRINT").Enabled = False
.CommandBars("Edit").Controls("PRINT").Enabled = False
.CommandBars("Cell").Controls("PRINT").Enabled = False
' Disable keyboard shortcuts
.OnKey "^n", ""
.OnKey "^p", ""
' enable shortcut keys to run macros
' new shortcut #1
.OnKey "^n", "AddNEWrecord"
' new shortcut #2
' enable Shift+^+R to run macro: "InsertROWS"
.OnKey "+^r", "InsertROWS"
' new shortcut #3
.OnKey "^p", "PrintSheet"
End With
End Sub
Private Sub Workbook_Deactivate()
' CommandBars disappear (whilst the workbook remains open, but another is in
view)
On Error Resume Next
'Re-enable shortcut(s) on Standard toolbar, EDIT menu, and cell SHORTCUT
menu
With Application
' these lines with TRUE are needed if you have ones with FALSE above
.CommandBars("Standard").Controls("NEW").Enabled = True
.CommandBars("Edit").Controls("NEW").Enabled = True
.CommandBars("Cell").Controls("NEW").Enabled = True
.CommandBars("Standard").Controls("PRINT").Enabled = True
.CommandBars("Edit").Controls("PRINT").Enabled = True
.CommandBars("Cell").Controls("PRINT").Enabled = True
' Re-enable NEW keyboard shortcut
.OnKey "^n"
' Re-enable Shift+Ctrl+R (just in case this ever exists)
.OnKey "+^r"
End With
End Sub
*************************************************************

One of my printer toolbar icons has a print macro in the personal.xls called
"PrintCurrentPage" assigned to it, the other 2 are icons chosen from the
commands box when customizing the toolbar.

Thank you! D

 

[StargateFanFromWork]

I've read many posts in the archives and I've managed to figure out how to
disable shortcuts in various spots, but disabling all regular XL2K print
options is eluding me. I'm only being partially successful. I also can't
seem to pinpoint a reference for any code to this in the archives so far.

My question is how do I grey out the regular print icon from the toolbar as
well as the print reference in the FILE menu. The second problem deals with
my customs icons on the toolbar, as well. I do the ^p covered; it's calling
a print macro for this workbook.

Here is what I've managed to make up. I've put this in the workbook module
with the referenced macros in a regular module (though not shown here):

Well, right away after work and on personal time I went back to the
archives. It took some digging but I found some solutions and have actually
have quite a bit of success so far. I'm extremely keen on disabling
menu/toolbar printing venues as there are several print options on icons
that I put on the sheet itself. I don't mind if users find ways around
these, and I don't have to lock things up tightly, this is mostly to
encourage users to use the printing options I've set up as they sort the
sheet in various ways, etc., before printing.

Many of the menu items are now greyed out, as well while this sheet is open.
I just tried various bits of code as I found them to see how things worked
and liked some of the results, so I kept those. Here is what I have so far
that goes into the workbook module:

----------------------------------------------------------------------
Private Sub Workbook_Activate()
On Error Resume Next
'Disable shortcut(s) on STANDARD toolbar, EDIT menu and cell SHORTCUT menu
With Application

'Disable described in each comment below:
'FILE MENU: "Print..." (#4) and "Print Preview (#109)
.CommandBars("File").FindControl(ID:=4).Enabled = False
'STANDARD TOOLBAR: generic print icon
.CommandBars("Standard").FindControl(ID:=2521).Enabled = False
'STANDARD TOOLBAR: custom print icons
.CommandBars("Standard").Controls("Print current page").Enabled =
False
.CommandBars("Standard").Controls("Print dialogue box").Enabled =
False

'seems to disable items in the context menu of cells
.ShortcutMenus(xlWorksheetCell).Enabled = False

' Disable these keyboard shortcuts for this workbook
.OnKey "^n", ""
.OnKey "^p", ""
' enable shortcut keys to run macros
' new shortcut #1
.OnKey "^n", "AddNEWrecord"
' new shortcut #2
' enable Shift+^+R to run macro: "InsertROWS"
.OnKey "+^r", "InsertROWS"
' new shortcut #3
' enable ^P to run macro: "PrintOne_SortedByPositionNumber"
.OnKey "^p", "PrintOne_SortedByPositionNumber"
End With
End Sub
Private Sub Workbook_Deactivate()
' CommandBars disappear (whilst the workbook remains open, but another is in
view)
On Error Resume Next
'Re-enable shortcut(s) on Standard toolbar, EDIT menu, and cell SHORTCUT
menu
With Application

'Disable described in each comment below:
'FILE MENU: "Print..." (#4) and "Print Preview (#109)
.CommandBars("File").FindControl(ID:=4).Enabled = True
'STANDARD TOOLBAR: generic print icon
.CommandBars("Standard").FindControl(ID:=2521).Enabled = True
'STANDARD TOOLBAR: custom print icons
.CommandBars("Standard").Controls("Print current page").Enabled =
True
.CommandBars("Standard").Controls("Print dialogue box").Enabled =
True

're-able items in the context menu of cells
.ShortcutMenus(xlWorksheetCell).Enabled = True

' Re-enable NEW keyboard shortcut
.OnKey "^n"
' Re-enable Shift+Ctrl+R (just in case this ever exists)
.OnKey "+^r"
End With
End Sub
----------------------------------------------------------------------

I can live with the above as it stands; however, my question now is one of
curiosity as it would be good to do the next step for myself. I found the
control IDs on an excellent KB article
(http://support.microsoft.com/kb/213552/en-us). There are literally
hundreds on those pages! What I'd like to know is if there is a way to
figure out what control ID is assigned, if any, to any custom icons we put
on the standard toolbar. I have two other print ones other than the main
one on that bar and it would be great if I could grey those out as well for
myself. But no luck so far finding anything in the archives re control IDs
for custom icons. I know that others won't have these on any of their
toolbars, it would just make things neat and tidy for me.

Thank you! D

 

[Dave Peterson]

You could do something like:

Option Explicit
Sub testme()
Dim myBar As CommandBar
Dim myCtrl As CommandBarControl

Set myBar = Application.CommandBars("qwerqwer")

For Each myCtrl In myBar.Controls
MsgBox myCtrl.Caption & vbLf & myCtrl.ID
Next myCtrl
End Sub

I created a temporary toolbar named "qwerqwer" to test.

 

[StargateFan]

You could do something like:

Option Explicit
Sub testme()
Dim myBar As CommandBar
Dim myCtrl As CommandBarControl

Set myBar = Application.CommandBars("qwerqwer")

For Each myCtrl In myBar.Controls
MsgBox myCtrl.Caption & vbLf & myCtrl.ID
Next myCtrl
End Sub

I created a temporary toolbar named "qwerqwer" to test.

That's awesome, thank you. I'll put this code away in my XL2K tips
folder! I think I'm understanding this to be for a completely custom
_toolbar_, correct? Kewl because I do have a couple of those even
though they don't need disabling in this particular workbook <g>.

I didn't find code that worked to get rid of the _whole_ of the
standard toolbar, though, and my custom icons are on it. There were
posts in the archives that seemed to point to disabling that bar and
in the kb there was code, too, but nothing seemed to work unlike what
I found that gets rid of the pulldown menus so that these disappear
while the workbook is up and running. (That worked beautifully with
the pulldowns winking back on when I closed it!) It would neat to
find a way to either get rid of the bar altogether in this same manner
or, even better, that greys out my custom icons on it.

The latter is preferable, which is why I was trying to figure out how
to find the control IDs for them as I couldn't find text that worked
for other print options, whereas using IDs did! If there is no way to
find the IDs for custom icons, or if XL2K doesn't assign specific
control IDs for them, then the alternative would be to completely
knock out the standard bar.

So, also getting out my shovel again and heading over to the archives
<g>. Maybe I'll hit upon better search keywords and will get better
results!

In the meantime, thank you for the code! I find that every bit helps!
D

 

[StargateFan]

You could do something like:

Option Explicit
Sub testme()
Dim myBar As CommandBar
Dim myCtrl As CommandBarControl

Set myBar = Application.CommandBars("qwerqwer")

For Each myCtrl In myBar.Controls
MsgBox myCtrl.Caption & vbLf & myCtrl.ID
Next myCtrl
End Sub

I created a temporary toolbar named "qwerqwer" to test.

[snip]

Well, Dave, your response helped a lot! I've not resolved my problem,
but I have come halfway <g>.

When I saw that you'd created a temporary toolbar to use in this
example, I got to thinking that my method all those years ago of
customizing the standard toolbar was only one way to go about things.
I went back and created a whole new custom toolbar and I dumped all
the icons from the modified Standard one onto it. I then reset the
Standard bar to the default icons and turned it off, and then turned
on my "new" custom toolbar.

The code I'd found yesterday now works when put in the Activate and
De-activate in the workbook module:

.CommandBars("My Standard Toolbar").Visible = False

The "difficulty" here is that the entire toolbar winks out even with
".Visible" used instead of ".Enable" as per the original code. (So
both do the same thing, they actually turn it right off!) This is a
bit extreme but I'll see if I can live with this behaviour with this
workbook. It means using the File pulldown menu for saving the file
as everything like that is on the custom toolbar (I don't want to
create another worksheet icon to do this). But must admit that it is
a means of getting the job done.

But still, the ideal would be to find out the control ID for the two
custom print options on the custom toolbar. I'm still stumped on
finding the control IDs for them, though. I found a kb article with a
tantalizing title, "Sample macros to return ID for a CommandBar
control" here:
http://support.microsoft.com/?scid=http://www.support.microsoft.com/kb/162814/en-us/
but couldn't figure out how to use with toolbars - not even for
built-in ones - as it seems to talk only about menu items.

Here's example code for the Exit command in the File pulldown menu
from the article:
*************************************************************************************
Menu Command on a Menu
The following macro example returns the Id for the Exit command on the
File menu of the built-in Worksheet menu bar:

Sub FileExit_Id()

Dim x as Object
set x = CommandBars("Worksheet Menu Bar").Controls("File") _
.Controls("Exit")
MsgBox x.Caption & Chr(13) & x.Id

End Sub
*************************************************************************************
Is there a way to modify the above to get _all_ values for a custom
toolbar which is called "My Standard Toolbar"??? That would do what I
asked last night re control IDs, I believe.

Thank you! I'd appreciate any help. D

 

[Dave Peterson]

Maybe...

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myBar As CommandBar
Dim myCtrl As CommandBarControl
Dim oRow As Long

Set wks = Worksheets.Add

Set myBar = Application.CommandBars("My Standard Toolbar")

For Each myCtrl In myBar.Controls
oRow = oRow + 1
wks.Cells(oRow, 1).Value = myBar.Name
wks.Cells(oRow, 2).Value = myCtrl.Caption
wks.Cells(oRow, 3).Value = myCtrl.ID
wks.Cells(oRow, 4).Value = myCtrl.Index
wks.Cells(oRow, 5).Value = myCtrl.OnAction
wks.Cells(oRow, 6).Value = myCtrl.Tag
wks.Cells(oRow, 7).Value = myCtrl.TooltipText
Next myCtrl
End Sub


There are lots of properties that you can use with controls. You can add them
to the list.

You could do something like:

Option Explicit
Sub testme()
Dim myBar As CommandBar
Dim myCtrl As CommandBarControl

Set myBar = Application.CommandBars("qwerqwer")

For Each myCtrl In myBar.Controls
MsgBox myCtrl.Caption & vbLf & myCtrl.ID
Next myCtrl
End Sub

I created a temporary toolbar named "qwerqwer" to test.

[snip]

Well, Dave, your response helped a lot! I've not resolved my problem,
but I have come halfway <g>.

When I saw that you'd created a temporary toolbar to use in this
example, I got to thinking that my method all those years ago of
customizing the standard toolbar was only one way to go about things.
I went back and created a whole new custom toolbar and I dumped all
the icons from the modified Standard one onto it. I then reset the
Standard bar to the default icons and turned it off, and then turned
on my "new" custom toolbar.

The code I'd found yesterday now works when put in the Activate and
De-activate in the workbook module:

.CommandBars("My Standard Toolbar").Visible = False

The "difficulty" here is that the entire toolbar winks out even with
".Visible" used instead of ".Enable" as per the original code. (So
both do the same thing, they actually turn it right off!) This is a
bit extreme but I'll see if I can live with this behaviour with this
workbook. It means using the File pulldown menu for saving the file
as everything like that is on the custom toolbar (I don't want to
create another worksheet icon to do this). But must admit that it is
a means of getting the job done.

But still, the ideal would be to find out the control ID for the two
custom print options on the custom toolbar. I'm still stumped on
finding the control IDs for them, though. I found a kb article with a
tantalizing title, "Sample macros to return ID for a CommandBar
control" here:
http://support.microsoft.com/?scid=http://www.support.microsoft.com/kb/162814/en-us/
but couldn't figure out how to use with toolbars - not even for
built-in ones - as it seems to talk only about menu items.

Here's example code for the Exit command in the File pulldown menu
from the article:
*************************************************************************************
Menu Command on a Menu
The following macro example returns the Id for the Exit command on the
File menu of the built-in Worksheet menu bar:

Sub FileExit_Id()

Dim x as Object
set x = CommandBars("Worksheet Menu Bar").Controls("File") _
.Controls("Exit")
MsgBox x.Caption & Chr(13) & x.Id

End Sub
*************************************************************************************
Is there a way to modify the above to get _all_ values for a custom
toolbar which is called "My Standard Toolbar"??? That would do what I
asked last night re control IDs, I believe.

Thank you! I'd appreciate any help. D

 

[Tom Ogilvy]

From excel VBA help for the ID property of a commandbarbutton:

The value of the Id property for all custom controls is 1.

You address custom controls either by positon or by the caption.

demo'd from the immediate window:

commandbars("Custom 1").Controls.Add Type:=msoControlButton
? commandbars("custom 1").Controls(3).builtin
False
? commandbars("custom 1").Controls(3).id
1
commandbars("custom 1").Controls(3).Caption = "AAA"
? commandbars("custom 1").Controls("AAA").Caption
AAA
? commandbars("custom 1").Controls("AAA").Index
3

--
Regards,
Tom Ogilvy

You could do something like:

Option Explicit
Sub testme()
Dim myBar As CommandBar
Dim myCtrl As CommandBarControl

Set myBar = Application.CommandBars("qwerqwer")

For Each myCtrl In myBar.Controls
MsgBox myCtrl.Caption & vbLf & myCtrl.ID
Next myCtrl
End Sub

I created a temporary toolbar named "qwerqwer" to test.

[snip]

Well, Dave, your response helped a lot! I've not resolved my problem,
but I have come halfway <g>.

When I saw that you'd created a temporary toolbar to use in this
example, I got to thinking that my method all those years ago of
customizing the standard toolbar was only one way to go about things.
I went back and created a whole new custom toolbar and I dumped all
the icons from the modified Standard one onto it. I then reset the
Standard bar to the default icons and turned it off, and then turned
on my "new" custom toolbar.

The code I'd found yesterday now works when put in the Activate and
De-activate in the workbook module:

.CommandBars("My Standard Toolbar").Visible = False

The "difficulty" here is that the entire toolbar winks out even with
".Visible" used instead of ".Enable" as per the original code. (So
both do the same thing, they actually turn it right off!) This is a
bit extreme but I'll see if I can live with this behaviour with this
workbook. It means using the File pulldown menu for saving the file
as everything like that is on the custom toolbar (I don't want to
create another worksheet icon to do this). But must admit that it is
a means of getting the job done.

But still, the ideal would be to find out the control ID for the two
custom print options on the custom toolbar. I'm still stumped on
finding the control IDs for them, though. I found a kb article with a
tantalizing title, "Sample macros to return ID for a CommandBar
control" here:
http://support.microsoft.com/?scid=http://www.support.microsoft.com/kb/162814/en-us/
but couldn't figure out how to use with toolbars - not even for
built-in ones - as it seems to talk only about menu items.

Here's example code for the Exit command in the File pulldown menu
from the article:
****************************************************************************
*********
Menu Command on a Menu
The following macro example returns the Id for the Exit command on the
File menu of the built-in Worksheet menu bar:

Sub FileExit_Id()

Dim x as Object
set x = CommandBars("Worksheet Menu Bar").Controls("File") _
.Controls("Exit")
MsgBox x.Caption & Chr(13) & x.Id

End Sub
****************************************************************************
*********
Is there a way to modify the above to get _all_ values for a custom
toolbar which is called "My Standard Toolbar"??? That would do what I
asked last night re control IDs, I believe.

Thank you! I'd appreciate any help. D

 

[StargateFan]

Maybe...

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myBar As CommandBar
Dim myCtrl As CommandBarControl
Dim oRow As Long

Set wks = Worksheets.Add

Set myBar = Application.CommandBars("My Standard Toolbar")

For Each myCtrl In myBar.Controls
oRow = oRow + 1
wks.Cells(oRow, 1).Value = myBar.Name
wks.Cells(oRow, 2).Value = myCtrl.Caption
wks.Cells(oRow, 3).Value = myCtrl.ID
wks.Cells(oRow, 4).Value = myCtrl.Index
wks.Cells(oRow, 5).Value = myCtrl.OnAction
wks.Cells(oRow, 6).Value = myCtrl.Tag
wks.Cells(oRow, 7).Value = myCtrl.TooltipText
Next myCtrl
End Sub

Sorry for the delay, it took some time to get through everything <g>.
I ran above macro yesterday and then incorporated control IDs into the
code. It worked amazingly! I've also added pw protection to vbe and
cleaned up the workbook in general.

I'm not concerned with average user at the office getting around the
few controls I've put in. It's just to keep the workbook as clean as
possible without them easily messing up the file (which they did
extremely well in workbooks I left behind last year in a previous
contract here! <g>). None of them know enough to get around these
things and even if they get someone who does, that person would well
be able to "fix" or change whatever they did or needed to do. So
win-win.

There are lots of properties that you can use with controls. You can add them
to the list.

Great!

The macro above only hitches on a tiny thing. It makes up the
spreadsheet just fine but an error code does come up:

"Run-time error '438':
Object doesn't support this property or method."

and debug brings me to this line in yellow:
"wks.Cells(oRow, 5).Value = myCtrl.OnAction"

It didn't seem to affect anything I needed and the sheet with data
could be created, so I didn't bother with it.

Thanks much for the fabulous help! I'm also very pleased to report
that this workbook is first one I did mostly on my own! <g> That's a
mighty good feeling. I started it Thursday in spare moments and
completed this morning. That's really fast for me for finding
code/procedures, etc.

Cheers! D

You could do something like:

Option Explicit
Sub testme()
Dim myBar As CommandBar
Dim myCtrl As CommandBarControl

Set myBar = Application.CommandBars("qwerqwer")

For Each myCtrl In myBar.Controls
MsgBox myCtrl.Caption & vbLf & myCtrl.ID
Next myCtrl
End Sub

I created a temporary toolbar named "qwerqwer" to test.

[snip]

Well, Dave, your response helped a lot! I've not resolved my problem,
but I have come halfway <g>.

When I saw that you'd created a temporary toolbar to use in this
example, I got to thinking that my method all those years ago of
customizing the standard toolbar was only one way to go about things.
I went back and created a whole new custom toolbar and I dumped all
the icons from the modified Standard one onto it. I then reset the
Standard bar to the default icons and turned it off, and then turned
on my "new" custom toolbar.

The code I'd found yesterday now works when put in the Activate and
De-activate in the workbook module:

.CommandBars("My Standard Toolbar").Visible = False

The "difficulty" here is that the entire toolbar winks out even with
".Visible" used instead of ".Enable" as per the original code. (So
both do the same thing, they actually turn it right off!) This is a
bit extreme but I'll see if I can live with this behaviour with this
workbook. It means using the File pulldown menu for saving the file
as everything like that is on the custom toolbar (I don't want to
create another worksheet icon to do this). But must admit that it is
a means of getting the job done.

But still, the ideal would be to find out the control ID for the two
custom print options on the custom toolbar. I'm still stumped on
finding the control IDs for them, though. I found a kb article with a
tantalizing title, "Sample macros to return ID for a CommandBar
control" here:
http://support.microsoft.com/?scid=http://www.support.microsoft.com/kb/162814/en-us/
but couldn't figure out how to use with toolbars - not even for
built-in ones - as it seems to talk only about menu items.

Here's example code for the Exit command in the File pulldown menu
from the article:
*************************************************************************************
Menu Command on a Menu
The following macro example returns the Id for the Exit command on the
File menu of the built-in Worksheet menu bar:

Sub FileExit_Id()

Dim x as Object
set x = CommandBars("Worksheet Menu Bar").Controls("File") _
.Controls("Exit")
MsgBox x.Caption & Chr(13) & x.Id

End Sub
*************************************************************************************
Is there a way to modify the above to get _all_ values for a custom
toolbar which is called "My Standard Toolbar"??? That would do what I
asked last night re control IDs, I believe.

Thank you! I'd appreciate any help. D

 

[StargateFan]

From excel VBA help for the ID property of a commandbarbutton:

The value of the Id property for all custom controls is 1.

You address custom controls either by positon or by the caption.

demo'd from the immediate window:

commandbars("Custom 1").Controls.Add Type:=msoControlButton
? commandbars("custom 1").Controls(3).builtin
False
? commandbars("custom 1").Controls(3).id
1
commandbars("custom 1").Controls(3).Caption = "AAA"
? commandbars("custom 1").Controls("AAA").Caption
AAA
? commandbars("custom 1").Controls("AAA").Index
3

[snip]

Hi, Tom!

I actually was successful with Dave's code, but this looks mighty
interesting.

I had a lot of trouble with the word wrap so haven't been successful
yet. Thanks for tip on where to find this in help. I tried vbe help
for control IDs, too, in past few days but nothing pertinent came up.
I went to archives, to kb, I went all over! <g> Having more precise
search term like this will bring up what you're showing me, thanks.
I'll give it a try later on today after running some errands.

Thanks! D