Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> Microsoft ISV Community Center Forums >> Visual Basic for Applications (VBA)

Saving a pic

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

[441 byte] By [Edday] at [2008-1-7]
«« Timer not firing in a COM Class
»» Crystal Report''s CrystalReportViewer doesn''t work on SharePoint site
# 1
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

AndyPope at 2007-10-2 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 2
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

Edday at 2007-10-2 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 3
Here is some code to save the userform as a image to the worksheet. You could then crop the image.

Posted by Orlando Magalh?es Filho and modified by Tom Ogilvy

Modification of code originally posted by
"Orlando Magalh?es 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
AndyPope at 2007-10-2 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 4
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!

Edday at 2007-10-2 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 5
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.

AndyPope at 2007-10-2 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 6

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

Edday at 2007-10-2 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 7
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.

AndyPope at 2007-10-2 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
Microsoft ISV Community Center Forums
Visual Basic for Applications (VBA)

Microsoft ISV Community Center Forums Hot Topic

Microsoft ISV Community Center Forums New Topic

Microsoft ISV Community Center Forums

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified