?
Solved

Changing the color of a command button label

Posted on 2011-03-11
7
Medium Priority
?
461 Views
Last Modified: 2012-08-14
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
Comment
Question by:Frank Freese
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 35109408
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35109422
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35109432
Obviously replace Button 2 with Recon5010. :)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Frank Freese
ID: 35109786
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35109821
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
 

Author Comment

by:Frank Freese
ID: 35109839
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
 

Author Closing Comment

by:Frank Freese
ID: 35109856
there you go - great job...perfect and thank you again
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question