How to include an added shape into macro

JasmineL

Active Member
Joined
Jan 7, 2003
Messages
299
I'm using a US Colour Map tool Andy Pope created (I sent him a PM as well). It's a cool gadget for coloring individual states based on criteria entered into a table, e.g., enter 1 next to the state name Alaska, run macro, Alaska colored red.

I had to add Puerto Rico to the map. I've included PR in the States Named Range and have hyperlinked the shape to its respective worksheet in the file. When I try to test the coloring, I receive the following error: With .Shapes(strStateName)

I usually can adapt the VBA others have created to meet my needs. However, I'm not familiar with looking at some of the code and knowing what it's telling me and how to fix it.

Here are my steps:
-I've hand drawn the shape of PR onto the MainMap worksheet.
-I've hyperlinked the shape (like the other states) to a newly created worksheet titled "Puerto Rico."
- I've added an AutoTip so that when the user rolls over PR, the words Puerto Rico appear.
- I've inserted Puerto Rico below Pennsylvania on the Control worksheet.
- I've updated the named range States to include all of the states.

Here's the code being used to "recolor" the map:

Code:
'
' Written by Andy Pope © 2003
'
Option Explicit

Sub ColourStates()
'
' Using the values from named range STATE
' And the colours from named range STATE_COLOURS
' re colour the map on sheet MainMap
'
    Dim intState As Integer
    Dim strStateName As String
    Dim intStateValue As Integer
    Dim intColourLookup As Integer
    Dim rngStates As Range
    Dim rngColours As Range
    
    Set rngStates = Range(ThisWorkbook.Names("STATES").RefersTo)
    Set rngColours = Range(ThisWorkbook.Names("STATE_COLOURS").RefersTo)
    
    With Worksheets("MainMap")
        For intState = 1 To rngStates.Rows.Count
            strStateName = rngStates.Cells(intState, 1).Text
            intStateValue = rngStates.Cells(intState, 2).Value
            If intStateValue > 9 Then
                ' stripped
                With .Shapes(strStateName)
                    intColourLookup = Application.WorksheetFunction.Match(CInt(Left(CStr(intStateValue), 1)), Range("STATE_COLOURS"), True)
                    .Fill.Patterned msoPatternWideUpwardDiagonal
                    .Fill.ForeColor.RGB = rngColours.Cells(intColourLookup, 1).Offset(0, 1).Interior.Color
                    intColourLookup = Application.WorksheetFunction.Match(CInt(Right(CStr(intStateValue), 1)), Range("STATE_COLOURS"), True)
                    .Fill.BackColor.RGB = rngColours.Cells(intColourLookup, 1).Offset(0, 1).Interior.Color
                End With
            Else
                ' single colour
                intColourLookup = Application.WorksheetFunction.Match(intStateValue, Range("STATE_COLOURS"), True)
                With .Shapes(strStateName)
                    .Fill.Solid
                    .Fill.ForeColor.RGB = rngColours.Cells(intColourLookup, 1).Offset(0, 1).Interior.Color
                End With
            End If
        Next
    End With

End Sub

Can anyone help me figure out how to correct this? Thank you so much!!

Have a great day!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

JasmineL

Active Member
Joined
Jan 7, 2003
Messages
299
Andy Pope is a rock star!!! The step I missed was naming my new shape for Puerto Rico...duh!!! Thanks everyone for reading my post!
 

DM2

New Member
Joined
Apr 24, 2011
Messages
4
I'm trying to do something similar, but how did you name the shape
 

Watch MrExcel Video

Forum statistics

Threads
1,123,320
Messages
5,600,946
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top