Shape Range
in [Charts]
|
From: kuhrty on 21 Apr 2010 15:23 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
From: Luke M on 21 Apr 2010 16:37 '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" <mark.kuhrt(a)gmail.com> wrote in message news:17973c9e-8206-4d82-9ce2-6a36913e48bf(a)k41g2000yqf.googlegroups.com... > 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
From: kuhrty on 22 Apr 2010 09:01 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" <lukemor...(a)nospam.com> 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" <mark.ku...(a)gmail.com> wrote in message > > news:17973c9e-8206-4d82-9ce2-6a36913e48bf(a)k41g2000yqf.googlegroups.com... > > > > > 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- Hide quoted text - > > - Show quoted text -
From: Jon Peltier on 22 Apr 2010 12:12 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 M"<lukemor...(a)nospam.com> 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"<mark.ku...(a)gmail.com> wrote in message >> >> news:17973c9e-8206-4d82-9ce2-6a36913e48bf(a)k41g2000yqf.googlegroups.com... >> >> >> >>> 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 -
|
Pages: 1 Prev: Offset Horizontal axis data Next: Chart background colors by value |