Simple geographical map shaded from data? Suggestions welcome!

caringsharingbristolbilly

caringsharingbristolbilly

Board Regular
Joined
Mar 23, 2004
Messages
241
Hi all.

I'm doing a piece of work which shows performance of geographic areas in my region on a number of indicators. I'd like to do a simple map for each of the indicators, whereby each region is shaded by according to its performance (by quintile or quartile, ideally).

There are 14 areas in our region, and the way I thought I could do it was to paste in 14 simple shaded shapes (in, say, blue), placed together so the form the map of the region, and have each one linked to a bit of VBA code to alter the brightness of each one according to a particular value.

Would this be the way you would do it? Or would you suggest a simpler way? And if so, could someone just briefly give me an idea of the VBA command for changing the brightness of a picture object? I've had a look round and an experiment, but I'm a bit of an Excel VBA dunce, and haven't managed to make it work...

Any suggestions would be hugely appreciated, as always. :)

Many thanks!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
This will set the brightness of a picture named "Picture 1" on the active sheet to 50%:

Code: 
Sub Test()
    With ActiveSheet.Shapes("Picture 1")
        .PictureFormat.Brightness = 0.5
    End With
End Sub

The PictureFormat object also has a Contrast property.
 
Upvote 0
No

The one I was thinking of is called FilterMap, you can click on a state map and it will return, in this example, the doctors located within that state.

If I find anything else I will post back

Cheers
 
Upvote 0
That's great, thanks very much both of you for your help..!

Andrew - I think that method will work in conjunction with an example I found here http://www.ozgrid.com/forum/showthread.php?t=47639, so I think I should be good to go with that. Which then brings me onto another question, which is a little off topic, so I'll post a new thread for that.

In the meantime, if anyone has any further suggestions, let me know!

Thanks again... :)
 
Upvote 0
Andrew Poulsom said:
This will set the brightness of a picture named "Picture 1" on the active sheet to 50%:

Code: 
Sub Test()
    With ActiveSheet.Shapes("Picture 1")
        .PictureFormat.Brightness = 0.5
    End With
End Sub

The PictureFormat object also has a Contrast property.
Click to expand...

Hi again all.

Andrew, in the code you kindly posted, if I wanted to replace that 0.5 value with the value in a cell, how would I do that?

I promise I'll go on a VBA course soon, but just in the meantime, this would help to at least do a proof of concept on a spreadsheet I'm trying to develop.

Any help would be hugely appreciated again as always. :)

CSBBB
 
Upvote 0
Like this?

Code: 
Sub Test()
    With ActiveSheet
        .Shapes("Picture 1").PictureFormat.Brightness = .Range("A1").Value
    End With
End Sub
 
Upvote 0
Andrew Poulsom said:
Like this?

Code: 
Sub Test()
    With ActiveSheet
        .Shapes("Picture 1").PictureFormat.Brightness = .Range("A1").Value
    End With
End Sub
Click to expand...

Thanks very much, that works a treat. :)

Actually, I probably had that code originally at some point while I was trialling and erroring, but I was being an idiot and put the wrong picture name in the code!

Thanks for all your help on this, it's helped me to develop a simple but very, very useful mapping tool for presenting simple data, which (I hope) will be of use to many people in our office. I really appreciate it! :pray:
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
2-step Dynamic Ranges and Charts
Replies
0
Views
135
Nick Loughton
N
M
  • Question
Google MAP URL Function
Replies
1
Views
732
McNab
M
K
  • Question
Efficient ways to present Data using image map
Replies
0
Views
507
Kemidan2014
K
A
  • Solved
The best way to enter, display & edit data in worksheet.
Replies
1
Views
364
AndyEd
A
A
  • Question
Show Data Validation Lists in Next Row of Table
Replies
0
Views
399
AndyPandy27
A

Forum statistics

Threads
1,214,622
Messages
6,120,585
Members
448,972
Latest member
Shantanu2024

Share this page

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
Back
Top