lookup cell value
 

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

lookup cell value
 

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

lookup cell value
 

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

lookup cell value
 

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

lookup cell value
 

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