Macro behaviour problem (1 Click on "run" button not enough)

Question

Hello Everyone,

I am a newbie and I have looked for an answer everywhere but I could not find any solution.

I have an excel sheet with macros in it, when I run the macro , it does not apply instantly, I need to click anywhere else on the excel after having clicked on the run buttton to make the macro running.

Since the macros are used for a professional purpose, I am loooking to make it running by just pressing the " run " button one time .

Thank you in advance for your Help

Best regards,

Yas_

Answer 1

Hi

It is difficult to visualize your requirement with the current information, your workbook is too complex to give you a solution without having access to your file.

Kindly suggest prepare and upload a sample file to Onedrive, Dropbox, etc ... and share the link here

Please, 

a)  Remove any personal/sensitive data

b) Keep the headers, table structure, and formulas as they actually are

c) ZIP the file if contain macros, shapes, or other objects.

d) Provide more details of your scenario and goals. Also, post the results expected.

It will help us to give you a prompt and right solution.

If you need help with how to upload the file please, click the link below

https://support.office.com/en-us/article/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

You may also try

https://www.youtube.com/watch?v=NnXsE0SNuCc&t=14s

Regards

Jeovany

Answer 2

Sorry I think that I have not explained it clearly, the autofiltering is written on the sheet code but it is active only on a few cells ( U1 to AL2) that are initially empty.

I fill these empty cells by using combo boxes with some datas input I have assigned , so when I select an input in the combo box, it writes it in the empty cells, and the autofiltering launches automatically. 

I use macros as well to empty the content of the cells that have already been filled to remove the filter, and the autofiltering removes directly the filters.

My issue comes from the macros for clearing and the combo boxes for filling the cells. They work fine, but my only issue is that it is not user friendly, you need for the combo boxes to  : select + click on any cell of the sheet, for the macros : run macros + click on any cell of the sheet.

I need it to select or run the macros directly without having to click on any cell of the sheet afterwards.

So without the auto filtering, the combo boxes would just fill the empty cells and nothing would happen.

If I   am not wrong, the autofiltering should not interfere with the tasks of my combo boxes and macros right ? 

Do you have an idea of the reasons why I need to click afterwards to make the validation of the tasks possible and how to change it ?

Thank you again for your patience and help, I am new to this excel coding.

Best regards,

Yasin Ameslon

Answer 3

Hi Yasin

It is not advisable in your scenario to use the filtering macro on the worksheet SelectionChange event. 

As it is the macro will run every single time you select any cell or object in the sheet. 

In plain words. That's driving crazy the application, and giving conflicts and errors

The macro should run (I guess) after choosing the filters on the combo boxes.

Answer 4

Thank you for your answer, my macros are macros for clearing data:

Code Example :

---

Sub Clearcells()

'Updateby Extendoffice

Range("AH2").Clear

End Sub

---

And the code on the worksheet is for autofiltering :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Range("A2:R1000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _

("U1:AL2"), Unique:=False

End Sub

I have also combo boxes for choosing the filters, and the behaviour is the same as the macros , when I choose an element in the combo box, I cannot push enter and I need to click elsewhere on the excel to validate.

Unfortunately I do not see anything stopping the automatic calculation.

Best regards,

Yasin Ameslon

Answer 5

HI

Please 

Check if your macro or any other macro in the workbook have some code line that turns off the automatic calculations on your workbook. 

Regards 

Jeovany