Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

Conditionally format labels on Userform

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
faraoosiris
Asked:
faraoosiris
  • 4
  • 2
1 Solution
 
hnasrCommented:
Upload a worksheet showing the issue.
0
 
faraoosirisAuthor Commented:
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
 
faraoosirisAuthor Commented:
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
hnasrCommented:
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
 
faraoosirisAuthor Commented:
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
 
faraoosirisAuthor Commented:
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
 
Martin LissRetired ProgrammerCommented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now