Save pictures by VBA on Windows 10 lock screen

Question

i have a vba code that saves a range of cells in image format; however, if the PC has a locked screen, the images that are saved are all white:

Range("B6:M80").Select
Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
GifLargh = Selection.Width
GifAlt = Selection.Height
Sheets("Summary").Select
Dim ch As ChartObject
Set ch = Sheets("Summary").ChartObjects.Add(1, 1, GifLargh, GifAlt)
Sheets("Summary").ChartObjects(1).Activate
Worksheets("Summary").ChartObjects(1).Chart.Select
Worksheets("Summary").ChartObjects(1).Chart.Paste
Worksheets("Summary").ChartObjects(1) _
.Chart.Export Filename:="C:\Macro\Summary.gif", FilterName:="GIF"

the same code works perfectly if the screen is not locked: does anyone have a suggestion to solve the problem?

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). It might be that `Selection` is not what you expect it to be. Get rid of all `.Select` and `Selection.` statements, then [edit] your question and add your new code. — Pᴇʜ, Apr 22 '20 at 07:28
Also see here: [VBA CopyPicture fails on locked screen](https://stackoverflow.com/questions/57791292/vba-copypicture-fails-on-locked-screen-error-1004-cannot-empy-clipboard) • Did you get any error message? — Pᴇʜ, Apr 22 '20 at 07:32
Thank you @PEH, I try to answer in order: - I have read the guide but I am quite new on VBA and I have not understood how to modify avoiding to use select in this specific case. - What do you mean by "It might be that Selection is not what you expect it to be"? - No, I'm not getting any error messages, simply instead of saving the contents of the cells in the image, save a completely white image. thanks for your patience and support — Alessandro, Apr 22 '20 at 09:04

score 2 · Accepted Answer · answered Apr 22 '20 at 09:08

Don't use .Select or .Activate or Selection unless you really need to. This makes your code unreliable and extremley slow.

So instead of

Range("B6:M80").Select
Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

write it without

Range("B6:M80").CopyPicture Appearance:=xlScreen, Format:=xlBitmap

Make sure all your Range, Cells, Columns and Rows objects have a worksheet specified. Otherwise Excel cannot know for sure in which worksheet the range Range("B6:M80") is.
So specify a worksheet like below:
```
ThisWorkbook.Worksheets("MySheet").Range("B6:M80")
```
There is a difference between Sheets and Worksheets. While Worksheets only contains worksheets, Sheets also contains chart sheets or others. So don't use them mixed. If you mean worksheet then don't use Sheet for that.
Declare all your variables properly before using them. Make sure you use Option Explicit.

So if you clean it up it would look something like this

Dim wsSource As Worksheet
Set wsSource = ThisWorkbook.Worksheets("your source worksheet") '‹~~ adjust your sheet name

wsSource.Range("B6:M80").CopyPicture Appearance:=xlScreen, Format:=xlBitmap

Dim GifLargh As Double
GifLargh = wsSource.Range("B6:M80").Width

Dim GifAlt As Double
GifAlt = wsSource.Range("B6:M80").Height

Dim wsSum As Worksheet
Set wsSum = ThisWorkbook.Worksheets("Summary")

Dim ch As ChartObject
Set ch = wsSum .ChartObjects.Add(1, 1, GifLargh, GifAlt)

With wsSum.ChartObjects(1)
    .Activate      '‹~~ might not be necessary (check it)
    .Chart.Select  '‹~~ might not be necessary (check it)
    .Chart.Paste
    .Chart.Export Filename:="C:\Macro\Summary.gif", FilterName:="GIF"
End With

If this code produces white images too, then there might be the probability that you cannot screenshot with .CopyPicture if the screen is locked.

This code works perfectly (changing `.Chart.Select` with `.Select`) if the screen is not locked. If it is blocked, you have the same error (runtime 1004 executing `.CopyPicture`) indicated here: https://stackoverflow.com/questions/57791292/vba-copypicture-fails-on-locked-screen-error-1004-cannot-empy-clipboard. Thanks anyway for the support! — Alessandro, Apr 22 '20 at 10:05
@Alessandro yes, that is what I assumed to happen. So it looks like it is not possible because you cannot take a screenshot of a screen that is paused. — Pᴇʜ, Apr 22 '20 at 10:08

score 0 · Answer 2 · answered Apr 22 '20 at 12:56

0

I solved using a Keboard and Mouse blocker like this: https://sourceforge.net/projects/winkeylock/

answered Apr 22 '20 at 12:56

Alessandro

23
3

score 0 · Answer 3 · answered Dec 29 '21 at 16:39

0

I have the same issue running macros from Task Scheduler. I don't have a solution as yet.

However, in terms of preventing your screen from locking you can just issue a VBA.SendKeys "+" to send the shift key alone.

answered Dec 29 '21 at 16:39

JBAbbott2

1

2

This does not really answer the question. If you have a different question, you can ask it by clicking [Ask Question](https://stackoverflow.com/questions/ask). To get notified when this question gets new answers, you can [follow this question](https://meta.stackexchange.com/q/345661). Once you have enough [reputation](https://stackoverflow.com/help/whats-reputation), you can also [add a bounty](https://stackoverflow.com/help/privileges/set-bounties) to draw more attention to this question. - [From Review](/review/late-answers/30698847) – taylor.2317 Dec 29 '21 at 18:05

Save pictures by VBA on Windows 10 lock screen

3 Answers3