Solved

Conditionally format labels on Userform

Posted on 2013-10-29
8
223 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
[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
  • 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
Industry Leaders: 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!

 
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 47

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

734 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