Create Excel shape from MapInfo *OR* make picture background transparent?

[caringsharingbristolbilly]

Hi all.

Further to an original thread (http://www.mrexcel.com/forum/showthread.php?t=335897), I have a question regarding shapes and images in Excel.

Basically, I'm trying to make a map of 14 regions where each region changes brightness depending on values in the worksheets. I think I've got this bit sorted. I'm planning on creating the map using a collection of tessellated shapes or images, and using VBA to alter the properties of each one to create a dynamic map.

The output of the individual maps from MapInfo, our GIS software, is in .png or .jpg. format, and come with a white background. Is there any way to either make the background transparent so multiple shapes can be tessellated effectively, or a way to crop the image to the area's border?

Either that, or does someone know a way to output from MapInfo in such a way to make this possible? (Granted, it's a long shot, but you can't blame a guy for trying...)

Any help would be immensely appreciated. Sorry if I've not explained it well, but hopefully you'll get the drift.

Thanks,
CSBBB.

 

[Gunswick]

i just noticed in Paint, there is the option Draw Opaque.

A search of the help menu reported this:





To specify opaque or transparent drawing

• To draw an opaque image, on the Image menu, click Draw Opaque and make sure a check mark appears next to it. Selecting Opaque specifies that the existing picture will be covered by the white background of a selected area in Paint.
  
  
  For example, in the image at the left, notice that the small dotted box includes a white background around the green box. Also notice that the white background covers the larger image behind it.
• To draw a transparent image, on the Image menu, click Draw Opaque and make sure the check mark does not appear next to it. Turning off Opaque specifies that the underlying pictures will be visible through the background of a selected area in Paint.
  
  
  For example, in the image at the left, notice that the small dotted box is clear and the green box displays inside it. Also notice that the content of the image behind it is visible right up to the green box.

The 2 pictures you cannot see describe the scenario you need. Search the help file in paint for "draw opaque" and you can see / read it You will want option 2, make sure draw opaque is UNCHECKED. Have a play with it lol


EDIT:

OOOHHH I DID IT!

I opened paint. Unchecked Draw Opaque.
Then drew a blob.

Then just picked the square crop tool, and selected a large area around the blob.

Copy + paste normally into excel.

the picture toolbar popped up.

i clicked on "set transparent colour" on the right hand side of the picture toolbar.

then i clicked on the picture, or the white background of the spreadsheet. I got excited and forgot :D

and the background white vanished to so i can see things behind the blob :D


EDIT2:

You dont even need to this that draw opaque crap, just draw a blob, copy paste to xl.
picture toolbar, click set transparent colour. click on the white of the excel and then move the blob over something and you can see right through it :D

 

[LraeSinned]

Gimp, Picasa, Paint.Net are all free and should let you edit the image files.

 

[caringsharingbristolbilly]

EDIT2:

You dont even need to this that draw opaque crap, just draw a blob, copy paste to xl.
picture toolbar, click set transparent colour. click on the white of the excel and then move the blob over something and you can see right through it :D

Oh. My. God. It was that simple all along!!! Thanks so much. I've just started using Excel 2007, and they've hidden everything. In case anyone's reading this in the future, in Excel 2007, you need to paste the image in, click on it. Then bring up the "Picture Tools > Format" context menu in the ribbon and select "Recolor" (sic*), then "Set Transparent Color" (sic*), and you're good to go.

Many, many thanks for everyone's help, especially Gunswick for the solution. ray: It's been really useful and I've learned something new, which is exactly what this forum is for.

Take care!

CSBBB


* Most Annoying Things About Excel 2007 #68: Lack of any localization (sic) for the UK market.

 

[Gunswick]

great, im glad its sorted

could you post up your final macro when its written as im interested in seeing it. That smiley face that changed colour was cool (the ozgrid thread you linked to)

 

[caringsharingbristolbilly]

great, im glad its sorted

could you post up your final macro when its written as im interested in seeing it. That smiley face that changed colour was cool (the ozgrid thread you linked to)

Sure, will do. It's likely to be (a) in a few days and (b) very basic and probably quite clumsy, but I'm sure people will be able to streamline it if they need to...

 

[arkusM]

* Most Annoying Things About Excel 2007 #68: Lack of any localization (sic) for the UK market.

And the Canadian market.... But it is MS and what do they care? LOL

 

[Gunswick]

any update on this? Im intersted in seeing the final result.

a workbook with dummy figures and any sensitive data removed and replaced with jibberish would be awesome, as then I might be able to use the map regions etc...

 

[caringsharingbristolbilly]

Hi Gunswick.

Thanks for the nudge! I'd not forgotten, I'd just got so carried away with the project I'm on, this slipped down the list a little...

I can't install that HTML table maker app at work, so I can't really post it here. What I'll do, though, is post the code, describe what it does and send the sheet to anyone who wants it if they PM me an e-mail address, how's that?

Anyway, here's the dead-simple-thanks-to-everyone-here's-help code:

Sub TestNEW()
    With ActiveSheet
        .Shapes("BAPPIC").PictureFormat.Brightness = .Range("BAPQ").Value
        .Shapes("BANESPIC").PictureFormat.Brightness = .Range("BANESQ").Value
        .Shapes("BRISPIC").PictureFormat.Brightness = .Range("BRISQ").Value
        .Shapes("CORNPIC").PictureFormat.Brightness = .Range("CORNQ").Value
        .Shapes("DEVPIC").PictureFormat.Brightness = .Range("DEVQ").Value
        .Shapes("DORPIC").PictureFormat.Brightness = .Range("DORQ").Value
        .Shapes("GLOSPIC").PictureFormat.Brightness = .Range("GLOSQ").Value
        .Shapes("NSOMPIC").PictureFormat.Brightness = .Range("NSOMQ").Value
        .Shapes("PLYPIC").PictureFormat.Brightness = .Range("PLYQ").Value
        .Shapes("SOMPIC").PictureFormat.Brightness = .Range("SOMQ").Value
        .Shapes("SGLOSPIC").PictureFormat.Brightness = .Range("SGLOSQ").Value
        .Shapes("SWINPIC").PictureFormat.Brightness = .Range("SWINQ").Value
        .Shapes("TORBPIC").PictureFormat.Brightness = .Range("TORBQ").Value
        .Shapes("WILTPIC").PictureFormat.Brightness = .Range("WILTQ").Value
    End With
End Sub

In this case, the map is made up of simple shapes which were generated from our maps (named with the "(region)PIC" convention), and the values are quartiles for the data (named with the "(region)Q" convention).

The shapes are a mid-blue shade, so that I can vary the brightness to get a clear variation in the regions.

The quartile values (e.g. "BAPQ") are between 0.5 and 1. These values can ONLY be between 0 and 1, as they are essentially percentages that the code uses to define how bright or dark it makes the image. I calculated these from my original data by ranking the original values, then using that to make the top three 0.5, the next three 0.6, and so on.

I hope this makes it reasonably clear. If you have any questions, please let me know, but most importantly if you'd like to see a copy, PM me your e-mail address.

Alternatively, if anyone knows any way I could post this on here that doesn't require installing anything, I'd be happy to oblige.

Thanks,
CSBBB.

 

[Gunswick]

PM sent

 

[Gunswick]

Thanks for the book.

it looks good, though sadly the macro errors on the first line:

.Shapes("BAPPIC").PictureFormat.Brightness = .Range("BAPQ").Value

error message:
run-time error 5
invalid procedure call or argument

so im assuming that this code needs tweaked for excel 2003