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

Conditionally format labels on Userform

Posted on 2013-10-29
8
211 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
ID: 39610011
Upload a worksheet showing the issue.
0
 

Author Comment

by:faraoosiris
ID: 39611228
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
ID: 39620061
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 30

Expert Comment

by:hnasr
ID: 39620165
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
ID: 39625515
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
ID: 39650477
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 46

Expert Comment

by:Martin Liss
ID: 40396490
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

839 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