Making Buttons to change colors and text

Microsoft Excel 2003 (full product)
December 30, 2009 at 12:56:55
Specs: Windows Vista, n/a
I'm just trying to make a spreadsheet to where there is a button that the user can click that will change a cell to Completed instead of Pending to mark daily progress in a list of tasks. then following the dayend, there will be a reset button to clear all the boxes back to pending... Is there anyway that I could get a quick tutorial?

Thank you for your help!

See More: Making Buttons to change colors and text

Report •

December 30, 2009 at 13:48:55

There are several ways to do something like this.

This solution uses a combination of conditional formating to color the cells containing the task names, and a command button embedded in the worksheet to run a short piece of code to reset the statuses at the end of day.

Setup cells A1 to B5 as follows:

	A	B
1	Task	Status
2	Task 1	
3	Task 2	
4	Task 3	
5	Task 4	

Select cells A2 to A5, right click and select Format Cells.
In the Pattern Tab select a red color. Click OK.

Each of the cells B2 to B5 can be empty or contain either "Pending" or "Completed"
To ensure that only Pending or Completed are entered exactly as shown, use Data entry Validation.

Create a 'list' in cells H2 to H3
Enter Pending in H2 and Completed in H3.

Select Cells B2 to B5.
From the Menu select Data - Validation...
In the Settings Tab select List from the 'Allow:' drop-down.
In the 'Source:' box enter =$H$2:$H$3
Click OK

Clicking any of the cells B2 to B5 will result in a drop down containing only Pending and Completed. Users can choose only one or the other (or hit Delete to clear the cell).

Now create conditional formating in cells A2 to A5 so that the background color of the cells changes depending on what is in the adjacent cell in column B
Select cells A2 to A5
From the Menu select Format - Conditional Formatting...
Under Condition 1, select Formula Is from the drop-down.
In the formula box enter =IF(B2=$H$2,TRUE,FALSE)
Click the Format button, and from the Patterns Tab select a yellow color.
Click OK
Back in the Conditional formatting box click Add.
Repeat the process for Condition 2
Select Formula Is
Enter formula =IF(B2=$H$3,TRUE,FALSE)
Click format button and select a green color, click OK
Click OK to close the conditional formatting box.

The final step is to create a button to clear all the entered statuses.
From the Control Toolbox toolbar (you may have to show this toolbar - right click on existing toolbar and check Controls toolbox)
Select the command button icon
Draw a command button.
Right-click the button and select View code (also right click to Edit the object to change the name on the button to Reset).
In the window that opens enter this code:

Private Sub CommandButton1_Click()
'resets all statuses to empty
'all task cells will revert to base background pattern/color
Dim rngStatuses As Range
Dim rngCell As Range

'set range of cells that contain statuses for each task
Set rngStatuses = ActiveSheet.Range("B2:B5")
For Each rngCell In rngStatuses
    rngCell.Value = ""
Next rngCell
End Sub

Hit Alt +f11 to return to main Excel spreadsheet.
Exit 'design mode' (first icon on controls toolbox bar)

Now change statuses by selecting Pending or Completed from cells in Column B
Click the Reset button and all statuses return to empty.

Lots of options for minor changes here. The reset code can make all cells in column B = "Pending" by changing this line:
rngCell.Value = "Pending"
Also the base color of the cells can be left with no color.
Another option is to move the reset code from a button to the Open event so that whenever the workbook is opened, it resets all the statuses.
You could save a date in a cell on the worksheet, and make the Open event code only reset the statuses if the date 'now' is later than the saved date.

If you want to try one of these ideas, please ask.


Report •
Related Solutions

Ask Question