Command Button error

[krishnaa_kumarr88]

I created input userform. Eachtime i input any information in the userform and when i close the userform i should able to see the corresponding information in the excel row. But the excel is not doing auto filter which made me to create autofilter in the vb close button. But the issue is when i click close button in excel it taking atleast 4 mins to update each information. Does any one know why ? the code is below,


private Sub CommandButton2_Click()
Unload Me
Worksheets("TPM FORM").Activate
ActiveSheet.AutoFilterMode = False
UserForm1.Hide
End Sub

Thanks

[HansV]

The code itself doesn't provide a clue...

[krishnaa_kumarr88]

http://www.filedropper.com/rudi_3" onclick="window.open(this.href);return false;

Hi ,
When you open the above attachment, you will automatically see TPM FORM. Please input some data and in the RESULT field, please select REQUIRES ATTENTION and click CLOSE. It take atleast 2 min to close the input form.
Do you have any idea why?
thanks a lot

[HansV]

1) Each change that the code makes to the TPM FORM sheet causes the Worksheet_Change event procedure to run, and this calls ActiveWorkbook.RefreshAll. Since you update 9 to 13 cells, all external links and pivot tables in the workbook are updated 9 to 13 times. You could avoid this by inserting

Code: Select all

    Application.EnableEvents = False

at the beginning of CommandButton1_Click, and

Code: Select all

    Application.EnableEvents = True

at the end.

Next, the code will send an e-mail, so it has to start Outlook, create an e-mail message and send it. This takes time.

[HansV]

Sorry, the above applies to the OK button. The reason that the Close button is so slow is that the used area of your sheet has more than 39,000 rows, while only 44 of them are actually in use. If you delete the rows below the last filled row, the Close button will not cause a delay.

[krishnaa_kumarr88]

Thanks for your reply. Do you mean to delete in manually?

If i remove the below code from the close button, it does not cause delay,

Activesheet.AutoFilterMode=False

but , each time i need to click on filter in the excel sheet to view the latest updated list. Its not updating automatically in the excel sheet.

Thanks

[HansV]

Yes, I meant to delete the unused rows in the TPM FORM sheet manually.

[krishnaa_kumarr88]

thanks