creating graphs from vba form

[satyampakker]

i have three columns week,X,and AVG in excel 2003

1)i need to create a user form which should contain week range which are already there in the week column ,how can i read all the column values into the form if i want to use dropdown box in the form??

2)if the user selects the week range i.e for ex:if he selects 9 and 22 (implies the values between 9 and 22) it should plot plot a graph between selected week range and X i.e it has to select the corresponding X values for that week range and must plot a graph.
how can i do that??

please provide me the solution and also some good books on vba and excel

thanks

[Aussiebear]

Hi Satyampakker. Welcome to VBAX. Others will help you with your graphing request. In your 3rd request you asked for some good books on VBA & Excel. That's a pretty open ended question since we don't yet know your level of experience with Excel and or VBA.

However, I personally use a book put out by Que Publishing called "Useing Microsoft Office Excel 2003" by Patrick Blattner, for Excel referencing. By useing both Excel's help and this book, most things tend to make some sence. There are hundreds of book out there, and other people may have their favourites as well.

With regard to VBA, there are a number of great resources here at vbaexpress.com which can be used to build your experience with VBA. Firstly, there is training available, if you go back to the portal page and select the option. Secondly you can go to the Knowlege Base section and see how certain sets of coding are written, or thirdly, search through the pages here for a topic.

Or you can simply ask questions here. :friends:

[mdmackillop]

Have a look at the attached

[Bob Phillips]

Did that work for you Malcolm?

For me it choked on



ActiveSheet.Shapes.AddChart.Select


and when I got past that, it then ran foul of



ActiveChart.SeriesCollection(1).XValues = "=Sheet1!" & rng1.Address & ":" & rng2.Address

[mdmackillop]

It does. I don't do much with charts and tweaked a recorded macro to get a result (excel 2007)

[Bob Phillips]

Aah, 2007!

It chokes in 2003.

[mdmackillop]

2003 version
Sub MakeChart()
Dim rng1 As Range, rng2 As Range
Application.ScreenUpdating = False
'First selected
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
Set rng1 = Columns(1).Find(ListBox1.List(i))
Exit For
End If
Next
'Last selected
For i = ListBox1.ListCount - 1 To 1 Step -1
If ListBox1.Selected(i) Then
Set rng2 = Columns(1).Find(ListBox1.List(i))
Exit For
End If
Next

Charts.Add
With ActiveChart
.ChartType = xlLineMarkers
.SetSourceData Source:=Sheets("Sheet1").Range(rng1, rng2).Offset(, 1).Resize(, 2), PlotBy _
:=xlColumns
.SeriesCollection(1).Name = "=""x"""
.SeriesCollection(2).Name = "=""Average"""
.Location Where:=xlLocationAsObject, Name:="Sheet1"
End With

Application.CommandBars("Chart").Visible = False
Range("A1").Select
Application.ScreenUpdating = True

End Sub

[satyampakker]

i am attaching a zip file containing my sample data and a readme text file where the description about the data and requirements are clearly mentioned

thanks
satyam

[mdmackillop]

You have not responded to any posts and I see nothing in your attachment that relates to your question.

[satyampakker]

You have not responded to any posts and I see nothing in your attachment that relates to your question.



i attached my files to the code that you wrote
did you got my attachments ??the attachment has two files i cleary mentionedthe description in read me file.

[mdmackillop]

See post #9

[satyampakker]

i attached my files to the code that you wrote
did you got my attachments ??the attachment has two files i cleary mentionedthe description in read me file.

the solution that you gave is for the question i posted in the thread is working but can i use combobox instead of listbox,and the chart type to column??sorry for not replying for the previous posts.

[satyampakker]

the solution that you gave is for the question i posted in the thread is working but can i use combobox instead of listbox,and the chart type to column??sorry for not replying for the previous posts.

i am sry i havent included the readme file in the previous attachment now i am sending the correct attachment please try solve my problem

thanks satyam

[mdmackillop]

Please post the workbook with the userform and code. I don't have time to replicate these.

[satyampakker]

Please post the workbook with the userform and code. I don't have time to replicate these.

i had already created the user form in the workbook press alt+f11 you can see the user form
thanks

[mdmackillop]

See post #14

Unless you get your attachments sorted out, I'm closing this thread.

[satyampakker]

See post #14

Unless you get your attachments sorted out, I'm closing this thread.

Sub MakeChart()
Dim rng1 As Range, rng2 As Range
Application.ScreenUpdating = False
'First selected
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
Set rng1 = Columns(1).Find(ListBox1.List(i))
Exit For
End If
Next
'Last selected
For i = ListBox1.ListCount - 1 To 1 Step -1
If ListBox1.Selected(i) Then
Set rng2 = Columns(1).Find(ListBox1.List(i))
Exit For
End If
Next

Charts.Add
With ActiveChart
.ChartType = xlLineMarkers
.SetSourceData Source:=Sheets("Sheet1").Range(rng1, rng2).Offset(, 1).Resize(, 2), PlotBy _
:=xlColumns
.SeriesCollection(1).Name = "=""x"""
.SeriesCollection(2).Name = "=""Average"""
.Location Where:=xlLocationAsObject, Name:="Sheet1"
End With

Application.CommandBars("Chart").Visible = False
Range("A1").Select
Application.ScreenUpdating = True

End Sub

in the code above if i want to use a combobox instead of a listbox what changes should be made in the code??what are the changes if i want to plot weeks/x as a column and weeks/avg as a line in the same graph?

thanks

[mdmackillop]

As you have contributed nothing to this thread, I am closing it,