lookup cell value

[RichHoughton]

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

[Tim Kredlo]

I cannot tell what your question is.


"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

[RichHoughton]

Hello

Sorry my explanation probably wasn't great. My core problem is assigning
the value generated by the combo-box, i.e company name, as a VBA variable. I
would then hope to check the variable, i.e IF variable = "Company A" THEN,
use range A2:B12 as the graph range, ELSE, use the range A14:B40.

I have this rubbish code so far;

Private Sub openform_Click()
Dim CompanyName As String
Sheets("lookup").Select
CompanyName = Range("B20").Text


If (CompanyName = "CompanyA") Then
Sheets("rich").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.XValues = "=lookup!R2C1:R12C2"
Else
Sheets("rich").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.XValues = "=lookup!R2C14:R40C2"
End If

End Sub

Thanks for responding

Rich

"Tim Kredlo" wrote:

> I cannot tell what your question is.
>
>
> "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

[Fordguy]

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

[RichHoughton]

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