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
Solved

Changing the color of a command button label

Posted on 2011-03-11
7
454 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
  • 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel if formula 2 19
Excel formula to show $0.00 if negative number. 7 53
Combine like values in a graph excel 2016 7 31
excel table with over 25000 rows. 3 29
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Let Bitmoji into your life. Now is the time to learn a new language of smartphone messaging with this brief introduction.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

840 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