Public Function declare for use in form with CommandButton and listbox

[papakos]

Hello,
Trying to declare some variables in Module, so when i hit commandbutton to take another value (also depending from listbox value) to create a graph in a picturebox,
but when i execute the values are 0.
Here is the code.

Option Explicit
Public Function mysub()
Dim myrange As Range
Dim mycol As String
Dim myrow_start As String
Dim myrow_end As String
Dim chartT As XlChartType




If CommandButton1.Click Then
mycol = "C"
Else
If CommandButton2.Click Then
mycol = "D"
Else
If CommandButton3.Click Then
mycol = "E"
Else
If CommandButton4.Click Then
mycol = "F"
etc etc
End If
End If
End If
End If


Select Case ListBox1.Value

Case ListBox1.Value = "A"
myrow_start = "6"
myrow_end = "17"

Case ListBox1.Value = "B"
myrow_start = "22"
myrow_end = "33"

Case ListBox1.Value = "C"
myrow_start = "38"
myrow_end = "49"
etc etc

End Function




Private Sub CommandButton1_Click()
myrange = mycol & myrow_start & ":" & mycol & myrow_end
Set ChartData_GR = ActiveSheet.Range(myrange)


On Error Resume Next
ActiveSheet.ChartObjects.Delete
On Error GoTo 0
ChartData.Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartArea.Width = 468
ActiveChart.ChartArea.Height = 354
ActiveChart.SetSourceData Source:=myrange
ActiveChart.ChartType = chartT
ActiveChart.ApplyLayout (5)
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = ListBox1.Value
ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("C2:N2")

 

[papakos]

How are you calling the Function?

Sorry for the delayed answer, i was with fever for two days.
SOLVED IT like this.

Public Function mysub(ByVal cmdbutton As Integer)

CHANGE THE IF'S WITH Select Case like this

Select Case cmdbutton
Case 1 'CommandButton1.Click
mycol = "C"
Case 2 ' CommandButton2.Click
mycol = "D"


So when i press commandbutton1 i call mysub(1)
when i press commandbutton2 i call mysub(2)
etc
etc

Thanks Guys