0

I have very weird problem.

I have a dashboard where there are two sheets one named: "Current DMB Template" and 2nd named "KPI". In main tab there is two columns green one D8:E27 and red one G8:H27:

enter image description here

In KPI there are 4 tables:

enter image description here

I have a macro that creates a chart for each table from KPI in either green or red column depending on if the KPI was achieved or not.

Sub DeleteKPICharts()

    On Error Resume Next

    Worksheets("Current DMB Template").ChartObjects.Delete
KPI
End Sub


Sub KPI()
Dim wb As Workbook
Dim main As Worksheet
Dim KPI As Worksheet

Dim embeddedchart As ChartObject
Set wb = ThisWorkbook
Set main = wb.Sheets("Current DMB Template")
Set KPI = wb.Sheets("KPI")

Dim TotalGraphsD As Byte 'will store how many graphs in D
Dim TotalGraphsG As Byte ' will store how many graphs in G
Dim i As Long, j As Long
Dim ThisColumn As String


For i = 1 To 11 Step 3
    ThisColumn = IIf(KPI.Range("E" & (i + 1)).Value = "No", "G", "D") 'which column will be the graph
    j = 9 + (7 * IIf(ThisColumn = "G", TotalGraphsG, TotalGraphsD))
    Set embeddedchart = main.ChartObjects.Add(Left:=Range(ThisColumn & j).Left, Width:=170, Top:=Range(ThisColumn & j).Top, Height:=100)
    With embeddedchart.Chart
        .SetSourceData Source:=KPI.Range("B" & i & ":C" & (i + 1))
        .ChartWizard , xlColumn, , xlColumns, , , True 'plot by cols
        .SeriesCollection(1).XValues = "=" & KPI.Range("A" & (i + 1)).Address(True, True, xlA1, True, True) 'title
    End With
    If ThisColumn = "G" Then TotalGraphsG = TotalGraphsG + 1 Else TotalGraphsD = TotalGraphsD + 1
Next i


End Sub

As you can see everything works beautifully, but only when I call macro from Developer>Macros tab.

I created a button, and assigned this macro to the button. Now, when I call the Macro via button this happens:

enter image description here

Macro is still working BUT it places the charts in original place where columns D and G should be (currently the sizes of some columns and rows have been changed as you can see). The macro works 100% correctly only when called from the tab or sometimes when called from VBA environment. Otherwise this is what happens.

Why is it happening? How can I overcome this?

Help please.

Many thanks for your help in advance.

BigBen
  • 46,229
  • 7
  • 24
  • 40
Kris_Toor
  • 91
  • 6
  • 2
    Probably need to specify the parent worksheet for `Range(ThisColumn & j)`. – BigBen Apr 21 '23 at 14:50
  • WOW! It works! I just needed to add `main.` and it works perfectly. Thank you so much! So I guess that the macro worked correctly when I was running it while I had my main worksheet active if I started it from another worksheet that would happen, and since the button is located in KPI worksheet it's always starting from another one. Am I correct? – Kris_Toor Apr 21 '23 at 15:09
  • 1
    An unqualified `Range` is implicitly `ActiveSheet.Range` in this case. – BigBen Apr 21 '23 at 15:10
  • Okeeeeeyyyyyyy, got it! Thank you @BigBen – Kris_Toor Apr 21 '23 at 15:11

0 Answers0