Creating a chart (using worksheet data) in a userform in VBA in Excel 2013

[kitisn01]

Hi All,

I have been trying to use OWC 11 components in Excel 2013 on a 64-bit operating system to create a chart within a userform (combo box) which has the source linked to my worksheet data with absolutely no luck.

It appears that Microsoft no longer supports OWC 11 components..does anyone know of a work around for this or if this is even possible in Excel 2013? i.e. am I just completely wasting my time?

There is a previous post relating to the matter that appears to have worked in Excel 2003 - I am literally just trying to emulate the same thing i.e. by using the method approach rather than exporting a picture of the chart and linking it to the image within the userform which other users have suggested.

https://www.ozgrid.com/forum/forum/t...-web-component

Many thanks for taking the time to read this!

 

[Dryver14]

forgive me if this is not the same thing, if you have checked the microsoft forms 2.0 object library in the references one of the additional tools is an OWS Post data.

Is that the same thing?

 

[kitisn01]

forgive me if this is not the same thing, if you have checked the microsoft forms 2.0 object library in the references one of the additional tools is an OWS Post data.

Is that the same thing?

Thanks for the input Dryver14, I've got Microsoft forms 2.0 Object library checked as one of the references but still nothing is working, I'm thinking it is just a compatibility issue between the versions?

 

[Dryver14]

No worries, I don't even know why I mentioned OWS when you were looking for OWC.

I personally am getting fed up with microsoft making things redundant in later versions. I still want a userform datepicker which is no more

 

[kitisn01]

Not a problem,

Yeah it's really annoying that they don't have any upgrades for previous tools that were useful...!

For me, I can create the chart in VBA and it plots etc, I just can't set the source of the chart data to the data within my worksheet - super frustrating..!

 

[Jaafar Tribak]

If the chart on the userform is only for displaying purposes then you could use the chart Export Property to save the chart as an image to disk then use the vba LoadPicture function to load the exported image file onto the userform Picture object.

A cleaner alternative that dosn't require saving an image file to disk is to copy the chart to the clipboard and then extract the picture object from there... This method is slightly more involded and uses the windows API .. Search for examples that use the OleCreatePictureIndirect ​API.

 

[kitisn01]

Thanks for the prompt response Jaafar, unfortunately, I need the chart to be dynamic so that it changes with the user input into the user form.

At the moment, I can create any chart in the userform by manually inputing the data and it all works fine. I just can't seem to get the VBA code to work which sets the chart source data as the data within my worksheet. (I have copied the code from the example in my initial post) Could there be any references that I am missing?

 

[Jaafar Tribak]

Thanks for the prompt response Jaafar, unfortunately, I need the chart to be dynamic so that it changes with the user input into the user form.

At the moment, I can create any chart in the userform by manually inputing the data and it all works fine. I just can't seem to get the VBA code to work which sets the chart source data as the data within my worksheet. (I have copied the code from the example in my initial post) Could there be any references that I am missing?

I am not sure I understand .

How are you creating the chart in the userform without the OWC control ? and is the user input on the worksheet (ie: chart source data cells) or on the userform?

Do you just want the user to be able to edit the chart source data on the worksheet and dynamically reflect the changes on the chart on the userform ?

 

[kitisn01]

Apologies for the confusion Jaafar, I have access to the userform OWC control i.e. I can use the command and options pop up to manually input the data for the chart and the chart will plot fine.

What I am trying to do is to be able to link the data which the chart is referencing to the data in my worksheet (rather than having to manually copy and paste the data into the data input).
The data in my worksheet is affected by the inputs of the userform. e.g. if I want to change the span length of a bridge from 20m to 50m I want the chart to auto update etc

 

[Jaafar Tribak]

I have no experience using the WOC chart control so I am not sure how it works.

here is a code example that should add a dynamic chart image to the userform.

The code assumes the chart source data is Sheet1!$A$1:$B$5 ... Change the worksheet and range as required.

Now assuming the userform is modless, you can edit the chart source data cell on the worksheet while the userform is on display ... Once you change the data on the worksheet, the chart image will change automatically as well.

Code in the userform module :

Option Explicit

Private WithEvents ws As Worksheet

Private Type uPicDesc
    Size As Long
    Type As Long
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
        hPic As LongPtr
        hPal As LongPtr
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
       hPic As Long
       hPal As Long
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If
End Type

Private Type GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(0 To 7) As Byte
End Type

[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function OleCreatePictureIndirect64 Lib "oleAut32.dll" Alias "OleCreatePictureIndirect" (PicDesc As uPicDesc, RefIID As GUID, ByVal fPictureOwnsHandle As Long, iPic As IPicture) As Long
    Private Declare PtrSafe Function OleCreatePictureIndirect32 Lib "olepro32.dll" Alias "OleCreatePictureIndirect" (PicDesc As uPicDesc, RefIID As GUID, ByVal fPictureOwnsHandle As Long, iPic As IPicture) As Long
    Private Declare PtrSafe Function CopyImage Lib "user32" (ByVal handle As LongPtr, ByVal un1 As Long, ByVal n1 As Long, ByVal n2 As Long, ByVal un2 As Long) As LongPtr
    Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
    Private Declare PtrSafe Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As LongPtr
    
    Private hCopy As LongPtr, hPtr As LongPtr
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
    Private Declare Function OleCreatePictureIndirect64 Lib "oleAut32.dll" Alias "OleCreatePictureIndirect" (PicDesc As uPicDesc, RefIID As GUID, ByVal fPictureOwnsHandle As Long, iPic As IPicture) As Long
    Private Declare Function OleCreatePictureIndirect32 Lib "olepro32.dll" Alias "OleCreatePictureIndirect" (PicDesc As uPicDesc, RefIID As GUID, ByVal fPictureOwnsHandle As Long, iPic As IPicture) As Long
    Private Declare Function CopyImage Lib "user32" (ByVal handle As Long, ByVal un1 As Long, ByVal n1 As Long, ByVal n2 As Long, ByVal un2 As Long) As Long
    Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Function CloseClipboard Lib "user32" () As Long
    Private Declare Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As Long
    
    Private hCopy As Long, hPtr As Long
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If

Private Const IMAGE_BITMAP = 0
Private Const PICTYPE_BITMAP = 1
Private Const LR_COPYRETURNORG = &H4
Private Const CF_BITMAP = 2
Private Const S_OK = 0

Private Sub UserForm_Initialize()
    Call UpdateChart
End Sub

Private Sub ws_Change(ByVal Target As Range)
    Call UpdateChart
End Sub

Private Sub UpdateChart()
    Dim oChart As ChartObject
    
    Set ws = Sheet1
    Set oChart = CreateChart(Range("Sheet1!$A$1:$B$5"))[COLOR=#008000] '<== change range as needed.[/COLOR]
    Set Me.Picture = CreatePicture(oChart)
    oChart.Delete
End Sub
    
Private Function CreateChart(ByVal SourceDataRange As Range) As ChartObject
    SourceDataRange.Parent.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=SourceDataRange
    Set CreateChart = ActiveChart.Parent
End Function
    
Private Function CreatePicture(ByVal Chart As ChartObject) As IPicture
    
    Dim lRet As Long
    Dim IID_IDispatch As GUID
    Dim uPicinfo As uPicDesc
    Dim iPic As IPicture

    Chart.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
    OpenClipboard 0
    hPtr = GetClipboardData(CF_BITMAP)
    hCopy = CopyImage(hPtr, IMAGE_BITMAP, 0, 0, LR_COPYRETURNORG)
    CloseClipboard
    
    With IID_IDispatch
        .Data1 = &H20400
        .Data4(0) = &HC0
        .Data4(7) = &H46
    End With
    With uPicinfo
        .Size = Len(uPicinfo)
        .Type = PICTYPE_BITMAP
        .hPic = hCopy
        .hPal = 0
    End With

    If InStr(1, Application.OperatingSystem, "32-bit") Then
        lRet = OleCreatePictureIndirect32(uPicinfo, IID_IDispatch, True, iPic)
    End If
    
    If InStr(1, Application.OperatingSystem, "64-bit") Then
        lRet = OleCreatePictureIndirect64(uPicinfo, IID_IDispatch, True, iPic)
    End If
    
    If lRet = S_OK Then
        Set CreatePicture = iPic
    End If
End Function

No external OWC controls needed... I am not sure this is exactly what you are looking for but hope it can at least point you in the right direction.