Hello,
I'm new to VBA and I need some help with speed being slow when inputing data into my file (sorry for my data being in French). When I input data in the Fields entitled "École", "Année scolaire", i can't do anything for about 4 seconds. When I input data into the field "Nombre d'élèves", and I click on the big yellow button to get to another sheet (2nd mini sheet) then the number of lines showing should match the number inputed on the first sheet by using this code (I have another similar sheet using the same code that is password locked which you can access by clicking on the left green button on mini sheet 1, but this sheet is running a little faster) and the some formulas in the sheet itself (see below)
CODE
Formula
=IFERROR(IF(Accueil!E13="","",1),"")
=IFERROR(IF(A11+1>Accueil!E$13,"",Ecole!A11+1),"")
where "Ecole" is the active sheet (mini sheet 2) and "Accueil!E$13" is the cell I inputed the numbre 5 in mini sheet 1
I then manually input data in the other columns in mini sheet 2 and here is where it gets really slow, about 15 seconds before I can click in another cell.
I must say that the data inputed into mini sheet 2 is beeing copied to 3 other sheets.
Any help would be appreciated
Mini sheet 1
Mini sheet 2
I'm new to VBA and I need some help with speed being slow when inputing data into my file (sorry for my data being in French). When I input data in the Fields entitled "École", "Année scolaire", i can't do anything for about 4 seconds. When I input data into the field "Nombre d'élèves", and I click on the big yellow button to get to another sheet (2nd mini sheet) then the number of lines showing should match the number inputed on the first sheet by using this code (I have another similar sheet using the same code that is password locked which you can access by clicking on the left green button on mini sheet 1, but this sheet is running a little faster) and the some formulas in the sheet itself (see below)
CODE
VBA Code:
Private Sub Worksheet_Calculate()
Dim c As Range
Application.EnableEvents = False
For Each c In Range("A11:A110") '<====Input your range here
If c.Value = "" Then
Rows(c.Row & ":" & c.Row).EntireRow.Hidden = True
Else
Range(c.Row & ":" & c.Row).EntireRow.Hidden = False
End If
Next
Application.EnableEvents = True
End Sub
Formula
=IFERROR(IF(Accueil!E13="","",1),"")
=IFERROR(IF(A11+1>Accueil!E$13,"",Ecole!A11+1),"")
where "Ecole" is the active sheet (mini sheet 2) and "Accueil!E$13" is the cell I inputed the numbre 5 in mini sheet 1
I then manually input data in the other columns in mini sheet 2 and here is where it gets really slow, about 15 seconds before I can click in another cell.
I must say that the data inputed into mini sheet 2 is beeing copied to 3 other sheets.
Any help would be appreciated
Mini sheet 1
Écoles élémentaires - Dotation AE 2021-2022.xlsm | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | |||
8 | |||||||||||||||
9 | |||||||||||||||
10 | École: | Année scolaire: | |||||||||||||
11 | |||||||||||||||
12 | |||||||||||||||
13 | Nombre d'élèves: | 5 | |||||||||||||
14 | |||||||||||||||
15 | |||||||||||||||
16 | Nom de la personne ou des personnes qui ont contribués aux données fournies dans ce fichier: | ||||||||||||||
17 | |||||||||||||||
18 | |||||||||||||||
19 | |||||||||||||||
20 | |||||||||||||||
21 | |||||||||||||||
22 | |||||||||||||||
Accueil |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
H10:H14 | List | = Année |
C10:E10 | List | = Écoles |
E13 | List | = Nombre |
Mini sheet 2
Écoles élémentaires - Dotation AE 2021-2022.xlsm | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | |||
8 | |||||||||||||||
9 | |||||||||||||||
10 | École: | Année scolaire: | |||||||||||||
11 | |||||||||||||||
12 | |||||||||||||||
13 | Nombre d'élèves: | 5 | |||||||||||||
14 | |||||||||||||||
15 | |||||||||||||||
16 | Nom de la personne ou des personnes qui ont contribués aux données fournies dans ce fichier: | ||||||||||||||
17 | |||||||||||||||
18 | |||||||||||||||
19 | |||||||||||||||
20 | |||||||||||||||
21 | |||||||||||||||
22 | |||||||||||||||
Accueil |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
H10:H14 | List | = Année |
C10:E10 | List | = Écoles |
E13 | List | = Nombre |