Copy Cell and Paste as Picture Link Causes Slow Down of Macro Execution

bisel

Board Regular
Joined
Jan 4, 2010
Messages
204
Office Version
  1. 365
Platform
  1. Windows
Hello All,

My workbook is fairly large and there is a lot of VBA in it. I am experiencing a problem that has been reported before, however the solution in prior posts is not working.

My problem:

When I copy a cell and paste it as a linked picture, the VBA slows ... especially when that VBA is checking individual cells for values, etc. Here is the link to prior post from a few years back ...

I am experience the same symptoms that were mentioned in the above post:
  • If I copy any cell on any worksheet and then paste it as a picture link anywhere in workbook, execution of VBA slows. Even for private subs on unrelated worksheets.
  • If I remove the picture that is linked to another cell, the problem goes away.

There is a bit of code which back in 2003 resolved the issue for the OP in that post, however, that code no longer works. If you review the post the code that was reported back in 2003 or so ran a routine which first un-linked picture objects from their source and then re-linked them after the code finished running which was previously running slowly.

Anyone have an up to date solution. I am running Win11 and Office 365 (or whatever Microsoft is calling it these days).

Thanks,

Steve
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Mr Statto

New Member
Joined
Jan 24, 2013
Messages
3
Hi Steve, this is a problem I had a few years ago which reared its head again today, at which point I stumbled across your question. If you've not been able to resolve it already then can I suggest the following:
  1. Name one cell "UPDATE" and use data validation to limit it to TRUE or FALSE
  2. Name the ranges that contain the data you want in the pictures, but rather than just referring to the range use the formula "=if(UPDATE,
    #VALUE!
#VALUE!
 

Attachments

  • Screenshot 2022-01-21 175656.jpg
    Screenshot 2022-01-21 175656.jpg
    56.5 KB · Views: 17

Mr Statto

New Member
Joined
Jan 24, 2013
Messages
3
Hi Steve, this is a problem I had a few years ago which reared its head again today, at which point I stumbled across your question. If you've not been able to resolve it already then can I suggest the following:
  1. Name one cell "UPDATE" and use data validation to limit it to TRUE or FALSE
  2. Name the ranges that contain the data you want in the pictures, but rather than just referring to the range use the formula "=if(UPDATE,
    #VALUE!
#VALUE!

Sorry, it looks like something in my answer has caused all sorts of issues! I'll try again

  1. Name one cell "UPDATE" and use data validation to limit it to TRUE or FALSE
  2. Name the ranges that contain the data you want in the pictures, but rather than just referring to the range use the formula "=if(UPDATE, {range} )" (eg =IF(UPDATE,Slide!$J$62) (see attached screenshot for examples)
  3. Copy the range as a link, or use the Camera tool
  4. Change the reference of the linked picture to the range name (eg change "=$G$36" to "=NORDIC_ON"
  5. Amend your VBA code to set the UPDATE cell to FALSE before running, and then back to TRUE once complete.
This makes a huge difference to the running of the code
 

Forum statistics

Threads
1,181,770
Messages
5,931,933
Members
436,809
Latest member
dtechhh

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
Top