Changing the color of a command button label

Experts,
In my primary spreadsheet I have a form commandf button named Recon5010 with a text labeled Recon. The color of the text label is black.
Here's my objective:

If the value 'Wgs Whse Wkr 5015.100'!L35<>0 (a different spreadsheet) I'd like to change the label color to red for Recon5010 from black, indicating I have a reconciliation problem in spreadsheet Wgs Whse Wkr 5015.100
Frank FreeseAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
Yes - it would be:
Private Sub Worksheet_Calculate()
Dim lngColour as Long
 If Range("L35").Value <> 0 Then
lngcolour = 3
Else
lngcolour = 1
End If
Sheets("Month Total").Buttons("Recon5015100").Font.ColorIndex = lngcolour
End Sub

Open in new window

0
 
StephenJRCommented:
This perhaps?
If Sheets("Wgs Whse Wkr 5015.100").Range("L35").Value <> 0 Then
    ActiveSheet.Shapes("Recon5010").TextFrame.Characters.Font.ColorIndex = 3
Else
    ActiveSheet.Shapes("Recon5010").TextFrame.Characters.Font.ColorIndex = 0
End If

Open in new window

0
 
Rory ArchibaldCommented:
Is that sheet in the same file?
You can either put a formula on the sheet with the button and use the sheet's Calculate event to colour the text, or use the calculate event of the other sheet.
Private Sub Worksheet_Calculate()
   If Sheets("Wgs Whse Wkr 5015.100").range("L35").Value <> 0 Then 
Me.Buttons("Button 2").Font.ColorIndex = 3
Else
Me.Buttons("Button 2").Font.ColorIndex = 1
end if
End Sub

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Rory ArchibaldCommented:
Obviously replace Button 2 with Recon5010. :)
0
 
Frank FreeseAuthor Commented:
I tried putting the above code in the with the sheets Calculate event as follows:

Private Sub Worksheet_Calculate()
 If Sheets("Wgs Whse Wkr 5015.100").Range("L35").Value <> 0 Then
Me.Buttons("Recon5015100").Font.ColorIndex = 3
Else
Me.Buttons("Recon5015100").Font.ColorIndex = 1
End If
End Sub

And got an error and I think I know why. Me.Buttons("Recon5015100") exits on one spreadsheet and I'm trying to change the color on another spreadsheet called  "Month Total". It looks like I would need to reference back to the "Month Total" spreadsheet but I don't know how to do that?
Also, since I have my code on the in the first spreadsheet ("Wgs Whse Wkr 5015.100") can I not change my If statement to something like this:

If L35<> 0 Then
.
.
.
.
End If
0
 
Frank FreeseAuthor Commented:
I tried this but it failed:
Private Sub Worksheet_Calculate()
   If Range("L35") <> 0 Then
   Sheets("Month Total").Me.Buttons("Recon5015100").Font.ColorIndex = 3
   Else
      Sheets("Month Total").Me.Buttons("Recon5015100").Font.ColorIndex = 1
End If
End Sub
0
 
Frank FreeseAuthor Commented:
there you go - great job...perfect and thank you again
0
All Courses

From novice to tech pro — start learning today.