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!

 

[kitisn01]

Many thanks for the detailed response Jaafar, I will give this a go shortly..!

 

[kitisn01]

So I ran the code after updating the source data and received the following error message:

Run-time error '1004':

Method 'Range' of object '_Global' failed

Unsure that that is referring to?

However, when I place your code in a new worksheet it works absolutely fine...I'll keep digging until I get to the bottom of it! Thanks again for your help!

 

[kitisn01]

This works perfect Jafaar, I just need to figure out how to adjust the creation of the chart to multiple tabs within the user form i.e. different tabs will have different charts - is there any quick way to do this that you know of?

Many thanks in advance!

 

[Jaafar Tribak]

This works perfect Jafaar, I just need to figure out how to adjust the creation of the chart to multiple tabs within the user form i.e. different tabs will have different charts - is there any quick way to do this that you know of?

Many thanks in advance!

It is difficult to tell ... If you can upload a simplified version demo of your workbook to a file sharing website like DropBox.com or box.com , I can take a look.

 

[Jaafar Tribak]

Upon someone's request, I have added a combobox to the userform to enable the user to change the chart type from it.

Workbook demo

The userform contains a frame (Frame1) to display the charts and a ComboBox (ComboBox1) to choose the chart types.

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 Enums(70, 2) As Variant[COLOR=#008000] '<== Stores XlChartTypes[/COLOR]
Private Const Chart_Source_Range = "Sheet1!$B$1:$C$5"[COLOR=#008000] '<== Change addrs as required[/COLOR]

Private Sub UserForm_Initialize()
    Call FillComboBox(Combo:=Me.ComboBox1)
    Frame1.PictureSizeMode = fmPictureSizeModeStretch
    Frame1.Caption = ""
[COLOR=#008000]'    Frame1.SetFocus[/COLOR]
End Sub

Private Sub ComboBox1_Change()
    Dim lCharType As XlChartType
    
    If Me.ComboBox1.ListCount = UBound(Enums, 1) + 1 Then
        lCharType = IIf(Me.ComboBox1.ListIndex = -1, xlLine, Me.ComboBox1.Value)
        Call UpdateChart(SourceRange:=Range(Chart_Source_Range), ChartType:=lCharType)
    End If
End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    KeyCode = 0
End Sub

Private Sub ws_Change(ByVal Target As Range)
    Dim lCharType As XlChartType
    
    lCharType = IIf(Me.ComboBox1.ListIndex = -1, xlLine, Me.ComboBox1.Value)
    Call UpdateChart(SourceRange:=Range(Chart_Source_Range), ChartType:=lCharType)
End Sub

Private Sub UpdateChart(ByVal SourceRange As Range, ByVal ChartType As XlChartType)
    Dim oChart As ChartObject
    
    Set ws = SourceRange.Parent
    Set oChart = CreateChart(SourceRange, ChartType)
    Set Me.Frame1.Picture = CreatePicture(oChart)
    oChart.Delete
End Sub
    
Private Function CreateChart(ByVal SourceDataRange As Range, ByVal ChartType As XlChartType) As ChartObject
    SourceDataRange.Parent.Shapes.AddChart.Select
    ActiveChart.ChartType = ChartType
    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

Private Sub FillComboBox(ByVal Combo As ComboBox)
    Enums(0, 0) = -4098:     Enums(0, 1) = "xl3DArea"
    Enums(1, 0) = 78:        Enums(1, 1) = "xl3DAreaStacked"
    Enums(2, 0) = 79:        Enums(2, 1) = "xl3DAreaStacked100"
    Enums(3, 0) = 60:        Enums(3, 1) = "xl3DBarClustered"
    Enums(4, 0) = 61:        Enums(4, 1) = "xl3DBarStacked"
    Enums(5, 0) = 62:        Enums(5, 1) = "xl3DBarStacked100"
    Enums(6, 0) = -4100:     Enums(6, 1) = "xl3DColumn"
    Enums(7, 0) = 54:        Enums(7, 1) = "xl3DColumnClustered"
    Enums(8, 0) = 55:        Enums(8, 1) = "xl3DColumnStacked"
    Enums(9, 0) = 56:        Enums(9, 1) = "xl3DColumnStacked100"
    Enums(10, 0) = -4101:    Enums(10, 1) = "xl3DLine"
    Enums(11, 0) = -4102:    Enums(11, 1) = "xl3DPie"
    Enums(12, 0) = 70:       Enums(12, 1) = "xl3DPieExploded"
    Enums(13, 0) = 1:        Enums(13, 1) = "xlArea"
    Enums(14, 0) = 76:       Enums(14, 1) = "xlAreaStacked"
    Enums(15, 0) = 77:       Enums(15, 1) = "xlAreaStacked100"
    Enums(16, 0) = 57:       Enums(16, 1) = "xlBarClustered"
    Enums(17, 0) = 71:       Enums(17, 1) = "xlBarOfPie"
    Enums(18, 0) = 58:       Enums(18, 1) = "xlBarStacked"
    Enums(19, 0) = 59:       Enums(19, 1) = "xlBarStacked100"
    Enums(20, 0) = 15:       Enums(20, 1) = "xlBubble"
    Enums(21, 0) = 87:       Enums(21, 1) = "xlBubble3DEffect"
    Enums(22, 0) = 51:       Enums(22, 1) = "xlColumnClustered"
    Enums(23, 0) = 52:       Enums(23, 1) = "xlColumnStacked"
    Enums(24, 0) = 53:       Enums(24, 1) = "xlColumnStacked100"
    Enums(25, 0) = 102:      Enums(25, 1) = "xlConeBarClustered"
    Enums(26, 0) = 103:      Enums(26, 1) = "xlConeBarStacked"
    Enums(27, 0) = 104:      Enums(27, 1) = "xlConeBarStacked100"
    Enums(28, 0) = 105:      Enums(28, 1) = "xlConeCol"
    Enums(29, 0) = 99:       Enums(29, 1) = "xlConeColClustered"
    Enums(30, 0) = 100:      Enums(30, 1) = "xlConeColStacked"
    Enums(31, 0) = 101:      Enums(31, 1) = "xlConeColStacked100"
    Enums(32, 0) = 95:       Enums(32, 1) = "xlCylinderBarClustered"
    Enums(33, 0) = 96:       Enums(33, 1) = "xlCylinderBarStacked"
    Enums(34, 0) = 97:       Enums(34, 1) = "xlCylinderBarStacked100"
    Enums(35, 0) = 98:       Enums(35, 1) = "xlCylinderCol"
    Enums(36, 0) = 92:       Enums(36, 1) = "xlCylinderColClustered"
    Enums(37, 0) = 93:       Enums(37, 1) = "xlCylinderColStacked"
    Enums(38, 0) = 94:       Enums(38, 1) = "xlCylinderColStacked100"
    Enums(39, 0) = -4120:    Enums(39, 1) = "xlDoughnut"
    Enums(40, 0) = 80:       Enums(40, 1) = "xlDoughnutExploded"
    Enums(41, 0) = 4:        Enums(41, 1) = "xlLine"
    Enums(42, 0) = 65:       Enums(42, 1) = "xlLineMarkers"
    Enums(43, 0) = 66:       Enums(43, 1) = "xlLineMarkersStacked"
    Enums(44, 0) = 67:       Enums(44, 1) = "xlLineMarkersStacked100"
    Enums(45, 0) = 63:       Enums(45, 1) = "xlLineStacked"
    Enums(46, 0) = 64:       Enums(46, 1) = "xlLineStacked100"
    Enums(47, 0) = 5:        Enums(47, 1) = "xlPie"
    Enums(48, 0) = 69:       Enums(48, 1) = "xlPieExploded"
    Enums(49, 0) = 68:       Enums(49, 1) = "xlPieOfPie"
    Enums(50, 0) = 109:      Enums(50, 1) = "xlPyramidBarClustered"
    Enums(51, 0) = 110:      Enums(51, 1) = "xlPyramidBarStacked"
    Enums(52, 0) = 111:      Enums(52, 1) = "xlPyramidBarStacked100"
    Enums(53, 0) = 112:      Enums(53, 1) = "xlPyramidCol"
    Enums(54, 0) = 106:      Enums(54, 1) = "xlPyramidColClustered"
    Enums(55, 0) = 107:      Enums(55, 1) = "xlPyramidColStacked"
    Enums(56, 0) = 108:      Enums(56, 1) = "xlPyramidColStacked100"
    Enums(57, 0) = -4151:    Enums(57, 1) = "xlRadar"
    Enums(58, 0) = 82:       Enums(58, 1) = "xlRadarFilled"
    Enums(59, 0) = 81:       Enums(59, 1) = "xlRadarMarkers"
    Enums(60, 0) = 88:       Enums(60, 1) = "xlStockHLC"
    Enums(61, 0) = 89:       Enums(61, 1) = "xlStockOHLC"
    Enums(62, 0) = 90:       Enums(62, 1) = "xlStockVHLC"
    Enums(63, 0) = 91:       Enums(63, 1) = "xlStockVOHLC"
    Enums(64, 0) = 83:       Enums(64, 1) = "xlSurface"
    Enums(65, 0) = 85:       Enums(65, 1) = "xlSurfaceTopView"
    Enums(66, 0) = 86:       Enums(66, 1) = "xlSurfaceTopViewWireframe"
    Enums(67, 0) = 84:       Enums(67, 1) = "xlSurfaceWireframe"
    Enums(68, 0) = -4169:    Enums(68, 1) = "xlXYScatter"
    Enums(69, 0) = 74:       Enums(69, 1) = "xlXYScatterLines"
    Enums(70, 0) = 75:       Enums(70, 1) = "xlXYScatterLinesNoMarkers"
    
    With Combo
        .ColumnCount = 2
        .BoundColumn = 1
        .List = Enums
        .ColumnWidths = "0pt;100pt"
        .ListIndex = 41
    End With
End Sub

 

[Ingolf]

Upon someone's request, I have added a combobox to the userform to enable the user to change the chart type from it.

Workbook demo

The userform contains a frame (Frame1) to display the charts and a ComboBox (ComboBox1) to choose the chart types.

Wow...EXCELLENT

Thank you very much.

 

[kitisn01]

Thanks for that Jafaar - that is awesome!

I may have caused some confusion with my initial request, what I am trying to do is to have different charts (chart 1, Chart 2..chart n) display on on a multi-page userform e.g. you click on page 3 of the userform and there is a chart specific to something e.g. bridge geometry (which is a function of the span length etc) and then when you click on e.g. page 4, I have a different chart which shows some different aspects of the bridge i.e.its data source is a different range within the worksheet - do you think that is something that can be done?

THANK YOU VERY MUCH FOR YOUR HELP THUS FAR!

 

[Jaafar Tribak]

Thanks for that Jafaar - that is awesome!

I may have caused some confusion with my initial request, what I am trying to do is to have different charts (chart 1, Chart 2..chart n) display on on a multi-page userform e.g. you click on page 3 of the userform and there is a chart specific to something e.g. bridge geometry (which is a function of the span length etc) and then when you click on e.g. page 4, I have a different chart which shows some different aspects of the bridge i.e.its data source is a different range within the worksheet - do you think that is something that can be done?

THANK YOU VERY MUCH FOR YOUR HELP THUS FAR!

Hi Kitisn01,

Would it be ok to have a combobox on the userform that enables the user to choose different charts instead of having a tab for each chart ?

I think this is better andmore flexible plus it makes the userform less cluttered and easier to code .

 

[kitisn01]

Hi Jaafar,

Ideally we need different charts on different pages within the userform as each of the charts are equally as important as each other (alternatively, if there is no easy way to do this then a combo box may be the only viable solution..!)

Additionally Jaafar, do you know if there is a simple way in which I can specify the xvalues as well as the series y values for the chart? and also add multiple series of y values for the same x values (in a similar way that in OWC we would set the x values as the chart category and then we could have as many different series that we wanted?)

Many thanks for your help!

 

[Jaafar Tribak]

Hi Jaafar,

Ideally we need different charts on different pages within the userform as each of the charts are equally as important as each other (alternatively, if there is no easy way to do this then a combo box may be the only viable solution..!)

Additionally Jaafar, do you know if there is a simple way in which I can specify the xvalues as well as the series y values for the chart? and also add multiple series of y values for the same x values (in a similar way that in OWC we would set the x values as the chart category and then we could have as many different series that we wanted?)

Many thanks for your help!

Hi Kitisn01,

I already have an unfinished project from a few years back but the userform doesn't use multi-tabs ie= The chart is displayed on a frame control on the userform.

However, the userform enables the user to dynamically select and change the chart source data range and the series with the mouse and also enables the user to change the chart type.

It is not perfect and it can't add multiple series of y values for the same x values but it is the closest I could arrived at.

I'll post the code together with a demo workbook when I am done.