1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Checkboxes

Discussion in 'Business Applications' started by MacShand, Dec 23, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. MacShand

    MacShand Thread Starter

    Joined:
    Oct 13, 2009
    Messages:
    11
    Hello

    I'm trying to get a checkbox to apply to two rectangles so that when checked, they will be green, and unchecked will be red.

    I've got the code for that sorted, the only issue is this:

    eg.

    * Rectangle 1 is in Cell A2; Rectangle 2 is in Cell A3.
    CheckBox 1 applies to BOTH of these rectangles to change their colour depending on its status.

    However, if I select the whole row to reorder them, (eg, row two is moved down to become row 10), the whole process goes out of kilter and CheckBox 1 no longer changes the colours of Rectangles 1 & 2.

    Sorry it's so long winded. Look forward to some help!

    Thanks.


    M
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    "Rectangle 1 is in Cell A2; Rectangle 2 is in Cell A3."

    No, they only look like they are. Shapes can't be in cells, they can only hover over -- since they exist in a "separate ?layer? of the object model" (or something like that :eek: ).

    Anyway, I positioned 2 Drawing toolbar rectangles over A2 & A3 respectively. I added a Forms toolbar checkbox to the sheet. I named G1 "Link", then set the checkbox cell link to "Link".

    Then I assigned this code to the checkbox:

    Sub test()
    If Range("Link") = True Then
    ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor.SchemeColor = 11
    ActiveSheet.Shapes("Rectangle 2").Fill.ForeColor.SchemeColor = 11
    Else
    ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor.SchemeColor = 10
    ActiveSheet.Shapes("Rectangle 2").Fill.ForeColor.SchemeColor = 10
    End If
    End Sub


    It worked. And still worked after adding some rows at the top. HTH :)
     
  3. MacShand

    MacShand Thread Starter

    Joined:
    Oct 13, 2009
    Messages:
    11
    Thanks very much. Works very Well. Much appreciated!!


    Is there a way that the CheckBox will also move with the cells if you cut and paste the whole row/



    M
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    No built-in way for cut & paste, AFAI can see.

    If you copy the row further down the checkbox goes with, then the original row's up for deletion.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/887782

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice