Solved: Code for buttons

[jhofilena]

Hello everyone,

This is my first post, so I would first like to say hello to everyone. Here's what I'm trying to do...

I have created a checklist of things that someone needs to do, and what I want them to be able to do is, click on a COMPLETE button at the end of each row (task) and when they click on it, I want the row to change color. Is this possible?

Thanks,
Jen

[JKwan]

Here try this:
http://blog.livedoor.jp/andrewe/archives/50259630.html

[Bob Phillips]

I would use conditional formatting, and designate a certain column and the status indicator column. If that column contains an X, the whole row will be coloured.

Assuming the X is in column M, and your data starts in row 2

Select rows 2:n
Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of =$M2="X"
Click the Format button
Select the Pattern Tab
Select an appropriate highlighting colour
OK
OK

[jhofilena]

Doing this using the conditional formatting works great, thanks. Is it not possible using a button? I can get the button to change color when clicked on, but i really wanted to change the color of the row. To change the color of the button this is what I do:

Private Sub CommandButton1_Click()
red = Int(Rnd * 255)
CommandButton1.BackColor = red
End Sub


If anyone knows how to change the excel row when the button is clicked, that would be great. Thanks!

[Bob Phillips]

Private Sub CommandButton1_Click()
red = Int(Rnd * 255)
Activecell.Entirerow.Color = red
End Sub

[jhofilena]

I'm getting an error: Object doesn't suppor this property or method.

[JKwan]

Try this:

ActiveCell.EntireRow.Interior.Color = red

[jhofilena]

That did get a row to turn red, but not the correct row. I'm expecting row 8 to go red, but row 15 went red. If you continue to click on the button, the row goes through multiple colors. Is there a way to have it go red, and then stop?

[mdmackillop]

Do you have a button at the end of each row?

[jhofilena]

Yeah, I have a button at the end of each row. I got the row to change color finally using:

Private Sub Complete_Click()
ActiveCell.EntireRow.Interior.ColorIndex = 15
End Sub

Is there a way to specify the column where I want the color to end? For example, I only want columns C-E to change color.
How can I reset the page back to it's original state?

[jhofilena]

I think I got it.


Private Sub Complete_Click()
Range("C8:E8").Interior.ColorIndex = 15
End Sub


Thanks everyone!!!

[mdmackillop]

The following code uses the address of the cell below the top left corner of the button. You can call the same code from a number of buttons to toggle colour in the selected cells.

Private Sub CommandButton1_Click()
ChangeRow CommandButton1.TopLeftCell.Row
End Sub

Private Sub CommandButton2_Click()
ChangeRow CommandButton2.TopLeftCell.Row
End Sub

Sub ChangeRow(Rw As Long)
If Cells(Rw, 3).Interior.ColorIndex = 3 Then
Cells(Rw, 3).Resize(1, 3).Interior.ColorIndex = xlNone
Else
Cells(Rw, 3).Resize(1, 3).Interior.ColorIndex = 3
End If
End Sub

[jhofilena]

So now that I am able to change the row color, can someone help me with changing it back? Here's the scenario...When the person clicks on the "Complete" button, then the row turns grey. If they click on it again, then I want a msg box to pop asking if they want to mark that row as Incomplete. When the user clicks OK, I want the row to change back to white. Any ideas? What if the user doesn't want to mark the row as Incomplete?

[mdmackillop]

Sub ChangeRow(Rw As Long)
If Cells(Rw, 3).Interior.ColorIndex = 15 Then
If MsgBox("Do you wish to make Incomplete?", vbYesNo) = vbYes Then
Cells(Rw, 3).Resize(1, 3).Interior.ColorIndex = xlNone
End If
Else
Cells(Rw, 3).Resize(1, 3).Interior.ColorIndex = 15
End If
End Sub

[jhofilena]

This works great! THANKS SO MUCH!!!!!!

[mdmackillop]

Glad to help. You can mark your threads "solved" using the Thread Tools dropdown.
Regards
MD

[rory]

Just for interest's sake, since you already have a working solution, you could also use buttons from the Forms toolbar and assign the same macro to all of them at once:
Sub ClickHandler()
Dim Rw As Long
Rw = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
If Cells(Rw, 3).Interior.ColorIndex = 15 Then
If MsgBox("Do you wish to make Incomplete?", vbYesNo) = vbYes Then
Cells(Rw, 3).Resize(1, 3).Interior.ColorIndex = xlNone
End If
Else
Cells(Rw, 3).Resize(1, 3).Interior.ColorIndex = 15
End If
End Sub

[jhofilena]

This is great, thanks!