FYI Finally got it to work. The combo box generates a number, i.e. 1. Then
i use this code;

Private Sub openform_Click()
Dim PCT As String
Sheets("lookup").Select

PCT = Worksheets("lookup").Range("B9")

If (PCT = "1") Then
Sheets("richchart").Select
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Sheets("lookup").Range("E4:G23")
Else
Sheets("richchart").Select
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Sheets("lookup").Range("E27:G63")
End If

End Sub

Which works.

Thanks all

R

"Fordguy" wrote:

Your combo box will place the selected value in a cell you determine, let's
say C1. You can see that information with a line of code something like:

CompanyName=Range("C1").Value

Rob

"RichHoughton" wrote:

Hi

I am trying to use VBA to pick up a cell value (company name) generated by a
combo box and create charts based upon that value. I have 2 companies to
which there are varying providers and values associated to each provider.
The raw data is held on one sheet. Company A, in A2:B12 and Company B, in
A14:B40. Where column A in the provider name and column B the numerical
value. The main worksheet houses the combo-box to which the desired company
can be selected and numerous buttons which all generate different charts.

I would be grateful for any help, if my explaination of my problem makes any
sense to anyone.

TIA

Rich