Without use SendKeys

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

Sub PrintScreen()
    keybd_event VK_SNAPSHOT, 1, 0, 0
    ActiveSheet.Paste
End Sub

      

        
        
        
      

    

However, using this approach, if you are using multiple monitors, it will only capture the active monitor, so extra effort needs to be made if you need to capture another monitor (maybe this can be done with API calls, but I don't know got this far).

Note. The operator AppActivate

can be used to activate another (non-Excel) application, and if you do, the function keybd_event

will only capture that application, for example:

AppActivate "Windows Command Processor" 'Modify as needed
keybd_event VK_SNAPSHOT, 1, 0, 0
ActiveSheet.Paste

      

        
        
        
      

    

Usage SendKeys

, problem solved:

While it SendKeys

is notorious, if you need to use this method due to the limitations of the API method described above, you may have some problems. As we noticed, the call ActiveSheet.Paste

did not actually insert the print screen, but rather pasted what was previously in the clipboard queue, in the sense that you had to click a button to invoke the macro twice before it actually inserted the screenshot.

I tried several different things to no avail, but didn't notice the obvious: while debugging, if I put a breakpoint on ActiveSheet.Paste

, I no longer see the problem described above!

This tells me that it SendKeys

is not processing fast enough to put the data on the clipboard before executing the next line of code, there are two possible solutions to solve this problem.

• You can try Application.Wait
  
  . This method seems to work when I test it, but I would warn you that it is also unreliable.
• The best option would be DoEvents
  
  because it is clearly designed to work with things like this:

DoEvents transfers control to the operating system. Control returns after the operating system has finished processing events in its queue and all keys in the SendKeys queue have been sent.

This works for me whether I run the macro manually from the IDE, from the Macros ribbon, or using Click

the event procedure button :

Option Explicit
Sub CopyScreen()

Application.SendKeys "({1068})", True
DoEvents
ActiveSheet.Paste

Dim shp As Shape
With ActiveSheet
    Set shp = .Shapes(.Shapes.Count)
End With

End Sub

      

        
        
        
      

    

How to install, resize and crop an image:

Regardless of which method you use, once the image has been inserted with ActiveSheet.Paste

, it will be a shape that you can manipulate with.

To resize: when you have a form descriptor, just assign properties to it Height

and Width

as needed:

Dim shp As Shape
With ActiveSheet
    Set shp = .Shapes(.Shapes.Count)
End With
shp.Height = 600
shp.Width = 800

      

        
        
        
      

    

To post this: use the form TopLeftCell

property .

To cut it: use shp.PictureFormat.Crop

(and / or CropLeft

, CropTop

, CropBottom

, CropRight

, if you need to fine-tune what part of the screenshot is needed, for example, it puts a screenshot in the screenshot at 800x600.:

Dim h As Single, w As Single
h = -(600 - shp.Height)
w = -(800 - shp.Width)

shp.LockAspectRatio = False
shp.PictureFormat.CropRight = w
shp.PictureFormat.CropBottom = h