Button colour change if data changed ?

klong

New Member
Joined
Oct 23, 2005
Messages
44
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Sahak

Well-known Member
Joined
Nov 10, 2006
Messages
884
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

Code:
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

New Member
Joined
Oct 23, 2005
Messages
44
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

Well-known Member
Joined
Nov 10, 2006
Messages
884
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

New Member
Joined
Oct 23, 2005
Messages
44

ADVERTISEMENT

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

Well-known Member
Joined
Nov 10, 2006
Messages
884
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

New Member
Joined
Oct 23, 2005
Messages
44
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,042
Messages
5,599,469
Members
414,312
Latest member
mikefire911

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top