Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Idiot proof Excel - session 7: stopping the screens moving when a macro runs

0

Hi, 

After I uptade my file with the VBA code from Idiot Proof session 7, I am unable to store the data in the "Data" sheet and it is not cleared from the "Form" sheet.

Can you please help me? 

I am attaching the file, so you can have a look. 

Note: I deleted the conditional formating feature, because was not working properly and I want to be able to submit data without all the fields beeing filled. 

Thank you very much, 

Mariana Guedes 

excel
posted by: mguedes33 (rep: 2) on Fri Oct 7, 2022 at 5:55 am
Answer
Discuss

Discussion

Hi, I just downloaded it, filled in the remaining 10 fields, hit the "Submeter" button and everything worked for me. It also looks really good, you've made an impressive input form!

I'd say you should close and re-open the file, make sure macros are enabled, and try again. If it still doesn't work, please provide more information for troubleshooting purposes, beacuse, to me, it looks fine and everything is working.
don (rep: 1989) Oct 7, '22 at 4:37 pm
Hi Mariana

I've never seen this file before but it all seems to work as I would expect it to. Like Don, I downloaded the file and could create a new record using the "Submeter" button. I could also retrieve that (or other records) using the "Procurar" button and giving a number from column A (numInternamento) of the Data worksheet.

Hope that Don's suggestion of enabling macros works for you.
John_Ru (rep: 6142) Oct 8, '22 at 5:28 am
Hi, 
Thank you Don and John! The issue is that I want to save the data, even though I have empty fields. I  managed to solve the issue by moving the "save the data" command to after the "End If" sentence. 
Now, I have a different issue. I added the code to password protect both sheets (form and data sheets), and now everytime that I submit or search for records in the form sheet it takes too long and the form sheet keps moving up and down. (new Excel attached in my first post).
Can you please let me know how I can improve this feature? 
Thank you very much for your help ;) 

Have a nice day, 
Mariana 
mguedes33 (rep: 2) Oct 17, '22 at 6:16 am
Mariana

Please see my Answer. Also, kindly edit your question so the title now refers to stopping the screens moving when a macro runs (that will be helpful to other users).
John_Ru (rep: 6142) Oct 17, '22 at 9:48 am
Thanks for changing the question title too 
John_Ru (rep: 6142) Oct 18, '22 at 4:42 am
Add to Discussion

Answers

0
Selected Answer

Mariana

One way to speed up your macro(s) and prevent "the form sheet keeps moving up and down" is to disable screen updating (so you only see the results once the macros have run).

I'm not sure how your macros interact but I've applied that to each macro, putting this line early in the macro: 

' Speed up by preventing updates

Application.ScreenUpdating = False

and finishing each with: 

' Restore updates

Application.ScreenUpdating = True

(You probably need that only on 2 or 3 of the 4 really).

Note that on your form, I had to remove the data validation on cell F4 since it would not accept the existing values from your Data sheet. Also.you have D4 as "Nº pocesso" but it should probably be !Nº processo".

There are other ways to speed up macros .e.g. see Don's tutorial here Guide to Making Your Macro Run Faster and Better in Excel

Hope this helps.

posted by: John_Ru (rep: 6142) on Mon Oct 17, 2022 at 9:47 am
Discuss

Discussion

Thank you very much John. 
It solved my problem ;)
mguedes33 (rep: 2) Oct 18, '22 at 4:23 am
Great. Thanks for selecting my answer Mariana. 
John_Ru (rep: 6142) Oct 18, '22 at 4:40 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login