We help IT Professionals succeed at work.

Need help refreshing Modeless Userform

166 Views
Last Modified: 2017-04-11
The program in the attached file is used by Account Managers to report chemical readings observed at a customer's location.  Some Account Managers may have 10 or more customer files to maintain.  I have developed a code module to support future releases of the program that systematically copies customer data from existing files into the newer version of the program.

In an effort to reduce screen flashes I created a Modeless Userform that informs the user of progress throughout the program update progress.  The issue I am having is that the Userform opens and displays correct information initially.  After a file is updated, the Userform is Unloaded and then the Show command is issued as the next file is being manipulated.  Although the Userform remains visible on the screen, it is now simply a white blank form with no progress information shown.

I am attaching 3 files than can be used by anyone who would like to correct this problem.
File 1 which is the new program file is "WaterTech Report Tool Vers 5.  The code module in question is "NewProgram_Import_All_Files"
Files 2 & 3: "Bar S Foods-2" and "Bar S Foods-2-1" are 2 files that would be updated via this code module.  Copy these 2 files to a Folder on your PC.  The program steps you through selecting these files.

For any seasoned programmer that attempts to help me I ask that you forgive my "Novice" mistakes.  This was a first effort at programming in VBA.  While my approach may leave you snickering at my less than desirable approaches I can assure you that other than this issue with the Userform the program delivers the desired outcome.  Through monitoring Experts Exchange and other VBA forums I have learned greatly about more professional approaches and have begun the process of updating my code to apply those practices such as not Selecting or Activating and using "Cells" to reference Ranges rather than needing to convert the numeric value of Columns to an alphanumeric value.

Thanks in advance for any assistance that can be provided.
WaterTech-Report-Tool-Vers-5.xlsm
Bar-S-Foods-2.xlsm
Bar-S-Foods-2-1.xlsm
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
How do I bring up the userform?

Author

Commented:
The Uerform; Import_Status, is called from within code module "NewProgram_Import_All_Files".
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'm sorry but I can't find a module by that name or any procedure with that name in the WaterTech-Report-Tool-Vers-5.xlsm workbook you posted. Am I starting in the right place? If so which of the buttons that show up when I open WaterTech-Report-Tool-Vers-5.xlsm should I select?

Author

Commented:
Go to the last Worksheet titled "Setup Controls". The button labeled "New Software Release Import Data from Existing Files" initiates code module
"NewProgramImportAllFiles"
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'm out and will be back in a couple of hours.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
When I run the program and select one or both of the other two workbooks I get this message and when I click OK msg  the main workbook closes. BTW you should put Application.ScreenUpdating = False at the beginning of the process and Application.ScreenUpdating = True at the end.

Author

Commented:
Martin thank you for your efforts. I will continue to try to figure out why the Userform doesn't update properly.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What should happen if I choose both of the two auxiliary files?

Author

Commented:
The program copies data from the Setup and Control Limits Worksheets from the file to be Imported into the program file (WaterTech Report Tool Vers  5).
That data is used to configure the Analysis, Historical Analysis Report and Historical Data Table Worksheets ( the number of columns and rows can differ for each individual file). Then data from the Analysis and Historical Data Table Worksheets is copied into the program file. Once those steps are complete, the file is saved back into the folder and using the file name of the file being imported.

Author

Commented:
Martin, It may help to open the files to be Imported, go to Worksheet "Setup", click on cell F16 and choose a Folder on your PC. The program saves a copy of the file to be imported to a folder that gets created as part of the Setup routine that gets called during this Import process.

Author

Commented:
I so appreciate your interest and willingness to assist me in spite of the challenges you have run into!
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
No assistance was provided and I ultimately was able to get the issue resolved on my own.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.