save copy of userform after update

tombmatt

New Member
Joined
Aug 22, 2006
Messages
9
Have a userform that user selects from dropdowns and fill in textboxes. the form is then printed for submission to the appropriate office and the data is filed in the spreadsheet. I need to make a copy of that userform once the form is filled in either before or after print, but I can't figure out how to copy it (the userform image) to file.
thanks Tom
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
You could do this with a bunch of code. Search MSDN for code example of "Print Screen Active Form". Another way is to use this bit of code:

Code:
Option Explicit
 
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_SNAPSHOT = &H2C
 
Public Function SavedPictureOfUserform(FileName As String) As Boolean
    Dim Pic As stdole.IPictureDisp
 
    On Error GoTo Err_SavedPictureOfUserform
 
    keybd_event VK_SNAPSHOT, 1, 0, 0
    DoEvents
 
    If Clipboard.GetFormat(vbCFBitmap) Then
        Set Pic = Clipboard.GetData(vbCFBitmap)
        SavePicture Pic, FileName
        SavedPictureOfUserform = True
    End If
 
Err_SavedPictureOfUserform:
End Function

This, however, will not work in VBA. Is VB. So I compiled the above code into a dll. Download the following and extract both to the same path. The zip file contains an example workbook and the dll file.

TomsDll.zip

Repace "C:\MyPic.bmp" with the fullname of the file you will saving to disk...

The workbook contains this code in userform1:

Code:
Private Sub CommandButton1_Click()
    Dim MyDll As Object
    
    Set MyDll = CreateObject("TomsDLL.GetUserformPic")
    If MyDll.SavedPictureOfUserform("C:\MyPic.bmp") Then
        'succeeded
        MsgBox "OK..."
    Else
        'failed
    End If
    Set MyDll = Nothing
    
    Call Shell("REGSVR32 /u /s " & Chr(34) & ThisWorkbook.Path & "\TomsDLL.dll")
    
End Sub
Private Sub UserForm_Initialize()
    Call Shell("REGSVR32 /s " & Chr(34) & ThisWorkbook.Path & "\TomsDLL.dll")
End Sub
 
Last edited by a moderator:
Upvote 0
Tom No 1

Why do you want to do this in the first place?

Userforms aren't really designed for this sort of thing.:)
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top