UserForm Conditional Formatting

[Apr881]

Hello,

I am working on a UserForm in VBA, and I would like to have some type of indicator which is conditionally formatted based on the value of a cell in a workbook (which is derived from a formula).

For example, if the cell value in the workbook is a number > $0.00, show a green highlight, or a green check mark, green textbox, any type of indication that the formula result is valid etc.
if the cell value is either N/A (error) or $0.00, then show a red highlight, or red x, or red textbox, etc.

I've been experimenting with textboxes, labels, and changing the actual userform background color (including initialize, change, click events), and it never seems to work. The formula will start off with a value of $0.00 or N/A, and then as the user changes the combo boxes, the formula will change, and should end with a >$0.00 value (unless the user selects in error). The purpose of this is to let the user know they cannot continue until they select valid combinations from all of the combo boxes on the userform (sorry if this is confusing).

Any suggestions on a path which might achieve my goal?

Thank you in advance!

 

[Dan_W]

It's a little difficult to visualise what difficulty you're experiencing. Do you have some (failed) code you can share and/or a dataset and an example of the type of result you would like to see? It's certainly an interesting idea you've got.

Separately, have you tried using the Userform Activate event? This follows the the UserForm initiailize event, assuming that the userforms ShowModal property is set to True.

 

[Apr881]

It's a little difficult to visualise what difficulty you're experiencing. Do you have some (failed) code you can share and/or a dataset and an example of the type of result you would like to see? It's certainly an interesting idea you've got.

Separately, have you tried using the Userform Activate event? This follows the the UserForm initiailize event, assuming that the userforms ShowModal property is set to True.

Thank you for your response!

Below is an image of the general format of the UserForm I am working with. The boxes on the left are labels (which I removed text for security purposes). To the right, are the combo boxes the user interacts with. The text box I wish to format (and display a msg validating selections or informing the user of invalid selections), is beneath the last combo box.

Once the user completes the combo boxes, I want the text box, or the background of the entire form, or green light / red light icon sets, to let them know they may proceed, and at that point, they click one of the buttons at the bottom of the form, to transfer the combo box data, into an excel spreadsheet. The data behind these combo boxes is a bit complex - they reference multiple sheets, which have multiple combinations, and unfortunately, they allow for incorrect selections (this has been this way before my time - it would be too labor intensive to correct this (which would solve my problem ultimately ha).

I have seen logic / code that can format the background color of the combo boxes, and also the background of the entire form, however, to further complicate, the user does not necessarily need to utilize all combo boxes to get a valid result - it depends on what option they are wanting.

 

[Apr881]

It's a little difficult to visualise what difficulty you're experiencing. Do you have some (failed) code you can share and/or a dataset and an example of the type of result you would like to see? It's certainly an interesting idea you've got.

Separately, have you tried using the Userform Activate event? This follows the the UserForm initiailize event, assuming that the userforms ShowModal property is set to True.

I have not tried the Activate event, however, I do have the User Form set to Modeless.