Solved

Conditionally format labels on Userform

Posted on 2013-10-29
8
202 Views
Last Modified: 2014-10-22
I made a UserForm with two textboxes and two labels. Textbox1 is linked to a worksheet and the input from Textbox1 is input in a worksheet, the Lookup is done on the worksheet. A cell on the worksheet has the answer and is linked to Textbox2 and once the label CALC has been pushed appears in Textbox2. The other label on the UserForm is to exit the form.

On the form I made a lot of labels to simulate a paper table the user is used to. The labels have a with statement. On initialize of the form the labels get their Forecolor and contents from the worksheet. Furthermore the labels get centered horizontally and vertically on a kind of background label. All this is working.

The one thing I'am trying to accomplish is to conditionally change the forecolor of the labels from a , hardly readable green, to a bright green. The reason behind this so the user can see where the outcome is derived from in the table. Furthermore it has to change back once another calculation is being made.

My first thought was to append some kind of code to the CALC label and compare the outcome already present in Textbox2 to the Caption of the labels and change the labels caption, i.e forecolor. Can this be done? Compare a Caption to a Textbox value and then change the forecolor by means of code.
0
Comment
Question by:faraoosiris
  • 4
  • 2
8 Comments
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Upload a worksheet showing the issue.
0
 

Author Comment

by:faraoosiris
Comment Utility
Open the Userform frm300ER from sheet1(blad1) bij clicking the button in cell C6.
This will open a new useform and you have to choose B777-300ER and push start.

This will open the Userform frm300ER I talked about. Fill in a value between 16.4 and 29 at the MACZFW and  push CALC in the upper right corner.

The form frm300ER can also be found by alt-F11 and choose frm300ER. By hitting F5 you will go the form layout.

It all is in a design state so codes and vba look not that professional. The 2 command buttons are there to zoom in and out. This because I design the form on a smaller resolution screen then the user has.
lookup5.xlsm
0
 

Author Comment

by:faraoosiris
Comment Utility
Is there a way by means of an Array to fill labels so they are consistently renamed and still make sense to me as to the content of the label.

I found this code

For i = 1 To 2
    With Sheets("blad5")
        If .Cells(i, "A").Value = "NO DATA" Then _
          Me.Controls("lbl" & i & "Af").BackColor = RGB(200, 200, 200)
        Me.Controls("lbl" & i & "Af").Caption = .Cells(i, "A").Value
    End With
Next i

and adjusted it to lbl instead of Label1, Label2... and put Af to it. As it turns out I can now
fill lbl1Af lbl2Af but how about lbl1Af, lbl1Bf, lbl1Cf upto lbl4Df. This way the labels would represent the cells the data is taken from.

Any suggestions?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Sorry! I spend more time to understand the design.
To help in this tell what to do step by step.
From opening workbook, to entering data, and tell what you see and what to expect.

Manually color the labels or text boxes you want to work with.

This may invite other people to contribute.

Upload the final state of the workbook.
0
 

Author Comment

by:faraoosiris
Comment Utility
As the excel program opens on the first worksheet there is a button command1 situated on cell "C6".

This command button opens a Userform.
On this form, for now, the user can choose via a radio button. I'am currently only developing the B777-300 UserForm. The difference between the two forms will eventually be just different values.

As the frm300ER opens, the user can give an input in textbox1. The lookup of the value to be returned is done on Blad4. The value is displayed in Textbox2 after pushing the CALC label in the righthand corner.

The input and the returned value are displayed on the righthand side on the green background and in white font. This will eventually be the only outcome being displayed. The Textbox2 I will hide.

This all works fine. The only thing I want to do now is to highlight the found value's.
For example 26.3 in Textbox1 will return 0.3 in Textbox2 and underneat each other on the green background.
In the center of the form you see  25.6-26.5 and to the right of it 0.3. This 0.3 is the answer and the value 26.3 is in the bracket of 25.6-26.5.

I want to hightlight 25.6-26.5 and 0.3 and the header of the colum MACZFW(%) and
PERFCORR(%).

Regards
0
 

Accepted Solution

by:
faraoosiris earned 0 total points
Comment Utility
I solved the problem by using the camera tool instead of filling the label's caption.

The camera takes a real time "picture" of the worksheet including the conditional formats.
I did use some helper colums to "highlight" the colum headers. I used an IF statement to determine if the values were TRUE or FALSE , equal to the value input in the worksheet, and used the SUM function of the colums the "highlight" the colums.

The other helper cell is to read a 0 or 1 from the UserForm. The on the mousedown event of TextBox1 generates a triggger to "repaint" the picture in the Image frame. I also use it at startup of the form to clear the form of it conditional formats.
Demo.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Outlook Free & Paid Tools
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now