Can't refer to graph when using if statements?

[JKB]

Hi everybody.
I have posted this problem earlier without luck, but now i have some additional info, which will hopefully help solving the problem...


My problem is the following:
The function "Adjust_graphs" works just fine without the if statement. I.e. if i remove everything which is purple and call Adjust_Graphs using the button in overview i have no problems! But when i run the function within the if statement, it goes into debug mode here:

shtHis.ChartObjects("Chart 11").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",B1:B" & lHorizon)

Hence i have concluded that there's something fishy about that if statement, and that the function works just fine. Can anybody help me?

Code:

Sub Button2_Click()
Dim shtOv As Worksheet: Set shtOv = Sheets("Overview")
Dim shtHis As Worksheet: Set shtHis = Sheets("Historic")
If shtHis.Range("A2").Value <> Date Then
shtHis.Range("A2").EntireRow.Insert
shtHis.Range("A2").Value = Date
shtOv.Range("H3:H6").Copy
shtHis.Range("B2").PasteSpecial Transpose:=True
Call Adjust_Graphs
End If


'Call Adjust_Graphs
End Sub


-----------
Sub Adjust_Graphs()
'Defining sheets/Variables
Dim shtOv As Worksheet: Set shtOv = Sheets("Overview")
Dim shtHis As Worksheet: Set shtHis = Sheets("Historic")
Dim lHorizon As Long: lHorizon = shtHis.Range("A2").End(xlDown).Row


'Adjusting graphs
shtHis.ChartObjects("Chart 11").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",B1:B" & lHorizon)
shtHis.ChartObjects("Chart 2").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",C1:C" & lHorizon)
shtHis.ChartObjects("Chart 3").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",D1:D" & lHorizon)
shtHis.ChartObjects("Chart 4").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",E1:E" & lHorizon)


End Sub
--------

Additional Info:
I have two sheets.. "Overview" from which i copy some information into my other sheet "Historic". The launch-button is located in "Overview".
The code is supposed to do two things:
1) If the data in "Historic" is not up to date, it should copy the info, which is up to date from "Overview" and insert in Historic
2) If it inserted a new row, it should adjust the graphs (I have 4, which refer to five columns in the "Historic" sheet, A = Dates and B,C,D,E contains the y-axis data, such that it includes the new info, which has just been inserted. If the sheet is already up to date, it just exits the sub.

The point is that i want to be able to update the graph each day, and then it should at the new up to date info from "Overview".

[Aflatoon]

I think a workbook would be more helpful.

[JKB]

I think a workbook would be more helpful.

how do i do that? :) (Kind of new in here!)

[JKB]

Ah if you mean whether i could upload the workbook itself, it's right here!
It's weird... Just tried the program on my mac (full program, with if statement) and it works like a charm, but it doesn't on windows, does it work on your windows? :)

[Paul_Hossler]

FWIW, it works OK for me in Win 10, Excel 2010

I did add two lines since I didn't think the event handler on Historic should fire (I could be wrong)


I also removed the Adjust_Graphs sub from the Historic sheet code module, I think one is enough so I went with the one in the standard module

I also spelt 'Winter' correctly :devil2:




Option Explicit
Sub Button1_Click()
Dim shtOv As Worksheet: Set shtOv = Sheets("Overview")
Dim shtHis As Worksheet: Set shtHis = Sheets("Historic")
If shtHis.Range("A2").Value <> Date Then
Application.EnableEvents = False '----------------------------------------------------------
shtHis.Range("A2").EntireRow.Insert
shtHis.Range("A2").Value = Date
shtOv.Range("H3:H6").Copy
shtHis.Range("B2").PasteSpecial Transpose:=True
Application.EnableEvents = True '----------------------------------------------------------
End If

Call Adjust_Graphs
End Sub


Sub Adjust_Graphs()
'Defining sheets/Variables
Dim shtOv As Worksheet: Set shtOv = Sheets("Overview")
Dim shtHis As Worksheet: Set shtHis = Sheets("Historic")
Dim lHorizon As Long: lHorizon = shtHis.Range("A2").End(xlDown).Row
'Adjusting graphs
shtHis.ChartObjects("Chart 1").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",B1:B" & lHorizon)
shtHis.ChartObjects("Chart 2").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",C1:C" & lHorizon)
shtHis.ChartObjects("Chart 3").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",D1:D" & lHorizon)
shtHis.ChartObjects("Chart 4").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",E1:E" & lHorizon)
End Sub

[JKB]

FWIW, it works OK for me in Win 10, Excel 2010

I did add two lines since I didn't think the event handler on Historic should fire (I could be wrong)


I also removed the Adjust_Graphs sub from the Historic sheet code module, I think one is enough so I went with the one in the standard module

I also spelt 'Winter' correctly :devil2:




Option Explicit
Sub Button1_Click()
Dim shtOv As Worksheet: Set shtOv = Sheets("Overview")
Dim shtHis As Worksheet: Set shtHis = Sheets("Historic")
If shtHis.Range("A2").Value <> Date Then
Application.EnableEvents = False '----------------------------------------------------------
shtHis.Range("A2").EntireRow.Insert
shtHis.Range("A2").Value = Date
shtOv.Range("H3:H6").Copy
shtHis.Range("B2").PasteSpecial Transpose:=True
Application.EnableEvents = True '----------------------------------------------------------
End If

Call Adjust_Graphs
End Sub


Sub Adjust_Graphs()
'Defining sheets/Variables
Dim shtOv As Worksheet: Set shtOv = Sheets("Overview")
Dim shtHis As Worksheet: Set shtHis = Sheets("Historic")
Dim lHorizon As Long: lHorizon = shtHis.Range("A2").End(xlDown).Row
'Adjusting graphs
shtHis.ChartObjects("Chart 1").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",B1:B" & lHorizon)
shtHis.ChartObjects("Chart 2").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",C1:C" & lHorizon)
shtHis.ChartObjects("Chart 3").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",D1:D" & lHorizon)
shtHis.ChartObjects("Chart 4").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lHorizon & ",E1:E" & lHorizon)
End Sub



Hahaha... For god sake! Thank you for spelling winter correct! ;)

Thanks a lot for the help! Then i just have to take a serious talk with my own windows-computer now! ;)

[JKB]

Just to be sure... When you ran the code, you ran it, by pressing the button right? :P
- Because it works, when you launch from developer, it just doesn't work, when launching from the button! :)

[Paul_Hossler]

Yes, tried it both ways

Try this version:

I didn't think the event handler on Historic should fire
I removed the Adjust_Graphs sub from the Historic sheet code module

[Aflatoon]

Works fine for me too (original version) in XL2010 on Win7.

What are you running?

[JKB]

Paul and Aflatoon! Thank you very much for the help, i have now located the error!
Paul using your sheet worked, but only with those two sheets, in my workbook i have much more than 2 sheets, which made the code go into bug mode.
Using your code in the original sheet didn't work, it didnt know which objects i was refering to for some reason!

To make it work i replaced the way of defining the disjoint range with union instead:
Union(shtHis.Range("A1:A" & lHorizon), shtHis.Range("B1:B" & lHorizon))

Which was apparently more robust when having more sheets! :) Thank you so much for the help guys!