Excel 97 VBA Print Preview Options

[TheFIDDLER]

Anyone know a method to disable the Print Preview options using VBA for Excel 97?

I mean disabling the Margin, Setup and Page Break buttons that are found at the top of the print preview screen.

Thanks

[RobDog888]

This will disable the menu item for print preview on Excel 2003.
it should be the same on Excel 97.

Private Sub Workbook_Open()
    
    Dim oCtl As Office.CommandBarControl
    Dim oCtl2 As Office.CommandBarPopup
    
    Set oCtl2 = Application.CommandBars("Worksheet Menu Bar").Controls("&File")
    Set oCtl = oCtl2.CommandBar.FindControl(msoControlButton, Application.CommandBars("Standard").Controls("Print Pre&view").ID)
    If Not oCtl Is Nothing Then
        oCtl.Enabled = False
    End If
    Set oCtl = Nothing
 
End Sub

HTH

[TheFIDDLER]

Thanks, but not my question.

I am not looking to remove the print preview icon from the menu bar. I am trying to disable or remove the options available within print preview itself.

I can do this with Excel 02 and XP, but am looking for a method that works with the worksheet.printpreview command in Excel 97 via VBA.

[RobDog888]

Sorry for the mixup, buti no longer have any systems with Office
97 on them. Can you post your code for XP? Maybe I can think of
a way. Is the worksheet.printpreview method not available in 97?

[TheFIDDLER]

Starting with 02 (I think), Excel can do what I am looking for with the worksheet.printpreview event, and using the parameter of EnableChanges = false. Setting the EnableChanges parameter to false disabled the Margins and Setup button, hence preventing the user from modifying my page setup.

It just doesn't work in 97 and I was wondering if someone came up with another method to accomplish this.

[RobDog888]

Maybe as a temporary solution for 97 you could just disable the
print preview and only let them actually print the spreadsheet?

Or if the version is 97 you could hide the standard print preview
menu item and toolbar button and then create your own it their
place. This may give you more flexability in controlling the buttons
on the print preview form. you can "FindWindow and
FindWindowEx APIs to get the handel to the buttons and change
the style to disabled or hide them using SetWindowLong.

The only issue to get around would be the modal state of the
print preview window. Your findwindow code will not execute until
the print preview window is closed. To get around this you could
add the FindWindow code in a separate .exe and kick it off right
before you show the print preview window. Then it will be able to
interact with the print preview window without the limitation of
the Modality issue. Then close itself down after the changes are
made to the print preview window. Kind of like using two
programs to simulate multi-threading.

Hope I explained myself well.

[TheFIDDLER]

Sounds a bit more work than I was intending for.

But where I do need this for my software, I will file this under my to do list.

As best as I can imagine, Microsoft added the extra functionality as this did not exist for 97. Really bad stuff when a user changes my margins and my page setup and then calls me to say my application does not print correctly. I'm just trying to be proactive and remove what they shouldn't be playing with.


Thanks

[RobDog888]

No problem. I have the same issue with our client
management / employee time program. Our employees print out
service calls or their time card and is shells out to wordpad on our
terminal server and it is formatted to print on 1/2" margins, but
wordpad's default is 1 1/4". Changing the forms are not an
option so I used the method above until I found the registry key
for the print margins in hex. so now I just make sure that the
margins in the registry are the appropriate values and if not I
update them and after I set it back and they have no idea.