Multiple ranges with creating a chart via Excel VBA

Question

I am guessing this is a stupid logic/formatting issue, but I am pulling my hair out.

I am creating a chart in Excel, programmatically, using VBA. The user selects a range and this is passed on as a variable to setsoucedata. I performed this while recording a macro but I need this to be applicable to any user with any range, hence, variables and dynamic.

The macro shows the following, which works:

ActiveChart.SetSourceData Source:=Range("$C$36:$C$50,$E$36:$E$50,$K$36:$M$50")

My code to try and automate this is:

Dim rngTaskList As Range
Dim rngDateList As Range
Dim rngHiddenTable As Range

Set rngTaskList = Application.InputBox(Prompt:="Please select the task list of the project.", Title:="SPECIFY CELLS", Type:=8)
Set rngDateList = rngTaskList.Offset(0, 2)
Set rngHiddenTable = rngTaskList.Offset(0, 8)
ActiveSheet.Shapes.AddChart2(297, xlBarStacked).Select
ActiveChart.SetSourceData Source:=Range(rngTaskList, rngDateList, rngHiddenTable)

I keep getting a compile error: Wrong number of arguments or invalid property assignment.

I have tried about a dozen permutations, Unions, strings, to get this to work but I am sure there is something simple I am missing that should get this working. I know I can put the offsets within the Source but I broke it out to make it easier for me to understand and give my bloodshot eyes a break.

Anyone out there make any sense of this?

Thanks in advance.

Scott, you da man! Thank you, that fixed it.

ChiTriGuy
– ChiTriGuy

2025-07-10 18:09:44 +00:00
Commented Jul 10 at 18:09 — ChiTriGuy
– ChiTriGuy, Commented Jul 10 at 18:09

Scott Craner · Accepted Answer · 2025-07-09 21:09:45Z

2

Concatenate the addresses:

ActiveChart.SetSourceData Source:=Range(rngTaskList.address(0,0) & "," &rngDateList.address(0,0) & "," & rngHiddenTable.address(0,0))

Otherwise you are trying to pass 3 criteria to range which will not accept that.

edited Jul 9 at 21:09

answered Jul 9 at 20:55

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jon Peltier · Accepted Answer · 2025-11-19 00:02:28Z

1

Late to the party, but this also fixes it:

ActiveChart.SetSourceData Source:=Union(rngTaskList, rngDateList, rngHiddenTable)

answered Nov 19 at 0:02

Jon Peltier

6,1011 gold badge32 silver badges28 bronze badges

Collectives™ on Stack Overflow

Multiple ranges with creating a chart via Excel VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related