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")

 

[Fluff]

They need to be declared outside the function like this

Option Explicit
Public myrange As Range
Public mycol As String
Public myrow_start As String
Public myrow_end As String
Public chartT As XlChartType

Public Function mysub()

 

[papakos]

They need to be declared outside the function like this

Option Explicit
Public myrange As Range
Public mycol As String
Public myrow_start As String
Public myrow_end As String
Public chartT As XlChartType

Public Function mysub()

Tried that. Still myrange=0

 

[Fluff]

You're not setting MyRange anywhere in the function.

 

[papakos]

You're not setting MyRange anywhere in the function.

No. I want to change it depending the commandbutton pressed.
So, i set it when commandbutton pressed with the Public values .

 

[Fluff]

try

Set myrange = mycol & myrow_start & ":" & mycol & myrow_end

 

[papakos]

try

Set myrange = mycol & myrow_start & ":" & mycol & myrow_end

No! Type mismatch

 

[Fluff]

Set myrange = rnage(mycol & myrow_start & ":" & mycol & myrow_end)

 

[papakos]

Set myrange = rnage(mycol & myrow_start & ":" & mycol & myrow_end)

Runtime error '1004'
Method "range" of object "_Global" failed

 

[Fluff]

How are you calling the Function?