Solved

Changing the color of a command button label

Posted on 2011-03-11
7
458 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 500 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Messaging apps are amazing tools with the power to do a lot of good, but the truth is the process of collaborating with coworkers requires relationships established through meaningful communication - the kind of communication that only happens face-…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

626 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