ploting the graph according to the sheet selected

[satyampakker]

hi,

I have an excel sheet which has a user form contains a combobox is used for selecting the sheet by selecting the sheet name it opens the corresponding sheet and the list box contains the values of the first column of the sheet and it gets updated with respective to the selected sheet in the combobox by the first column of the selected sheet.In the sheet show user form button is there which shows the user form and in the user form we haveto select the sheet name labled as tool and week range we need to select two values int the weel range and click on plot button plots a graph between that week range to the corresponding values.

i am having a problem in doing this with other sheets my problem is am able to select the sheet name and the values in the listbox are updating by the values of their first column,but when iam slecting the weekrange and plot is clicked i getting an error i am unable to genralise it for n sheets it works for sheet1 only.

please help me ...


i am attaching my file and pasting the code here


Private Sub ComboBox1_Change()
Dim ws As String

ws = ComboBox1.Value
Sheets(ws).Select
ListBox1.List = Range(Cells(2, 1), Cells(2, 1).End(xlDown)).Value
End Sub
Private Sub CommandButton1_Click()
MakeChart
Unload UserForm1
End Sub
Private Sub UserForm_Initialize()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ComboBox1.AddItem ws.Name
Next
End Sub
Sub MakeChart()
Dim rng1 As Range, rng2 As Range

Application.ScreenUpdating = True
'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 = xlColumnClustered
.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


i was trying this code in the make chart method to genralise it but the code is not workin it is throwing a type mismatch error


Dim gs As String

gs = ComboBox1.Value
Sheets(ws).Select
.SetSourceData Source:=Sheets("gs").Range(rng1, rng2).Offset(, 1).Resize(, 2), PlotBy _
:=xlColumns
.Location Where:=xlLocationAsObject, Name:="gs"


thanks
satyam

[figment]

i tryed to get this error, on your sample workbook, but coudn't. although i did notice that you had well over 20 graphs stacked on top of each other. you might consider modifying the existing graph, rather then constently making new graphs.

although this section of code should be writen as this

Dim gs As String

gs = ComboBox1.Value
Sheets(ws).Select
.SetSourceData Source:=Sheets(gs).Range(rng1, rng2).Offset(, 1).Resize(, 2), PlotBy _
:=xlColumns
.Location Where:=xlLocationAsObject, Name:= gs

[satyampakker]

i tryed to get this error, on your sample workbook, but coudn't. although i did notice that you had well over 20 graphs stacked on top of each other. you might consider modifying the existing graph, rather then constently making new graphs.

although this section of code should be writen as this

Dim gs As String

gs = ComboBox1.Value
Sheets(ws).Select
.SetSourceData Source:=Sheets(gs).Range(rng1, rng2).Offset(, 1).Resize(, 2), PlotBy _
:=xlColumns
.Location Where:=xlLocationAsObject, Name:= gs


can you help with changmodifying the existing graph ??i am getting this error stacking of graphs when i am opening my file in a diffrent computer
is there any solution to overcome this problem??
my problem is i am unable to plot for diffrent sheets??