CODEWITHSUNDEEP
imagevbacharts
gde211
gde211

Reputation: 15

Paste range picture to chart area for export not working

I'm having a hard time getting the picture to paste to my chart. I've tried various combinations of "With TempChrt" to get the .paste to work to no avail. If perhaps I am going around my elbow to solve the problem I am open to suggestions.

Essentially I would like to transfer cells with: specific size, background color, and values to a userform for viewing purposes only. Also sorry but i couldnt get the bold or italicized fonts to work on the bit of code with the problem but the line that hangs up is ".Paste" following "With TempChrt". Method 'Paste' of object '_chart' failed.

Private Sub UserForm_Initialize()
  Dim i As Byte
  Dim rng1 As Range, rng2 As Range
  Dim wkshtDC As Worksheet
  Dim TempChrt As Chart
  Dim TempChrtNm As String

  Set wkshtDC = Worksheets("Data & Calcs")
  wkshtDC.Unprotect "123"
  Set TempChrt = wkshtDC.Shapes.AddChart.Chart
  TempChrtNm = TempChrt.Parent.Name
  With TempChrt.Parent
    .Width = 87.12
    .Height = 285.12
    .Top = wkshtDC.Range("BC28").Top
    .Left = wkshtDC.Range("BC28").Left
  End With
  For i = 1 To 4
    Set rng1 = wkshtDC.Range("BE28").Offset(0, i)
    Set rng2 = wkshtDC.Range("BE46").Offset(0, i)
    If i <> 1 Then
        TempChrt.Shapes.Range(Array("chart")).Delete
    End If
    wkshtDC.Range(rng1.Address, rng2.Address).CopyPicture xlScreen, xlPicture
    With TempChrt
        .Paste
        .Export Filename:="G:\MVOsafe\Ramp-up\misc\TempMatrix" & i & ".bmp"
    End With
    Me.Controls("Frame" & i).Picture = LoadPicture("G:\MVOsafe\Ramp-up\misc\TempMatrix" & i & ".bmp")
  Next i
  TempChrt.Parent.Delete
  wkshtDC.Protect "123"

End Sub

Upvotes: 0

Views: 37

Answers (1)

gde211
gde211

Reputation: 15

Got it...

With TempChrt

changed to

wkshtDC.Range(rng1.Address, rng2.Address).CopyPicture xlScreen, xlPicture
    wkshtDC.ChartObjects(TempChrtNm).Activate
    With ActiveChart
        .Paste
        .Export Filename:="G:\MVOsafe\Ramp-up\misc\TempMatrix" & i & ".bmp"
    End With

where

TempChrtNm = TempChrt.Parent.Name

Upvotes: 0

Related Questions