How do you call a graph from JUST vba?

[ExcelVBANoobie]

Hi all,

Sorry to bother you guys with a (simple?) problem. I'm not entirely new to VBA but new enough that I am a noobie compared to everyone here.

So what I'm envisioning is this:

1) Let's say I have 2 worksheets. Worksheet 1 and Worksheet 2. Worksheet 2 is HIDDEN but has DATA in it. For example, Column A has 1-20. Column B has =rand()*100.

2) Let's say you make a bar chart with the two columns of data manually. You go to insert --> bar graph = bar graph with x-axis 1,2,3...20 etc. That's the end product that I want. BUT I want to do all that in 1 click of a button.

3) Let's say I make a userform OR an activeX command button in Worksheet 1 (where there's no data). I click the button and the graph just pops up in worksheet 1 with all the data in 2. Is that possible??

Thanks so much in advance guys!

[watersev]

hi ExcelVBANoobie, welcome to Excelforum, option

[ExcelVBANoobie]

Hi Watersev,

Thanks so much for responding! That's EXACTLY what I needed. Thanks SO MUCH!! I wish I could commend you or something but I dont think Excel Forum has that lol.

Thanks again,

Noobie

[ExcelVBANoobie]

Hi Guys,

Sorry to bother again but I have a follow-up question. So i've been playing around with the coding:

Sub NewTest()

With Sheets("Sheet3")
.Shapes.AddChart
With .ChartObjects(1).Chart
.ChartType = xlColumnClustered
.SetSourceData Source:=Sheets("Sheet3").Range("a1:a" & Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row)
End With
End With

End Sub


Can someone quickly explain to me what it's doing? I understand all the lines except for the most important one:

.SetSourceData Source:=Sheets("Sheet3").Range("a1:a" & Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row)

I understand it's pulling data from sheet 3 column a. However, I'm trying to put some data into column b as well and it's not working. I'm trying this code:

.SetSourceData Source:=Sheets("Sheet3").Range("a1:a" & "b1:b" & Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row)

But I'm getting an error. What am i doing wrong?

[watersev]

first of all you need to wrap any posted code using hash sign in message formating panel otherwise you might not get any replies. It will look like this then:

Please Login or Register  to view this content.

It sets source range for the graph from A1 to the last row with value in A column in column B. You've got the error due wrong syntax for Range, check VB help for further details

[ExcelVBANoobie]

Great! Thanks!!!