Saving userform in Excel as an image

September 1, 2008 at 16:09 · Filed under Userforms, VBA Advanced, VBA Examples

-Selva V Pasupathy, HSBC Global Resourcing, Hyderabad 1
Original source: Colo’s Excel Junk Room – Masaru Kaji

Last week I was working on an excel application where I wanted several images of the userform at various stages so that I can prepare SOP for documentation. It was difficult for me because on my system Print Screen button was not working and I did not know how to activate that. While browsing hte net and going through colo;s junk room, I came across the code below which saves a userform as an image when you double click on the userform. With API, this code pastes an image of the form into a worksheet of the new workbook, then save it as a HTML file. When the Excel workbook is saved as a html file, all image files will be placed in the different folder.

HOW SHOULD I USE THE FOLLOWING CODE

Place the following code in a userform module. 

Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, _
                                              ByVal bScan As Byte, _
                                              ByVal dwFlags As Long, _
                                              ByVal dwExtraInfo As Long)
Private Const VK_LMENU = &HA4
Private Const VK_SNAPSHOT = &H2C
Private Const VK_CONTROL = &H11
Private Const VK_V = &H56
Private Const VK_0x79 = &H79
Private Const KEYEVENTF_EXTENDEDKEY = &H1
Private Const KEYEVENTF_KEYUP = &H2
'__________________________________________________
Private Sub UserForm_DblClick _
                    (ByVal Cancel As MSForms.ReturnBoolean)
    Dim sAppOs As String
    Dim wks As Worksheet
    'get oparating system
    sAppOs = Application.OperatingSystem

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    If Mid(sAppOs, 18, 2) = "NT" Then
    ' WinNT,Windows2000,WindowsXP - Using Win32API
Call keybd_event(VK_LMENU, VK_V, _
                          KEYEVENTF_EXTENDEDKEY, 0)
Call keybd_event(VK_SNAPSHOT, VK_0x79, _
                          KEYEVENTF_EXTENDEDKEY, 0)
Call keybd_event(VK_LMENU, VK_V, _
                          KEYEVENTF_EXTENDEDKEY Or _
                          KEYEVENTF_KEYUP, 0)
Call keybd_event(VK_SNAPSHOT, VK_0x79, _
                          KEYEVENTF_EXTENDEDKEY _
                          Or KEYEVENTF_KEYUP, 0)
    Else
    ' Windows95,Windows98,WindowsME
Call keybd_event(VK_SNAPSHOT, 0, _
                   KEYEVENTF_EXTENDEDKEY, 0)
Call keybd_event(VK_SNAPSHOT, 0, _
                   KEYEVENTF_EXTENDEDKEY Or KEYEVENTF_KEYUP, 0)
    End If
    DoEvents
    Unload Me
    Set wks = Workbooks.Add.Sheets(1)
    Application.Goto wks.Range("A1")
    ActiveSheet.Paste
    wks.SaveAs Filename:="C:\myfile.htm", FileFormat:=xlHtml
    wks.Parent.Close False

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "Have a look at C:\myfile.files folder."
End Sub
'__________________________________________________

To download example file CLICK HERE

Leave a Comment

You must be logged in to post a comment.