• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 471
  • Last Modified:

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
0
Frank Freese
Asked:
Frank Freese
  • 3
  • 3
1 Solution
 
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
 
Rory ArchibaldCommented:
Obviously replace Button 2 with Recon5010. :)
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
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
 
Rory ArchibaldCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now