Please confirm the following bug in Excel

[DennisE]

I'd like you MVP types (or anyone else for that matter) to confirm the
existence
a bug in Excel, in that the caption associated with any frame(s) on a userform
will
not print out under the printform command. To see what I'm talking about,
create
a user form containing a frame with caption, say, "Frame1" and place a command
button on that user form containing the single code line "UserForm1.printform".
Run a macro that says "UserForm1.Show" and then click the command button. Any
and all non-frame controls you've placed on the user form will contain their
captions when printed, but any and all frames contained on the userform seem
to print out as closed rectangles without their corresponding captions. If you
confirm this (and it seems to be the case for both Excel 2002 and 2003), can
you think of a good work-around?

-- Dennis Eisen

 

[Rob van Gelder]

I'm using Excel 2000 - Same thing occurs for me.

 

[Stephen Bullen]

Hi Dennis,

If you
confirm this (and it seems to be the case for both Excel 2002 and 2003), can
you think of a good work-around?

Yes it looks like a bug to me. One workaround might be to have another frame
formatted to have no borders that contains a simple label control, then
position the frame so it appears to be the other frame's caption. Messy, but
it should work.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

 

[Tom Ogilvy]

This might be a workaround that doesn't require you to change your userform:

Following code was posted previously by Orlando Magalhaes Filho:

In a general module put in these declarations/code:

Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, _
ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

Public Const VK_SNAPSHOT = &H2C

Sub Test()
UserForm1.Show
End Sub


In the code module of the Userform:

Private Sub CommandButton1_Click()
keybd_event VK_SNAPSHOT, 0, 0, 0
Workbooks.Add
Application.Wait Now + TimeValue("00:00:01")
ActiveSheet.PasteSpecial Format:="Bitmap", Link:=False, _
DisplayAsIcon:=False
ActiveSheet.Range("A1").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWorkbook.Close False
End Sub


This takes a "picture" of the userform and pastes it on a sheet. It then
prints the picture on the sheet. I would hope the picture picks up the
caption for the frame.


Regards,
Tom Ogilvy