Change UserForm background color when a date is filled in

[ProfXer]

Greetings all!

I have a UserForm named DataUF that I want to change the background color on when a date is entered into a Date Picker.

Right now the Date Picker is just the basic code. Checkboxes are enabled in it ("True") so if the checkbox is unchecked no color change should happen or change back to the normal color. But when it is checked they may choose a date and that date should change the background color of the UserForm DataUF. The Date Picker is named DTPDateFLWRrcvd.

Please Login or Register  to view this content.

I hope I have enough detail above for you to help. Please let me know if you need more. Thanks for the help.

[Logit]

.

Paste in the UserForm :

Please Login or Register  to view this content.

[ProfXer]

Thanks for the quick response, Logit. Unfortunately, I cannot get it to work. Perhaps I mislead you in focusing on the checkbox in the Date Picker. The checkbox of the Date Picker only toggles the ability of showing a date or not showing a date. So, I'm not sure if a click is functional on Date Pickers. I wonder if it would be functional to have the code focus on the value of the Date Picker either being "" or not "".

Below is the code as I adapted it from yours.

Please Login or Register  to view this content.

What do you think?

[ProfXer]

I know I have obviously done something wrong. Below is the code that involves the UserForm.

Please Login or Register  to view this content.

When I try to run and update a record with this code, it pops an error at the first line of code I have copied for you. Perhaps I have it in the wrong place. The error is:

"Compile error:
Only comments may appear after End Sub, End Function, or End Property"

I value your input. Thanks.

[Logit]

.
I cannot attest to the accuracy of your macro code. It is presumed to be correct.

The overall layout of the macros should be like this :

Please Login or Register  to view this content.

[ProfXer]

Logit, I have the code in place and no errors are flagged now. Progress! But when I put a date in the Date Picker nothing changes. Is it that while the Date Picker has a checkbox, it cannot be coded as a checkbox? When I click the checkbox, the only change in value is from "" (blank) to containing the date picked (see pictures below).

FLWR-DTP-NOdate.JPG
FLWR-DTP-date.JPG

[Logit]

.
Please post your workbook for review. Do Not include any confidential information.

What are you expecting to occur when you check the checkbox ... and when you uncheck the checkbox ?

[ProfXer]

It took a while, but I finally sanitized the workbook for posting. The User Form is accessed via the "ADD" or "EDIT" buttons on the Data tab. The EDIT button is the one I'm mainly interested in getting up and running.

Unfortunately, I cannot seem to attach a file. I've even tried it in Firefox & Chrome. Nor have I had much success with the site search engine to find instructions to attach workbook.

[ProfXer]

I think I may have found something that allows an upload. At any rate, when a date is entered in the form for "Date FLWR Received" (DTPDateFLWRrcvd) the action to change the UserForm, DataUF, should be triggered. To enter a date, one must click on the radio button of the Date Picker.

[ProfXer]

I was able to adapt the code Logit provided and make the form change color immediately upon clicking the checkbox to use the date picker. However, when I unclick the Date Picker the color remains changed and does not return to the original color. So something is still not quite right.

The code below assumes that the date picker Value returns to "" when the picker checkbox is not checked. Somehow I suppose I need to have the code re-evaluate any time the checkbox is checked or unchecked. I'm not sure how to do that since date pickers are not true checkboxes.

I also just noticed that when I close out the User Form and reopen the same record, the color has reverted to blue (the original color) even though the checkbox for DTPDaateFLWRRcvd is checked. That's a problem.

Please Login or Register  to view this content.

I appreciate Logit's code setting me out into a good direction.

Thanks for your insights,

[ProfXer]

I have also noticed that date pickers do not work with Win10 OS natively. One must install the lacking files extracted from MSCOMCT2.OCX. Since this means not every machine will be able to run this code I've worked so hard to produce, I am considering taking out the date pickers and putting in mere text boxes with date data validation. Not as classy, but more portable. In that event, the object I will be wanting to write code against is a text box. In fact, even this might not serve me well enough. I will need up to 3 different criteria that will change the form backcolor.

One criteria is the above date of the returned FLWR being filled out. I'd like this to be immediately effective when someone fills in a date or deletes it (makes it blank) in the userform.
The next criteria is when the status contains the word "connected" (this will include "connected" and "not connected"
Another possible criteria is a status of "disqualified"

One solution I see is to use helper columns for each entry. I could right a formula that results in true or false for each of these criteria. However, I would not see immediate color change on the first criteria since that information has yet to be written to the spreadsheet from the form.

Do you think this solution will be less problematic than the date pickers?

[ProfXer]

I am still searching for a solution to the problem. I dumped the Microsoft built in date pickers due to not being usable on all PCs the file is opened in. Using Trevor Eyre's VBA code date picker and it works great. So now in stead of DatePicker's with check boxes, etc., I am using a text box associated with a command button.

The objective is the same... change color of form when a date is filled in a specific text box (reverts to original color if the date is deleted from text box). The user form is named DataUF and the target text box is TxtDateFLWRrcvd. So if the form is opened and a date is already filled in (from the spreadsheet it reads from) it should turn color from default.