• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Transparent picture based on cell value

carlcmat

New Member
I have some pictures in a Excel sheet.
I want the pictures shall be transparent in some degree based on a cell value.

Ex.: If a specific cell value is 0 the picture transparency shall be 0 %, and so on...

I guess this is a VBA job. I've never worked with VBA. Can anyone please tell me exactly what to do?
 

Luke M

Excel Ninja
Hi calcman, and welcome to the forum!

Check out the attached file. To see the code, right-click on the sheet tab, view code.
upload_2016-2-26_10-22-1.png

Code is currently set to change all shapes/pictures based on value in cell A1 of the sheet.
 

Attachments

Luke M

Excel Ninja
Sure thing, it just takes more coding.
In the code, you should see the block of code like this
Code:
'Loop over all shapes in sheet
For Each sh In Me.Shapes
    sh.Fill.Transparency = myCell.Value
Next sh
If you know which shape you want, then you could instead just do something like this:
Code:
'Change single shape
    Shapes("Name of Shape").Fill.Transparency = Range("A1").Value
If you get stuck, it would help to have an example of your layout, showing which cell(s) need to control which shape(s).
 

mogabia

New Member
Sure thing, it just takes more coding.
In the code, you should see the block of code like this
Code:
'Loop over all shapes in sheet
For Each sh In Me.Shapes
    sh.Fill.Transparency = myCell.Value
Next sh
If you know which shape you want, then you could instead just do something like this:
Code:
'Change single shape
    Shapes("Name of Shape").Fill.Transparency = Range("A1").Value
If you get stuck, it would help to have an example of your layout, showing which cell(s) need to control which shape(s).
Hi Luke M,
I just found this on the forum and it is super useful, but I wanted to see whether you could help me with an issue I'm having. The change in transparency changes to a shape only if you enter a value in A1 yourself, is there anything I can do to make the transparency change via a formula. E.g. "A1"=IF(A2=1,1,0).
Many thanks!
 
Top