combobox_change

[ankita.sethi]

Hi,

I have written 3 VBA macros, namely:
chart_inprogress_Category
chart_inprogress_StrategicAlignment
chart_inprogress_investmentName

These macros change 1 common chart - based on certain criteras.
I want to create 1 combobox where the user can choose which one is wanted.

Here is the code I have written in the "Worksheet" code window
Private Sub Worksheet_Activate()
ComboBox1.Clear
ComboBox1.AddItem "Category"
ComboBox1.AddItem "StrategicAlignment"
ComboBox1.AddItem "Investment Name"
ComboBox1.Text = ComboBox1.List(0)
End Sub


Private Sub ComboBox1_Change()
Select Case ComboBox1.Text

Case "Category"
chart_inprogress_Category

Case "StrategicAlignment"
chart_inprogress_StrategicAlignment

Case "Investment Name"
chart_inprogress_investmentName

Case Else
End Select
End Sub



Howeve, this is throwing an error. In the combobox, no matter what value I choose, it comes back to the first one "Category".
Moreover, if I run the macro individually, they run fine. But running through combobox is giving an error (even running for 'Category').

Error: "Object Variable or with block variable not set"

PLEASE HELP!!

 

[taurean]

The code compiles correctly. I just wonder if there's something wrong with the specific macros.

Did you try to STEP THROUGH the code?

 

[ankita.sethi]

Please find below my code. I have tried to troubleshoot a lot, but to no effect
The error occurs in the line that is highlighted in red.

Sub chart_inprogress_Category()
Dim a, b, c, ot, gt As Integer
Dim col As Long, cc As String

'Find the column number for "Category"
Sheets("dmproject_queue08112011 In Prog").Select
col = Application.WorksheetFunction.Match("Category", Range("A1:CA1"), 0)
cc = ConvertColName(col)
Dim lrow As Integer
Range(cc & "2").Select
Selection.End(xlDown).Select
lrow = ActiveCell.Row

a = 0
b = 0
c = 0
ot = 0
For i = 2 To lrow
If Range(cc & i).Value = "Compliance Related (e.g., FIM)" Then
a = a + 1
ElseIf Range(cc & i).Value = "Other Projects" Then
b = b + 1
ElseIf Range(cc & i).Value = "Regulatory Related" Then
c = c + 1
Else: ot = ot + 1
End If
Next i
gt = a + b + c + ot

Sheets("Sheet1").Range("b34").Value = a
Sheets("Sheet1").Range("b35").Value = b
Sheets("Sheet1").Range("b36").Value = c
Sheets("Sheet1").Range("b37").Value = gt

'change the chart numbers
Sheets("Sheet1").ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(1).Values = "={" & a & "," & b & "," & c & "}"
ActiveChart.SeriesCollection(1).XValues = "={""Compliance Related (e.g., FIM)"",""Other Projects"",""Regulatory Related""}"

'chart title
ActiveChart.ChartTitle.Select
Selection.Characters.Text = "In Progress Initiatives - By Category"

Range("A5").Select
End Sub

 

[taurean]

I have never tried charts through VBA but it looks to me that you are trying to pass an array of values and failing. Take a look at this link:
http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html
If it still doesn't work out then tomorrow I'll give it a go. Good night!

 

[ankita.sethi]

Thanks a lot for your quick response!

It is not an array actually.
The a,b,c and d are four variables getting calculated in this macro itself.

As I said earlier, the Macro works fine if I run it just as it is. However, it fails if I try to run it through the combobox and i cannot figure out why!!

 

[taurean]

Can you upload a sample file? You can do this on any free file hosting website and then provide the URL here. Use dummy data if you have any sensitive info.

 

[ankita.sethi]

Hey I got it corrected! yay!

Here is the new code:

'change the chart numbers
Worksheets("Sheet1").Activate
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(1).Values = "={" & a & "," & b & "," & c & "}"
ActiveChart.SeriesCollection(1).XValues = "={""Compliance Related (e.g., FIM)"",""Other Projects"",""Regulatory Related""}"

However I do not understand why this works instead of the earlier one
If you know why, please let me know

 

[taurean]

Activate and select are tricky and are generally a result / byproduct of recorded code. You can bypass them in most of the cases. For example, your code can be reduced to:

Example 1:

With Sheets("Sheet1").ChartObjects("Chart 3").SeriesCollection(1)
.Values = "={" & a & "," & b & "," & c & "}"
.XValues = "={""Compliance Related (e.g., FIM)"",""Other Projects"",""Regulatory Related""}"
End With

Example 2:
Another instance would be here.
This code:

Dim lrow As Integer
Range(cc & "2").[COLOR=red]Select[/COLOR]
[COLOR=red]Selection.[/COLOR]End(xlDown).[COLOR=red]Select[/COLOR]
lrow = [COLOR=red]ActiveCell[/COLOR].Row

Can be:

Dim lrow As Integer
lrow = Range(cc & "2").End(xlDown).Row

Example 3:
This reference:

ActiveChart.ChartTitle.Characters.Text = "In Progress Initiatives - By Category"

can be set explicitly as:

Sheets("Sheet1").ChartObjects("Chart 3").ChartTitle.Characters.Text = "In Progress Initiatives - By Category"

With this your code would become leaner and faster!

 

[ankita.sethi]

woahh man!
thanks a lot for the tips!! i am still on the beginning of my learning curve with coding..

(perhaps I should send all my codes over to you!)
But no kidding, your advice is helpful! thanks a ton!!