Button colour change if data changed ?

[klong]

Hi all,

I have a command button on my spreadsheet that recalculates it. Is there an easy way to have the button change colour if the data on the sheet has changed since the last recalculation ?

Regards,

Ken

 

[Sahak]

Hi Klong,

If the data on the sheet has changed this macro will change button's color from gray (if it is gray) to green & after another change from green (if it is green) to gray and so on
I don’t know what you want to do, but I hope this macro will give you some idea

1. Copy the code
2. Right click sheet tub > View Code
3. Paste

Private Sub Worksheet_Change(ByVal Target As Range)
    If CommandButton1.BackColor = &H8000& Then
        CommandButton1.BackColor = &H8000000F
    Else
        CommandButton1.BackColor = &H8000&
    End If
End Sub

 

[klong]

Hi Sahak,

You're on the right line, but... I'm trying to get the button to change colour if I change any of the data so that I know I need to recalculate the answer - a bit like having a prompt for the F9 key !

My spreadsheet contains lots of supplier quotes for the same or multiple components. My macro on the CommandButton sorts the Savings Value (Quantity x (Price 1 - Price 2)). This makes sure the first occurrance of the part always has the best saving first. If a supplier offers a new price I change than field, but you need to remember to resort to make sure you have the best on top.

 

[Sahak]

Hi Klong,

In my opinion to find out if it is time to resort your data, you can use loop code (looping your all savings values) or you can use If statement formula for each row & get results from them in one cell.
For me not clear one thing, if you have a macro which sorts your data, why you don’t run it each time you add new supplier quote?

 

[klong]

Hi Sahak,

I do have a macro to sort the spreadsheet as there is a number of different criteria I take into account, therefore to have an accurate answer for the Savings Value you MUST sort the file otherwise you just have a nice number. The code is attached to the CommandButton.

Your previous code gets the button to toggle the colour for each change made. Ideally what I want is if one or more changes are made then the button colour changes from GREEN to GRAY. Once it's GREEN I know the sort sequence is right up to date. There are a lot of calculations on the sheet so i want to avoid having it run the code for each change.

You mention using a loop which to me suggests comparing to some other field, is this the case. If so I don't think it would work as I'm overtyping the cell that it would compare to or would be adding new rows. In fact I insert rows as I haven't figured out just yet how to code the SUBTOTAL function to allow for a variying last row.

To get the correct answer I for the moment just have to stick with running the code.

Ultimately I will be migrating it all to Access 2007 as this file is just going to keep growing. The two problems I have are that I haven't fully decided on the final content and layout, and secondly I'm only getting my head around Access now so a long way to go !

Regards,

Ken

 

[Sahak]

Hi Klong,

Sorry for not understanding & repeating my self.
You are saying that you have a macro which sorts your data.

To get the correct answer I for the moment just have to stick with running the code.

Why you don’t run this macro each time you add or enter a new supplier quote?

 

[klong]

Hi Sahak,

I do run it each time, I just thought it would be good if there was a visual trigger to tell you to run the macro !

Not to worry, I'll keep going as I am.

Regards,

Ken.