"Object required" error in combo box/macro combination

Hi,

I'm trying to set up a command button that executes an action based on the combo box selection. The command button runs a plotting macro that is always the same, however, the combo box is used to select the worksheet which provides the data the plotting macro will apply to. All the data worksheets are laid out the same.

The macro is set up so the data plotted is from the ActiveSheet, which is referenced to by the string strSheetName. I was trying to use the combobox selection to set strSheetName equal to the worksheet chosen, and then activate this worksheet before running the plotting macro.

My problem is that I keep getting a compilation error: object required. The error shows up first in the line Set strSheetName As String. I tried initializing strSheetName as Public or Global, but Public had the same problem and Global wouldn't work for the string...this combo box code is in sheet20, whereas the macro is in a module (i hope that is okay).

Here's my code so far:

Formula:

Private Sub CBox1_Click()

Dim strSheetName As String



If CBox1 = FB1 Then

Set strSheetName = Worksheets("FB1")

End If

If CBox1 = FB2 Then

Set strSheetName = Worksheets("FB2")

End If

If CBox1 = FB3 Then

Set strSheetName = Worksheets("FB3")

End If

If CBox1 = FB4 Then

Set strSheetName = Worksheets("FB4")

End IfWorksheets(strSheetName).Activate

Application.Run ("GraphFB6")

strSheetName = Empty



End Sub

Any help is greatly appreciated!

Dan

Thanks for the help, the code now gets passed the Object Required error. Unfortunately it gets caught up in a Runtime Error: Type mismatch at

Formula:

Worksheets(strSheetName).Activate

Any suggestions?

Cheers

Do I reference strSheetName differently if it's a Worksheet rather than a string (noobie)?

Thanks, after changing that I was getting a runtime error "object variable or runtime variable not set". And the error occurred at:

Formula:

SheetName.Activate

at the bottom of all my IF/End if statements. Originally, I was going to call a macro in a module from this combo box code (which is in a sheet as a Microsoft Excel Object), but I tried just putting the macro in the bottom of the code as shown below. This doesn't work - nothing happens. When I go through line by line, there are no error messages, but it doesn't do anything. I'm wondering if the SheetName variable is not setting to a worksheet correctly, or if there's a variable types problem.

Formula:

Private Sub ComboBox1_Click()



Dim SheetName As Worksheet

Dim lastdatapoint As Long



If ComboBox1.Value = "FB1" Then

Set SheetName = Worksheets("FB1")

End If

If ComboBox1.Value = "FB2" Then

Set SheetName = Worksheets("FB2")

End If

If ComboBox1.Value = "FB3" Then

Set SheetName = Worksheets("FB3")

End If



Application.ScreenUpdating = False



With Worksheets("Plots")

'delete any other chart on the sheet before proceding

    On Error Resume Next

    .ChartObjects.Delete

End With



With Worksheets("SheetName")

    lastdatapoint = Range("B" & Rows.Count).End(xlUp).Row

    .Shapes.AddChart.Select

End With

    

With ActiveChart

    .ChartType = xlLine

    .SetSourceData Worksheets(SheetName).Range("$R$3:$R" & lastdatapoint)

    .SeriesCollection(1).XValues = Worksheets(SheetName).Range("$B$3:$B" & lastdatapoint)

    .Location Where:=xlLocationAsObject, Name:="Plots"

    

    'x axis label spacing

    .Axes(xlCategory).TickMarkSpacing = 10

    .Axes(xlCategory).TickLabelSpacing = 10

    

    'axes titles

    .Axes(xlCategory, xlPrimary).HasTitle = True

    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"

    .Axes(xlValue, xlPrimary).HasTitle = True

    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Temperature (C)"

End With



'make the graph area bigger

With ActiveChart.Parent

    .Height = 325

    .Width = 500

End With



Application.ScreenUpdating = True



Set SheetName = Nothing



End Sub

If you have any more thoughts as to why either of these problems are occurring please send them along!

Thanks again,

Dan

Okay, above nothing was happening because of a stupid mistake:

I had Private Sub ComboBox1_Click()

instead of Private Sub CommandButton1_Click()

After the change, I click the button and it goes to the worksheet selected from the drop down menu of the combobox, but no graphs are plotted

Thanks for the code JP, I actually managed to figure out my mistake, but yours looks quite similar (I didn't test it though).

The problem was that "SheetName" is a variable that represents a worksheet and isn't a worksheet. So while i have to reference my worksheets by writing "Worksheets("FB1").Range...." I have to write "SheetName.Range..." when referencing the variable.

Thanks for all the help