Saving a pic

Edday

hey

i really need help with this, i have posted in tons of forums and the descussion groups, no replies! gar

lol, well i have this macro in excel 2003 and it has a userform and there are a few elements (3 pictures, 3 text boxes) that get modified during the macro then i need to kinda merge them and save it as one picture, can a macro do that

any help would be great thx

Andy Pope


Hi,

Maybe you could make use of the code in Stephen Bullen's PastePicture example
http://www.oaltd.co.uk/DLCount/DLCount.asp file=PastePicture.zip

Edday

hey

thx for the quick reply

it's a great program he's got there but there's no way i can mod that to do it to a frame, is there another way i can do it, i see how it works but the same commands don't work with a frame so i'm stuck

thx again

Andy Pope

Here is some code to save the userform as a image to the worksheet. You could then crop the image.

Posted by Orlando Magalhaes Filho and modified by Tom Ogilvy

Modification of code originally posted by
"Orlando Magalhaes Filho" <orlan...@geocities.com.br>

Modified to capture just the userform (not the whole window).

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

Public Const VK_SNAPSHOT = 44
Public Const VK_LMENU = 164
Public Const KEYEVENTF_KEYUP = 2
Public Const KEYEVENTF_EXTENDEDKEY = 1

Sub Test()
UserForm1.Show
End Sub

In the userform module:

Private Sub CommandButton1_Click()
' keybd_event VK_SNAPSHOT, 0, 0, 0
DoEvents
keybd_event VK_LMENU, 0, _
KEYEVENTF_EXTENDEDKEY, 0 ' key down
keybd_event VK_SNAPSHOT, 0, _
KEYEVENTF_EXTENDEDKEY, 0
keybd_event VK_SNAPSHOT, 0, _
KEYEVENTF_EXTENDEDKEY + KEYEVENTF_KEYUP, 0
keybd_event VK_LMENU, 0, _
KEYEVENTF_EXTENDEDKEY + KEYEVENTF_KEYUP, 0
DoEvents
Application.Wait Now + TimeValue("00:00:01")
ActiveSheet.PasteSpecial Format:="Bitmap", _
Link:=False, DisplayAsIcon:=False
End Sub

Edday

hey again

thx for code looks exactly what i want except when i put it in to test it, it just somes with the compile error:

"Constants, fixed-length strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules"

it says that on all the lines with "Public Const" at the start.

how could i fix it

but apart from that, Andy, you are a life saver!

Andy Pope

I think you have pasted ALL the code into the userform code module.
The first part goes in a standard code module.
The code after the line in the userform module:
goes in the userform code module.

Sorry I should have made that more clear.

Edday

WOW that thing i PERFECT, Andy you really are a lifesaver, just one last question

can i mod that code a little to only take a screenshot of the userform but not including the window box, so just the contents of the userform.

thx so much for the help

Andy Pope

Not by adjusting the code. What the code is doing is the same as if you were to use ALT+PRINTSCRN which is to restrict the screen grab to the active window. The active window in this case being your userform.

What you can do though is to record a macro of you cropping the image after it has been pasted to a worksheet.
The crop tool is located on the Picture toolbar.