+ Reply to Thread
Results 1 to 5 of 5

Shape fill not changing when linked cell changes

  1. #1
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,389

    Shape fill not changing when linked cell changes

    SITE worksheet on attached lists 11 properties in Col C.
    Col F has DropLists where user selects which "stage" the work is on that property.

    MAP sheet has outline of the street with shapes showing the locations of each property in the street.

    Code is set to run if any cell in F5:F15 changes.
    First part should link each shape to it's matching row in the SITE worksheet, (e.g. IF you change F5 then row 5 is the Active row, so Oval 5 becomes the "active" shape).
    Second part should then change the Fill colour depending on which stage is selected in Col F on the SITE worksheet (e.g. If F5 is "Step 3" then Oval 5 should turn to Yellow. If F5 changes to "Step 10") then Oval 5 should turn to Chrome.

    Tried setting the "active" shape as "SObject" so the "fill" element only has to run once, but throws "RunTime Error 91" - Object Variable or With block variable not set.

    Please Login or Register  to view this content.
    All solutions, suggestions or alternatives welcome as ever.

    Ochimus
    Attached Files Attached Files
    Last edited by Ochimus; 09-05-2021 at 06:52 PM.

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Shape fill not changing when linked cell changes

    Hi there,


    Tried setting the "active" shape as "SObject" so the "fill" element only has to run once, but throws "RunTime Error 91" - Object Variable or With block variable not set.

    Your code tries to "assign" a value to the SObject Shape, but you need to "set" its value.


    See if the following version of your code gets things moving in the right direction:

    Please Login or Register  to view this content.
    Unintuitive as it seems, setting the ForeColor (rather than BackColor) causes the required change.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

  3. #3
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,389

    Re: Shape fill not changing when linked cell changes

    Greg,

    Definitely a star!

    Two lines replacing half a forest in my attempt, and works seamlessly!

    Many thanks and can now mark this as Solved.

    Ochimus

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Shape fill not changing when linked cell changes

    Hi again,

    Many thanks for your feedback and also for the Reputation increase - much appreciated!

    You're welcome - glad I was able to help.

    Best regards,

    Greg M

  5. #5
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Shape fill not changing when linked cell changes

    Ochimus...I know this is solved...For future reference though...As you have all the information already laid out...
    Please Login or Register  to view this content.
    Last edited by sintek; 09-06-2021 at 10:08 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Cell data changes its Specified fill shape color
    By CDanielG in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-28-2020, 01:35 PM
  2. [SOLVED] Code VBA for changing fill color of shape base on data of cells
    By TanNguyen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-07-2015, 08:39 PM
  3. VBA to tell shape to fill with image when a certain cell = 1
    By Muzza86 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-19-2014, 05:00 AM
  4. [SOLVED] Shape to match linked cell's format
    By BRISBANEBOB in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-08-2014, 08:55 PM
  5. Lock/Unlock Macro for Entire Row linked to Color-Changing Shape
    By sumkynduvstrange in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-26-2013, 10:27 AM
  6. [SOLVED] Is it possible, using VBA, to fill a shape based on a different cell value
    By sdg2013 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-31-2013, 12:53 PM
  7. shape linked to cell
    By owaring in forum Excel General
    Replies: 3
    Last Post: 07-20-2005, 04:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1