Copy Image from Clipboard into Excel in Certain Cells

ashani

Board Regular
Joined
Mar 14, 2020
Messages
202
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

I'm using the below formula and I want to paste image from clipboard in certain area - however when i do it, the image doesn't fit in that section properly and either shrinks so much or so little.

Also, if there is no image on the clipboard or text - then I want the message box pop up to say "nothing to copy".

Thank you for your help.

VBA Code:
Sub copy()
    Dim p As Picture
    Dim Target As Range
    
    Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:="abc"

    Set p = ActiveSheet.Pictures.Paste
    Set Target = Range("B3:I26")
       
    With Target
        p.ShapeRange.LockAspectRatio = msoFalse 'Allows Proper Sizing
        p.Top = .Top
        p.Left = .Left
        p.Width = .Width
        p.Height = .Height
    End With

ActiveSheet.Protect Password:="abc", DrawingObjects:=False, contents:=True, Scenarios:=True


End Sub
 

ashani

Board Regular
Joined
Mar 14, 2020
Messages
202
Office Version
  1. 365
Platform
  1. Windows
you're amazing @rlv01 and the best - thank you so much
just one question on this - is it possible to have date & time of the copy next to the image or underneath - once click macro button it paste the object and put date & time ?
Thank you once again.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
Code to do that is included:
VBA Code:
        With .Offset(.Rows.Count, 1).Resize(1, 1)     'modify to suit yourself
            .Value = CStr(VBA.Date & "   " & VBA.Time)
            .EntireColumn.AutoFit
        End With

but where it should be and how it should be formatted is so subjective that I think you need to experiment with it.
 

ashani

Board Regular
Joined
Mar 14, 2020
Messages
202
Office Version
  1. 365
Platform
  1. Windows
agree but for some reason it's not coming up. Here is the screenshot - I'd like a date and time of paste in Column A11 for example.

1590108356801.png
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
VBA Code:
    ActiveSheet.Range("A11").Value = CStr(VBA.Date & " " & VBA.Time)
 

ashani

Board Regular
Joined
Mar 14, 2020
Messages
202
Office Version
  1. 365
Platform
  1. Windows
You're the best - thank you so much for all your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,800
Messages
5,626,958
Members
416,211
Latest member
lanka123

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