Shape Range
 

Below is a subroutine that passes in 2 variables. I am concerned with
the word "Selection" used for ShapeRange and would prefer to eliminate
it. All my attemptes have resulted in errors. I want qualify the
code to prevent error.

Is selection a keyword for ShapeRange or is there a way to qualify it?

Thanks in advance.

Public Sub CountryColor(ByVal strCountry As String, _
ByVal dColor As Double)

Dim shtMap As Worksheet, rgData As Range, strShapeNum As String
Dim i As Integer, dColor1 As Double

Set shtMap =
ThisWorkbook.Application.ThisWorkbook.Worksheets(" Map")
Set rgData =
ThisWorkbook.Application.ThisWorkbook.Worksheets(" Data").Range("RegionCountryData")

On Error Resume Next
shtMap.Select

For i = 1 To rgData.Rows.Count

strShapeNum = rgData.Cells(i, 3).Value

shtMap.Shapes(strShapeNum).Select

'This is my issue"
With Selection.ShapeRange
.Fill.ForeColor.RGB = dColor
.Fill.Visible = msoTrue
.Fill.Solid
End With

Next i

End Sub

Shape Range
 

'Does this not work?

With shtMap.Shapes(strShapeNum).ShapeRange
.Fill.ForeColor.RGB = dColor
.Fill.Visible = msoTrue
.Fill.Solid
End With

--
Best Regards,

Luke M
"kuhrty" wrote in message
...
Below is a subroutine that passes in 2 variables. I am concerned with
the word "Selection" used for ShapeRange and would prefer to eliminate
it. All my attemptes have resulted in errors. I want qualify the
code to prevent error.

Is selection a keyword for ShapeRange or is there a way to qualify it?

Thanks in advance.

Public Sub CountryColor(ByVal strCountry As String, _
ByVal dColor As Double)

Dim shtMap As Worksheet, rgData As Range, strShapeNum As String
Dim i As Integer, dColor1 As Double

Set shtMap =
ThisWorkbook.Application.ThisWorkbook.Worksheets(" Map")
Set rgData =
ThisWorkbook.Application.ThisWorkbook.Worksheets(" Data").Range("RegionCountryData")

On Error Resume Next
shtMap.Select

For i = 1 To rgData.Rows.Count

strShapeNum = rgData.Cells(i, 3).Value

shtMap.Shapes(strShapeNum).Select

'This is my issue"
With Selection.ShapeRange
.Fill.ForeColor.RGB = dColor
.Fill.Visible = msoTrue
.Fill.Solid
End With

Next i

End Sub

Shape Range
 

Luke,

Thank you for your input, when I attempt to qualify that way, the code
does run but the shapes don't fill with the color passed. I would
have assumed it is the correct way to code it but with an unexpected
result.

Thanks again for your response.

Mark

On Apr 21, 4:37*pm, "Luke M" wrote:
'Does this not work?

* * * * With shtMap.Shapes(strShapeNum).ShapeRange
* * * * * * .Fill.ForeColor.RGB = dColor
* * * * * * .Fill.Visible = msoTrue
* * * * * * .Fill.Solid
* * * * End With

--
Best Regards,

Luke M"kuhrty" wrote in message

...



Below is a subroutine that passes in 2 variables. *I am concerned with
the word "Selection" used for ShapeRange and would prefer to eliminate
it. *All my attemptes have resulted in errors. *I want qualify the
code to prevent error.

Is selection a keyword for ShapeRange or is there a way to qualify it?

Thanks in advance.

Public Sub CountryColor(ByVal strCountry As String, _
* * * * * * * * * * * *ByVal dColor As Double)

* *Dim shtMap As Worksheet, rgData As Range, strShapeNum As String
* *Dim i As Integer, dColor1 As Double

* *Set shtMap =
ThisWorkbook.Application.ThisWorkbook.Worksheets(" Map")
* *Set rgData =
ThisWorkbook.Application.ThisWorkbook.Worksheets(" Data").Range("RegionCount*ryData")

* *On Error Resume Next
* *shtMap.Select

* *For i = 1 To rgData.Rows.Count

* * * *strShapeNum = rgData.Cells(i, 3).Value

* * * *shtMap.Shapes(strShapeNum).Select

* * * 'This is my issue"
* * * *With Selection.ShapeRange
* * * * * *.Fill.ForeColor.RGB = dColor
* * * * * *.Fill.Visible = msoTrue
* * * * * *.Fill.Solid
* * * *End With

* *Next i

End Sub- Hide quoted text -

- Show quoted text -

Shape Range
 

The next thing I would try is looping through the shapes in the shape
range, and apply the formatting to one shape at a time.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 4/22/2010 9:01 AM, kuhrty wrote:
Luke,

Thank you for your input, when I attempt to qualify that way, the code
does run but the shapes don't fill with the color passed. I would
have assumed it is the correct way to code it but with an unexpected
result.

Thanks again for your response.

Mark

On Apr 21, 4:37 pm, "Luke wrote:
'Does this not work?

With shtMap.Shapes(strShapeNum).ShapeRange
.Fill.ForeColor.RGB = dColor
.Fill.Visible = msoTrue
.Fill.Solid
End With

--
Best Regards,

Luke wrote in message

...



Below is a subroutine that passes in 2 variables. I am concerned with
the word "Selection" used for ShapeRange and would prefer to eliminate
it. All my attemptes have resulted in errors. I want qualify the
code to prevent error.

Is selection a keyword for ShapeRange or is there a way to qualify it?

Thanks in advance.

Public Sub CountryColor(ByVal strCountry As String, _
ByVal dColor As Double)

Dim shtMap As Worksheet, rgData As Range, strShapeNum As String
Dim i As Integer, dColor1 As Double

Set shtMap =
ThisWorkbook.Application.ThisWorkbook.Worksheets(" Map")
Set rgData =
ThisWorkbook.Application.ThisWorkbook.Worksheets(" Data").Range("RegionCount*ryData")

On Error Resume Next
shtMap.Select

For i = 1 To rgData.Rows.Count

strShapeNum = rgData.Cells(i, 3).Value

shtMap.Shapes(strShapeNum).Select

'This is my issue"
With Selection.ShapeRange
.Fill.ForeColor.RGB = dColor
.Fill.Visible = msoTrue
.Fill.Solid
End With

Next i

End Sub- Hide quoted text -

- Show quoted text -