troubleshooting Question

vba to retrieve range of cells that were put on the clipboard with ctrl C

Avatar of rberke
rberkeFlag for United States of America asked on
Microsoft Excel
20 Comments4 Solutions2929 ViewsLast Modified:
I assure you this is a difficult question, so read it carefully before attempting to help.

Please, don't point me to any "rookie programmer links" or "sample clipboard code links".  I am already fairly expert at VBA and have spent hours looking at links.  I found a few that addressed this exact problem but those experts concluded that it can't be done within the Excel Object Module.  

The user selects several cells and does a Ctrl C, then selects another cell anywhere they want and then call my macro, PasteSpecialSum which inserts formula '=sum([book.xls]sheet1!a1:a5,...)' into the target cell.

PasteSpecialSum retrieves the users range off the clipboard which I discovered was very difficult.  I developed the attached ReselectClip code to solve the problem. But it is complicated, so the goal of this questions is to come up with a better way.

I do NOT want the user to call my macro first, so please do not suggest code like
    set DesiredRange = inputbox( "Please select desired cells" ....type:=8)

I also do NOT want my user to select the destination first. The ctrl C must be done first.

Also, I can't use events because the user might switch to a cell in another workbook, then call PasteSpecialSum. And they might select various cells before deciding where to paste it.  This means I cannot easily use events like Selection_change.

NOTE: Code posted below is not the correct code for this issue. Please see this comment for the correct code: https://www.experts-exchange.com/Q_27251773.html#a36356176
ASKER CERTIFIED SOLUTION
rberke
Consultant

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 4 Answers and 20 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 20 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros