Macro to Populate Textbox with Restricted Value

I am making a Dashboard for my workbook. I have inserted a textbox (call it "Textbox1" for the purposes of this study) on "Sheet1". I am wanting this textbox to go through all active sheets (excluding "Sheet1", "Sheet2", "Sheet3") and provide back the most current date from cell "F5" based upon a restriction that the WS cell "F3" has to be "Compliance". The intent would be that this would be a workbook open based code that way when the workbook opens it provides the user with the last completed date. Ex. "Sheet4" has August 29th, 2013 in cell F5, "Sheet5" has October 8th, 3013 in cell F5, "Sheet6" has November 7th, 2014 in cell F5 but no "Compliance" in F3. The value returned into the textbox would be October 8th, 2013.

Originally Posted by LJMetzger

Hi jared,


End Sub
[/CODE]

LJ it seems to do the intended function the only question I have is can this work as a workbook open code so the box is populated when the user enters the workbook? I think you may have had this setup but it did not work when I tried to run the file.

Hi jared,

I did not have the macro run in Workbook_Open(). The attached file has added the following code in Workbook_Open() (and works for me using Excel 2003):

Please Login or Register  to view this content.

Lewis

Originally Posted by LJMetzger

Hi jared,

I did not have the macro run in Workbook_Open(). The attached file has added the following code in Workbook_Open() (and works for me using Excel 2003):

Please Login or Register  to view this content.

Lewis

LJ if I wanted to add a Textbox2 which took the Textbox1 value and added three years would this be too complicated?

Also LJ because these are Active Control Textboxes are you able to adjust the fill color?? Or would I have to use an regular textbox and adjust the code to be able to add colorfill to the textbox?

Hi jared,

See the attached file which:
a. Has a second TextBox (TextBox2) with a date 3 years after the date in TextBox1.
b. Allows setting of the 'fill' (background) color of text boxes.
c. Demonstrates some of the Text Box attributes that can be changed (Font Color, Background Color, Font Size, Font Name, Font Bold, Text Centering).

I also added a module that accesses some RGB colors by name (e.g. myRGB_Red) rather than by RGB(255, 0, 0).

Lewis

LJ,

Wow very impressive. Everything seems to work well. Could you help me with a few minor modifications:

1.) Addition that if "Textbox1" = "No Dates" (i.e. I changed the wording from what you had) then "Textbox2" = Todays Date (being whatever the date is that the workbook is open)

2.) I am looking for the color scheme to work as such when "Textbox2" is 3 months from "Todays Date" the textbox will be Green, 1 month from "Todays Date" the textbox will be Yellow, 2 weeks from "Todays Date" the textbox will be Orange, and if it is on or has passed "Todays Date" then it will be Red.

jared,

We're getting very close. I think we probably need at least one more iteration to get what you want. I removed extraneous code, and implemented the changes you wanted.

I have the following Questions/Concerns:
1. There seemed to be a hole in the color scheme. I added MAGENTA for 1 - 13 days.
2. If the data in the TextBoxes is NEVER USER MODIFIED, then the 'Text Boxes' should be changed to LABELS (could be one or both text boxes).
3. If the data IS USER MODIFIED, Software for TextBox2 MUST be modified to recognize the change in TextBox1, and update the Value/Colors in TextBox2.

We need to:
a. Finalize the Color Scheme for TextBox2.
b. Determine if USER MODIFICATION is allowed in TextBox1.
c. Determine if USER MODIFICATION is allowed in TextBox2.

Lewis

LJ,

Textbox1 will have the potential to be user modified. Textbox2 should not though.

I think I may have misrepresented the intent of the color scheme. The goal is that Textbox2 is always 3 years from Textbox1. As originally designed. The color is used in a comparison with the value in Textbox2 to the date that the WB is opened (i.e. "Todays Date"). So for example when "Todays Date" is 3 months away from the textbox2 value it will change colors to show priority. This meaning the textbox2 value shouldn't changes values. (I May be confused by your test buttons but is this how they were working?)

So essentially if:

"Todays Date" is 3 months-1 month 1 day from textbox2 value then Textbox2 = Yellow
"Todays Date" is 1 months-15 days from textbox2 value then Textbox2 = Orange
"Todays Date" is 15 days-1 day from textbox2 value then Textbox2 = Magenta
"Todays Date" is 0 days or past from textbox2 value then Textbox2 = Red

Jared,

If this doesn't do what you want, it's extremely close.

I made the following changes:
a. The dates for the colors were modified per your request.
b. TextBox2 has been replaced with 'Label1' (looks and feels the same as TextBox2). 'Label1' can NOT be modified by the user.
c. When the user modifies 'TextBox1', the value in 'Label1' (and the 'Label1' colors) are automatically updated.
d. If the user accidentally changes 'TextBox1', 'Double Click' on 'TextBox1' will restore the original value that was in TextBox1 when the file was opened.

Lewis

LJ,

The code seems good. I came across one issue though. If the user changes the date in "Textbox1" to say a desired value and then saves/closes the WB when they reopen it since it is a wbopen code it will change there previously entered value. Do you have any suggestions? (I was thinking maybe making some way that when they changed the date it deactivates the wbopen portion but adds some kind of comment or asterick to the textbox that I can put like a footnote explaining this below it) your thoughts?

Jared

If the user changes the date in "Textbox1" to say a desired value and then saves/closes the WB when they reopen it since it is a wbopen code it will change there previously entered value.

Hi Jared,

The law of 'Unintended Consequences' strikes again.

There are a number of ways this issue that I can think of all triggered by the automatic update of 'Label1' when the value in 'TextBox1' changes:

1. Assign a cell on the worksheet that will contain non-blank data (suggestion value is the date and time when 'TextBox1' is manually updated). This cell could be formatted as hidden ';;;' (three consecutive semi-colons) to avoid changing the look and feel of the workbook.

2. Add an asterisk '*' in front of the date in 'Label1' to indicate that the date in 'TextBox1' was manually entered.

------------

When the chosen condition is present, (e.g. ASTERISKS) Workbook_Open() will not do any 'Compliance' date processing.

There is at least one additional side effect of this fix. Automatic Update is PROHIBITED forever after that. A possible fix to this situation is to manually CLEAR 'TextBox1'. This will allow AUTOMATIC UPDATE in the future.

I implemented Option 2 in the attached workbook. I also allowed AUTOMATIC UPDATE when 'TextBox1' is cleared.

Please let me know if this does what you want.

Lewis

Originally Posted by LJMetzger

Hi Jared,


Lewis

Lewis,

I think I might have a modified strategy that will work ( I have used a similar technique elsewhere). When the user clicks the "Textbox1" to make a manual entry it stimulates a msgbox vbYESNO saying "Change Last Completion Date". If users selects "NO" it deselects the textbox not allowing them to make the entry. If they select "YES" it pulls up a Userform (will say Userform1 just for example). Userform1 has a simple "Textbox1" and "Commandbutton1" and "Optionbutton1". They input the desired date into the userform textbox and hit the commandbutton. This takes the data from the userform textbox and puts it into the embedded textbox but with a simple * before. Then put a conditional statement that in the workbook open statement that if "Textbox1" has an * to not run. The Optionbutton1 will be code to restore the workbook open code so that when the user selects optionbutton1 and hits commandbutton1 it will run the automated WS search as well as reactivate the WB Open code(Thus removing the asterisk). (To reiterate the asterick is only place if userform textbox1 has a value and commandbutton1 is hit not when the optionbutton is selected)(The optionbutton1 will rerun the original code that searches the workbook and puts it in "Sheet1" "Textbox1" and then reactivate the WB open code).

If you want I can post my WB so we can stop using such loose terms and I can create the userform to have ready?

Jared

If you want I can post my WB so we can stop using such loose terms and I can create the userform to have ready?

Jared,

That sounds like an excellent idea. We should probably disregard my last update file ' ExcelForumWorksheetTextBox6-2014-04-24-12-52.xls‎'

Lewis

I am assuming the adjustment could be made individually on this line of code that before the first statement that if the mentioned "Textbox" value has an "*" then not nothing Else run the code

Please Login or Register  to view this content.

Hi Jared,

Thanks for posting the workbook. Very nice design. My Excel 2003 is not compatible with your Active X controls, so I had to make changes using my simplified Excel 2003 version of your workbook.

I think I was able to do everything you wanted, but as usual there was at least one unintended side effect. The 'Double Click' to restore the original values no longer works. The 'Double Click' is preempted by the code that activates UserForm20 and the 'Double Click' is not recognized. If you want to activate the code that the 'Double Click' called, addiing a CheckBox or Command Button to UserForm20 would probably solve the problem.

My simplified workbook and a zip file containing files and instructions for porting the code to your .xlsm file is attached.

The attached zip file contains the following files:
a. Module5.bas
b. ModZTest.bas
c. ModTemporary.bas
d. ModSheetXTemporary.bas
e. InstallationInstructions.txt (this text)


Changes made:
a. Added a global variable, supporting constants and code to identify the active Sheet 'Home' TextBox.
b. Added code in FindandDisplayLatestAuditDateInDisplaySheetTextBox() to prevent update of
Sheet 'Home' Labels when the corresponding 'TextBox' value starts with '*'.
c. Added routine SetFocusOnSheetHome() to move focus off Sheet 'Home' TextBoxes when
UserForm20 data entry completed.
d. Added 'Sub CommandButton1_Click()' to UserForm20 code.
e. Added Module ModUserForm20 to set up and then process UserForm20 data.
f. Modified 'Workbook_Open() to added call to 'SetFocusOnSheetHome() '.
g. Modified 'Worksheet 'Home' code:
(1) Added TextBoxN_GotFocus() routines to simulate a TextBox 'Click Event'.
Active X TextBoxes don't have standard 'Click Events'.
(2) Implemented global variable 'bGblInhibitSheetHomeTextBoxEvents' to prevent unwanted events.
h. Added OPTIONAL 'Double_Click()' events in all Sheets that return focus to Sheet 'Home'.
i. Corrected miscellaneous errors in code comments

NOTES:
1. Debugging UserForm20 code is a lot easier when the UserForm does not lock out other activities.
This can be done by setting CONDITIONAL COMPILATION constant 'PRODUCTION'
in module ModUserForm20 to 'False'.
2. Another Unwanted Side Effect - 'Double Click' of Text Box is superseded by 'Got_Focus()' used
by UserForm20. To implement restore original value, a choice (checkbox or Command Button) is
probably needed on UserForm20.

Installation Instructions:
1. Backup the .xlsm file

2. Delete the following Modules from the .xlsm file
a. Module5

3. Import the following Modules:
a. Module5.bas
b. ModZTest.bas
c. ModTemporary.bas
d. ModSheetXTemporary.bas

4. In the .xlsm 'ThisWorkbook' module:
a. Replace Workbook Open with the 'Workbook_Open() code in Module 'ModZTest'.

5. In the .xlsm 'UserForm20' code module:
a. Delete empty 'Sub Userform_Click()' which is not needed.
b. Add ( Cut and paste) the code for 'Sub CommandButton1_Click() [3 lines total ] 'from 'ModZTest.

6. In the .xlsm Sheet 'Home' module:
a. Delete the existing code.
b. Cut and paste the code from 'ModTemporary' to the Sheet 'Home' module.

7. Optional: Copy the code from Module 'ModSheetXTemporary' into the various Sheet
Modules, to enable 'Double Click' back to Sheet 'Home'.

8. Delete Modules:
a. ModZTest
b. ModTemporary
c. ModSheetXTemporary
WARNING: If the DELETE step is NOT DONE, the workbook may LOCK UP.

8. Save the file and Test


Lewis

LJ,

I am unable to access the ModSheetxTemporary or ModTemporary modules?? What exactly do these contain. I imported the new Module5 the ModzTest and the ModUserForm20. The application seemed to work but I was wondering what these temporary files were. As well another issue of concern is that it affected one of my other macros in the "ThisWorkbook" module. The one at the end because a few variables were not previously defined.

I'm sorry you had problems. I tested the entire procedure and it worked fine for me using Excel 2003.
You can access the files with any text editor.

ModSheetXTemporary code (optional - goes in code for other sheets):

Please Login or Register  to view this content.

ModTemporary Code (replaces the Sheet "Home" code):

Please Login or Register  to view this content.

Workbook_Open() code (should not affect anything else). It should replace your existing Workbook_Open() and should not interfere with your variables or anything else.

Please Login or Register  to view this content.

I've got too much invested in this to fail now. Please let me know what I can do to make you smile, without too much effort on your part.

Lewis

Strange I moved everything over again and it seems to work fine now. Do you see any other concerns with the format? As well thanks you for all your help Lewis and please feel free to take anything from the workbook that may be beneficial to future projects.

I'm glad it's working, and thank you for permission to use your software. Please let me know if you have any problems in the future on this project, or on anything else.

This was a fun project to work with, as I don't usually deal with TextBoxes and Labels on Spreadsheets very often.

Lewis

Thanks for all your help Lewis and if interested/bored I will have a few more posts out there as I am trying to make the final run on some concepts that I am unfamiliar with. I cant express how much I appreciate all your effort.