Getting at the range or cells
 

Hi - I have been given the code below and have not used VB functions
much before.

What it does is alows a user to select a cell in a grid of numbers, a
range "selectedrow" is then created which provides the source data for
a graph.

My problem is that the grid has certain columns hidden by default and
thus the graph only uses as its source data from the non-hidden cols.

Does anyone know how I could get some value from the function which
would allow me to somehow manipulate the values the function returns
to enable my graph to see all the cols?

Thanks

Andrew

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim valrange As Integer
Dim end_row1 As Integer
Dim rowcheck As Integer


If Target.Row = 8 And Target.Row <= 8 +
ActiveWorkbook.Names("numValues").RefersToRange.Va lue - 1 _
And Target.Column = 3 And Target.Column <= 16 Then

ActiveWorkbook.Names("Data").RefersToRange.Interio r.ColorIndex
= 0
ActiveWorkbook.Names("Data").RefersToRange.Font.Co lor = 1

'Range("B10:B" & end_val + 9).Name = "Left_chart_labels"


ThisWorkbook.Names("ValRange").RefersToRange.Inter ior.ColorIndex = 0
ThisWorkbook.Names("ValRange").RefersToRange.Font. ColorIndex =
1


ThisWorkbook.Names("SelectedRow").RefersToRange.In terior.ColorIndex =
4

ThisWorkbook.Names("SelectedCol").RefersToRange.Fo nt.ColorIndex = 5

ActiveWorkbook.Names("highlightCol").RefersToRange .Value =
Target.Column - 3
ActiveWorkbook.Names("highlightRow").RefersToRange .Value =
Target.Row - 10


ThisWorkbook.Names("SelectedRow").RefersToRange.In terior.ColorIndex =
25

ThisWorkbook.Names("SelectedRow").RefersToRange.Fo nt.ColorIndex = 19


ThisWorkbook.Names("SelectedCol").RefersToRange.In terior.ColorIndex =
25

ThisWorkbook.Names("SelectedCol").RefersToRange.Fo nt.ColorIndex = 19

Target.Interior.ColorIndex = 0
Target.Font.ColorIndex = 1

End If

End Sub

Getting at the range or cells
 

with you chart selected, go to Tools=Options, then go to the Chart tab.
Uncheck Chart Visible Cells Only.

--
Regards,
Tom Ogilvy


"Andreww" wrote:

Hi - I have been given the code below and have not used VB functions
much before.

What it does is alows a user to select a cell in a grid of numbers, a
range "selectedrow" is then created which provides the source data for
a graph.

My problem is that the grid has certain columns hidden by default and
thus the graph only uses as its source data from the non-hidden cols.

Does anyone know how I could get some value from the function which
would allow me to somehow manipulate the values the function returns
to enable my graph to see all the cols?

Thanks

Andrew

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim valrange As Integer
Dim end_row1 As Integer
Dim rowcheck As Integer


If Target.Row = 8 And Target.Row <= 8 +
ActiveWorkbook.Names("numValues").RefersToRange.Va lue - 1 _
And Target.Column = 3 And Target.Column <= 16 Then

ActiveWorkbook.Names("Data").RefersToRange.Interio r.ColorIndex
= 0
ActiveWorkbook.Names("Data").RefersToRange.Font.Co lor = 1

'Range("B10:B" & end_val + 9).Name = "Left_chart_labels"


ThisWorkbook.Names("ValRange").RefersToRange.Inter ior.ColorIndex = 0
ThisWorkbook.Names("ValRange").RefersToRange.Font. ColorIndex =
1


ThisWorkbook.Names("SelectedRow").RefersToRange.In terior.ColorIndex =
4

ThisWorkbook.Names("SelectedCol").RefersToRange.Fo nt.ColorIndex = 5

ActiveWorkbook.Names("highlightCol").RefersToRange .Value =
Target.Column - 3
ActiveWorkbook.Names("highlightRow").RefersToRange .Value =
Target.Row - 10


ThisWorkbook.Names("SelectedRow").RefersToRange.In terior.ColorIndex =
25

ThisWorkbook.Names("SelectedRow").RefersToRange.Fo nt.ColorIndex = 19


ThisWorkbook.Names("SelectedCol").RefersToRange.In terior.ColorIndex =
25

ThisWorkbook.Names("SelectedCol").RefersToRange.Fo nt.ColorIndex = 19

Target.Interior.ColorIndex = 0
Target.Font.ColorIndex = 1

End If

End Sub

Getting at the range or cells
 

Actually have found following solution...

put this code at the top of the function

If Not Intersect(Target, Range("Data")) Is Nothing Then
Range("A2").Value = Target.Row
End If

And it puts selected row number in cell A2 on the worksheet. As I
know all the columns I can create a data source for the chart outside
of the grid.

Thanks Tom anyway... hope this helps someone else.

Regards

Andrew

Getting at the range or cells
 

Guess by "graph" you meant "colored cells"

--
Regards,
Tom Ogilvy


"Andreww" wrote:

Actually have found following solution...

put this code at the top of the function

If Not Intersect(Target, Range("Data")) Is Nothing Then
Range("A2").Value = Target.Row
End If

And it puts selected row number in cell A2 on the worksheet. As I
know all the columns I can create a data source for the chart outside
of the grid.

Thanks Tom anyway... hope this helps someone else.

Regards

Andrew