Disabling Print on TOOLBAR

[Dan Gesshel]

Hello.

Okay... so maybe I was a little cocky, but I thought I had this solved.
I found out later I didn't.

I want to temporarily disable the Print button on the tool bar.
Unfortunately, it is forcing me provide the actual name of the Printer
on my network at the same time. For example my current code is:

CommandBars("Standard").Controls("Print (Marketing)").Enabled = False

This works great on my computer, but of course, not on anyone elses. I
need something that will disable it regardless of who is using it, and I
need it to work for both Excel 2000 and 2002 users. (Perhaps an idex
#??)

Can anyone help? I would appreciate it.

Thanks.

Dan

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Ranmara]

Only thing I could think of would be to do a loop and store the index in a
global variable when you open sheet.

That way you would know the Index and be able to enable/disable as needed.

It always will say "Print ( " so that would be the test in the loop.

if left(control.caption,5) = "Print" then
global = control.index
exit loop
end if

 

[Dan Gesshel]

I think I understand what you are getting at, but I'm having problems
with the syntax.

How do I go about looping throught the Standard toolbar to find the
correct index? Even if I could find the correct item, I probably can set
it to a variable and the do something like this:

Set myControl = CommandBars("Standard").Controls(???????) -- (or Index
here? if so.. what's the correct syntax to use)

With myControl
.DescriptionText = "Print Document"
.Caption = "Print"
End With

After that I think I could handle it. Thanks for the help in advance.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Dave Peterson]

Instead of stopping the icon from working (and stopping ctrl-P and File|Print
and file|printpreview|Print,....)

maybe you could just set a global variable in a General module:

Option Explicit
Public OkToPrint As Boolean

Then within the workbook module, look at that boolean variable:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If OkToPrint = False Then
Cancel = True
MsgBox "Sorry--can't print now"
End If
End Sub

And when you're gonna allow printing, toggle it to true.

Chip Pearson has lots of stuff about events at:
http://www.cpearson.com/excel/events.htm

 

[peterlee516]

Hey guys...This is a great site. I know it would be very helpful if I
understood what everyone was talking about.

You guys seem to know what you are doing. Would you guys help me with
disabling the Sheet option under the Format menu?

Or a way to disable a sheet not hide.

Basically I am trying to set it up so that users can put in a password
and only view what they are aloud to view and disable the rest. I don't
want to just hide the sheets just in case they know how to unhide
sheets, which I doubt.

Is there a disable function for sheets?

Thanks a lot.

 

[steve]

One of the recurring suggestions is to hide all but a 'Welcome' sheet
in the ThisWorkbook close event. using
Sheets("Sheet1").Visible = xlVeryHidden
You can do this with all but one of the sheets. With xlVeryHidden the
sheets can only be viewed through code, not through Format > Sheets
You can loop through the sheets to do this.

Than you can have code that will show selected sheets based on a
password. In your code you can use a Select Case structure to respond
to different passwords and unhide the relevent sheets.

Just remember to protect you code in the VB Editor to prevent users
getting in and 'messing'.

But keep in mind that Excel is not that secure. And someone could write
code in another workbook to unhide your sheets.

Post back if you need more help...