Hello Group,

I'm new to Excel programming and currently have written a module that
sends a query to a server. Gets some information then it populates
sheet1. Next step is clicking a button which takes the user to a tab as
Chart1 and shows a grap based on data. Of course, each report is
generated base on one column of the sheet. For instance I have:

Day of moth Accidents Col2
Col3
1 345
2 23
3 114

I had forogt to past my code. The second thread has it.


On Dec 18, 10:59 am, "Amit" wrote:
Hello Group,

I'm new to Excel programming and currently have written a module that
sends a query to a server. Gets some information then it populates
sheet1. Next step is clicking a button which takes the user to a tab as
Chart1 and shows a grap based on data. Of course, each report is
generated base on one column of the sheet. For instance I have:

Day of moth Accidents Col2
Col3
1 345
2 23
3 114
.
.
.
30 78

Problem:

Each column is different so reports are related only to associated
column in sheet1 but they all have a time range of a month. So I need o
have X label as Day of Month (X axies) which never appears in my
graph! "this is one problem". It shows only number "1" the 15 day of
month.

also, as you will see I have sent the bar color to be green (in
following code) but sometimes they automatically chage to different
color (each bar gets a different color!) or at least the last one get
purple color to itself.

I will appreciate it some of you could give me advice on this problem.




Dim TheChart As Chart
Dim DataSheet As Worksheet
Dim CatTitles As Range
Dim SrcRange As Range
Dim SourceData As Range
Dim iBarIndex As Integer

Set TheChart = Sheets("Chart1")
Set DataSheet = Sheets("Sheet1")

With DataSheet
Set CatTitles = .Range("B7:B7")
Set SrcRange = .Range(Cells(8, Item), .Cells(37, Item))
End With

'Source Data
Set SourceData = Union(CatTitles, SrcRange)

With TheChart

.Activate

For iBarIndex = 1 To ActiveChart.SeriesCollection.Count
.SeriesCollection(iBarIndex).Interior.Color = RGB(0, 128,
0) '123
Next iBarIndex

.ChartGroups(1).Overlap = -35
.ChartGroups(1).GapWidth = 200
.SetSourceData Source:=SourceData, PlotBy:=xlRows 'SourceData

.Deselect

End With

'With ActiveChart.Axes(xlValue)
' .HasMajorGridlines = True
' .HasMinorGridlines = False
' End With

' Application.ScreenUpdating = True

'With ActiveChart.Axes(xlCategory)
' .HasMajorGridlines = False
' .HasMinorGridlines = False
'End With

Sincerely,
Amit.

In this line

.SetSourceData Source:=SourceData, PlotBy:=xlRows 'SourceData

change PlotBy:=xlRows to PlotBy:=xlColumns so it reads

.SetSourceData Source:=SourceData, PlotBy:=xlColumns 'SourceData

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Amit" wrote in message
ups.com...


I had forogt to past my code. The second thread has it.


On Dec 18, 10:59 am, "Amit" wrote:
Hello Group,

I'm new to Excel programming and currently have written a module that
sends a query to a server. Gets some information then it populates
sheet1. Next step is clicking a button which takes the user to a tab as
Chart1 and shows a grap based on data. Of course, each report is
generated base on one column of the sheet. For instance I have:

Day of moth Accidents Col2
Col3
1 345
2 23
3 114
.
.
.
30 78

Problem:

Each column is different so reports are related only to associated
column in sheet1 but they all have a time range of a month. So I need o
have X label as Day of Month (X axies) which never appears in my
graph! "this is one problem". It shows only number "1" the 15 day of
month.

also, as you will see I have sent the bar color to be green (in
following code) but sometimes they automatically chage to different
color (each bar gets a different color!) or at least the last one get
purple color to itself.

I will appreciate it some of you could give me advice on this problem.




Dim TheChart As Chart
Dim DataSheet As Worksheet
Dim CatTitles As Range
Dim SrcRange As Range
Dim SourceData As Range
Dim iBarIndex As Integer

Set TheChart = Sheets("Chart1")
Set DataSheet = Sheets("Sheet1")

With DataSheet
Set CatTitles = .Range("B7:B7")
Set SrcRange = .Range(Cells(8, Item), .Cells(37, Item))
End With

'Source Data
Set SourceData = Union(CatTitles, SrcRange)

With TheChart

.Activate

For iBarIndex = 1 To ActiveChart.SeriesCollection.Count
.SeriesCollection(iBarIndex).Interior.Color = RGB(0, 128,
0) '123
Next iBarIndex

.ChartGroups(1).Overlap = -35
.ChartGroups(1).GapWidth = 200
.SetSourceData Source:=SourceData, PlotBy:=xlRows 'SourceData

.Deselect

End With

'With ActiveChart.Axes(xlValue)
' .HasMajorGridlines = True
' .HasMinorGridlines = False
' End With

' Application.ScreenUpdating = True

'With ActiveChart.Axes(xlCategory)
' .HasMajorGridlines = False
' .HasMinorGridlines = False
'End With

Sincerely,
Amit.

Hello Jon,

Thank you so much. Yes. that was the problem. I truly appreciate your
response. However, what I don't understand is why the graph bars' color
keep changing !!!
Also, is there any solution to make the chart readonly? to avoid
changes by user?

Once again thanks,
Amit




On Dec 18, 1:53 pm, "Jon Peltier"
wrote:
In this line

.SetSourceData Source:=SourceData, PlotBy:=xlRows 'SourceData

change PlotBy:=xlRows to PlotBy:=xlColumns so it reads

.SetSourceData Source:=SourceData, PlotBy:=xlColumns 'SourceData

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutionshttp://PeltierTech.com
_______

"Amit" wrote in oglegroups.com...





I had forogt to past my code. The second thread has it.

On Dec 18, 10:59 am, "Amit" wrote:
Hello Group,

I'm new to Excel programming and currently have written a module that
sends a query to a server. Gets some information then it populates
sheet1. Next step is clicking a button which takes the user to a tab as
Chart1 and shows a grap based on data. Of course, each report is
generated base on one column of the sheet. For instance I have:

Day of moth Accidents Col2
Col3
1 345
2 23
3 114
.
.
.
30 78

Problem:

Each column is different so reports are related only to associated
column in sheet1 but they all have a time range of a month. So I need o
have X label as Day of Month (X axies) which never appears in my
graph! "this is one problem". It shows only number "1" the 15 day of
month.

also, as you will see I have sent the bar color to be green (in
following code) but sometimes they automatically chage to different
color (each bar gets a different color!) or at least the last one get
purple color to itself.

I will appreciate it some of you could give me advice on this problem.

Dim TheChart As Chart
Dim DataSheet As Worksheet
Dim CatTitles As Range
Dim SrcRange As Range
Dim SourceData As Range
Dim iBarIndex As Integer

Set TheChart = Sheets("Chart1")
Set DataSheet = Sheets("Sheet1")

With DataSheet
Set CatTitles = .Range("B7:B7")
Set SrcRange = .Range(Cells(8, Item), .Cells(37, Item))
End With

'Source Data
Set SourceData = Union(CatTitles, SrcRange)

With TheChart

.Activate

For iBarIndex = 1 To ActiveChart.SeriesCollection.Count
.SeriesCollection(iBarIndex).Interior.Color = RGB(0, 128,
0) '123
Next iBarIndex

.ChartGroups(1).Overlap = -35
.ChartGroups(1).GapWidth = 200
.SetSourceData Source:=SourceData, PlotBy:=xlRows 'SourceData

.Deselect

End With

'With ActiveChart.Axes(xlValue)
' .HasMajorGridlines = True
' .HasMinorGridlines = False
' End With

' Application.ScreenUpdating = True

'With ActiveChart.Axes(xlCategory)
' .HasMajorGridlines = False
' .HasMinorGridlines = False
'End With

Sincerely,
Amit.- Hide quoted text -- Show quoted text -

If you plot a column of data by rows, each row becomes its own series with
its own distinct colors.

To protect the chart:
http://peltiertech.com/Excel/ChartsH...rotection.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Amit" wrote in message
ups.com...

Hello Jon,

Thank you so much. Yes. that was the problem. I truly appreciate your
response. However, what I don't understand is why the graph bars' color
keep changing !!!
Also, is there any solution to make the chart readonly? to avoid
changes by user?

Once again thanks,
Amit




On Dec 18, 1:53 pm, "Jon Peltier"
wrote:
In this line

.SetSourceData Source:=SourceData, PlotBy:=xlRows 'SourceData

change PlotBy:=xlRows to PlotBy:=xlColumns so it reads

.SetSourceData Source:=SourceData, PlotBy:=xlColumns 'SourceData

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutionshttp://PeltierTech.com
_______

"Amit" wrote in
oglegroups.com...





I had forogt to past my code. The second thread has it.

On Dec 18, 10:59 am, "Amit" wrote:
Hello Group,

I'm new to Excel programming and currently have written a module that
sends a query to a server. Gets some information then it populates
sheet1. Next step is clicking a button which takes the user to a tab
as
Chart1 and shows a grap based on data. Of course, each report is
generated base on one column of the sheet. For instance I have:

Day of moth Accidents Col2
Col3
1 345
2 23
3 114
.
.
.
30 78

Problem:

Each column is different so reports are related only to associated
column in sheet1 but they all have a time range of a month. So I need
o
have X label as Day of Month (X axies) which never appears in my
graph! "this is one problem". It shows only number "1" the 15 day of
month.

also, as you will see I have sent the bar color to be green (in
following code) but sometimes they automatically chage to different
color (each bar gets a different color!) or at least the last one get
purple color to itself.

I will appreciate it some of you could give me advice on this problem.

Dim TheChart As Chart
Dim DataSheet As Worksheet
Dim CatTitles As Range
Dim SrcRange As Range
Dim SourceData As Range
Dim iBarIndex As Integer

Set TheChart = Sheets("Chart1")
Set DataSheet = Sheets("Sheet1")

With DataSheet
Set CatTitles = .Range("B7:B7")
Set SrcRange = .Range(Cells(8, Item), .Cells(37, Item))
End With

'Source Data
Set SourceData = Union(CatTitles, SrcRange)

With TheChart

.Activate

For iBarIndex = 1 To ActiveChart.SeriesCollection.Count
.SeriesCollection(iBarIndex).Interior.Color = RGB(0, 128,
0) '123
Next iBarIndex

.ChartGroups(1).Overlap = -35
.ChartGroups(1).GapWidth = 200
.SetSourceData Source:=SourceData, PlotBy:=xlRows 'SourceData

.Deselect

End With

'With ActiveChart.Axes(xlValue)
' .HasMajorGridlines = True
' .HasMinorGridlines = False
' End With

' Application.ScreenUpdating = True

'With ActiveChart.Axes(xlCategory)
' .HasMajorGridlines = False
' .HasMinorGridlines = False
'End With

Sincerely,
Amit.- Hide quoted text -- Show quoted text -

Hi Jon again,

Yes that was the solution. I truly appreciate your help.

Thanks and merry Christmas



On Dec 19, 5:19 am, "Jon Peltier"
wrote:
If you plot a column of data by rows, each row becomes its own series with
its own distinct colors.

To protect the chart:
http://peltiertech.com/Excel/ChartsH...rotection.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutionshttp://PeltierTech.com
_______

"Amit" wrote in oglegroups.com...





Hello Jon,

Thank you so much. Yes. that was the problem. I truly appreciate your
response. However, what I don't understand is why the graph bars' color
keep changing !!!
Also, is there any solution to make the chart readonly? to avoid
changes by user?

Once again thanks,
Amit

On Dec 18, 1:53 pm, "Jon Peltier"
wrote:
In this line

.SetSourceData Source:=SourceData, PlotBy:=xlRows 'SourceData

change PlotBy:=xlRows to PlotBy:=xlColumns so it reads

.SetSourceData Source:=SourceData, PlotBy:=xlColumns 'SourceData

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutionshttp://PeltierTech.com
_______

"Amit" wrote in
oglegroups.com...

I had forogt to past my code. The second thread has it.

On Dec 18, 10:59 am, "Amit" wrote:
Hello Group,

I'm new to Excel programming and currently have written a module that
sends a query to a server. Gets some information then it populates
sheet1. Next step is clicking a button which takes the user to a tab
as
Chart1 and shows a grap based on data. Of course, each report is
generated base on one column of the sheet. For instance I have:

Day of moth Accidents Col2
Col3
1 345
2 23
3 114
.
.
.
30 78

Problem:

Each column is different so reports are related only to associated
column in sheet1 but they all have a time range of a month. So I need
o
have X label as Day of Month (X axies) which never appears in my
graph! "this is one problem". It shows only number "1" the 15 day of
month.

also, as you will see I have sent the bar color to be green (in
following code) but sometimes they automatically chage to different
color (each bar gets a different color!) or at least the last one get
purple color to itself.

I will appreciate it some of you could give me advice on this problem.

Dim TheChart As Chart
Dim DataSheet As Worksheet
Dim CatTitles As Range
Dim SrcRange As Range
Dim SourceData As Range
Dim iBarIndex As Integer

Set TheChart = Sheets("Chart1")
Set DataSheet = Sheets("Sheet1")

With DataSheet
Set CatTitles = .Range("B7:B7")
Set SrcRange = .Range(Cells(8, Item), .Cells(37, Item))
End With

'Source Data
Set SourceData = Union(CatTitles, SrcRange)

With TheChart

.Activate

For iBarIndex = 1 To ActiveChart.SeriesCollection.Count
.SeriesCollection(iBarIndex).Interior.Color = RGB(0, 128,
0) '123
Next iBarIndex

.ChartGroups(1).Overlap = -35
.ChartGroups(1).GapWidth = 200
.SetSourceData Source:=SourceData, PlotBy:=xlRows 'SourceData

.Deselect

End With

'With ActiveChart.Axes(xlValue)
' .HasMajorGridlines = True
' .HasMinorGridlines = False
' End With

' Application.ScreenUpdating = True

'With ActiveChart.Axes(xlCategory)
' .HasMajorGridlines = False
' .HasMinorGridlines = False
'End With

Sincerely,
Amit.- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -