Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Turn Button Red (OFFICE 97 SR2)

    Hi:
    Got a small question here:
    How can I have a Control Button turn red whenever the value in cell A1 is greater that the value in cell D1 (both cells within the same WS).
    Thanks
    Stephen <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Turn Button Red (OFFICE 97 SR2)

    You could trap the Sheet change event and run this code :<pre>Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A1") > Range("D1") Then
    Me.CommandButton1.BackColor = 225
    Else
    Me.CommandButton1.BackColor = -2147483633
    End If
    End Sub</pre>

    That code should be placed in the relevant worksheet object and not in a general module. Just right click on the sheet tab and select View code, and place that routine in the code window. You may need to change CommandButton1 as appropriate. You may also need to change the rather large negative number for the button color when the condition is not met.

    Andrew C

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Turn Button Red (OFFICE 97 SR2)

    instead of 255 and -2147483633 and other numbers, you could also use the slightly friendlier VB color constants

    e.g.
    <pre>vbRed=255
    vbButtonFace=-2147483633</pre>


    A full list can be found in VBA help, under Color Constants

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Turn Button Red (OFFICE 97 SR2)

    Thanks Adam,

    I knew they were there somewhere, but was just too lazy to find them

    Andrew.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Turn Button Red (OFFICE 97 SR2)

    Hi Andrew & Adam:
    This worked for me but I am having problems applying it to another situation. First, I had 2 buttons on the WS so I could use the following variation:
    If Range("A1") Range("D1") Then
    Me.CommandButton2.BackColor = 225
    Else
    Me.CommandButton2.BackColor = Me.CommandButton1.BackColor
    End If

    Now, the new problem: I have a number in cell A1 that is returned from a Counta(B:[img]/forums/images/smilies/cool.gif[/img] function. This number should never be less than 263 (it could only be less if someone or something deleted some rows from column B. If thast happens, I want CommandButton1 to turn red. I put another button on the WS because I did not know what constant to use (isn't there a way of setting the BackColor to its DEFAULT value?). So, I tried:

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Range("A1") < 263 Then
    Me.CommandButton1.BackColor = 225
    Else
    Me.CommandButton1.BackColor = Me.CommandButton2.BackColor
    End If
    End Sub

    This did not work (no message). So, I tried the following:

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim CC3_Variables As Integer
    CC3_Variables = 263
    If Range("A1") < CC3_Variables Then
    Me.CommandButton1.BackColor = 225
    Else
    Me.CommandButton1.BackColor = Me.CommandButton2.BackColor
    End If
    End Sub

    This gave me Run Time error 253 (type mismatch). I tried it using Set CC3_Variables = 263 and got the message that an "object" was required. I tried "Dim oCC3_Variables as object" but this bombed also.
    At this point, I am out of ideas and would appreciate some help.
    Thanks
    <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Turn Button Red (OFFICE 97 SR2)

    Stephen,

    A few reasons why your code might not have worked as expected.

    1. You are using the SelectionChange event, which only triggers when you select a different cell or range. The event best suited to your needs is probably Worksheet_Change, which triggers any time a change is made to any cell.

    2. Another reason might be that EnableEvents is disabled. Try typing Application.EnableEvents = True into the immediate window.

    3. Also make sure you are using a button from the Control toolbar and not the Forms toolbar.

    The following code works fine for me in XL97 :<pre>Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A1") < 263 Then
    Me.CommandButton1.BackColor = vbRed
    Else
    Me.CommandButton1.BackColor = vbButtonFace
    End If
    End Sub</pre>


    Note the vbRed and vbButtonFace in place of the values (thanks again to Adam). vbButtonFace should set the button color to the default color if the condition is not met.

    I am attaching a copy of a working sample of the above. Hope it suits.

    Andrew
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Turn Button Red (OFFICE 97 SR2)

    Hi epic60sman,

    First of all, in this case I would not use the SelectionChange event. Instead, I would use the Change event. If you use the SelectionChange event, the conditions are checked every single time the selection is changed. That means, the check will be performed a zillion times when it is not needed, in between the few times it is needed. With the Change event, the conditions are only checkd when the worksheet is changed - the check will occur less frequently, and only when it is possible for the condition to change.

    As for the "object required" error, which line does it error out on?

    Instead of using a second command button as a reference for the background color, use the following temporary code (in a standard module) to find out what the background color of a "fresh" button is:

    <pre>MsgBox Sheets("Sheet1").CommandButton2.BackColor
    </pre>


    Jot the number down, then use that in your code, instead of the reference to the command button. A quicky test here reveals that the value is -2147483633 for the standard gray.

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Turn Button Red (OFFICE 97 SR2)

    Hi again Stephen,

    I meant to also point out that you only use Set when assigning to an Object, which you seem to have guessed. However using an object would give a type mismatch error as Backcolor is expecting a Long Integer.

    Andrew

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Turn Button Red (OFFICE 97 SR2)

    Hey Jim &Andrew & Anyone Else That Read my Post:
    I made a stupid mistake. The value I wanted is in A2 and not A1. So, at this point I do not know what would have worked. But, nonetheless, I have gained some important knowledge from your comments.
    I shall return (to this), as the good general said.
    Thanks

Posting Permissions

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