Discussion :

[juan67]

Bonjour,

J'ai un fichier excel qui comporte 260 feuilles et qui aura tendance à s'agrandir. Chaque feuille comporte un tableau avec 4 radiobutton pour filtrer le tableau et un bouton imprimer.

Cependant, toutes les feuilles utilisent le même code est la même configuration .
N'ayant malheureusement pas réussi d'uniformiser cela en utilisant avec un module pour permettre d'écrire qu'une seul fois le code VB.

Le problème étant qu'aujourd'hui mon fichier excel met 30s à s'enregistrer et autant pour s'ouvrir ... c'est donc très lourd.

Pouvez-vous m'aider à éviter d'inscrire ce code VB sur toutes les feuilles , je pense que cela réduira la lourdeur du fichier.

ci-joint mon fichier pour mieux comprendre , que j'ai simplement laissé avec 30 feuille car celui avec 260 feuilles est beaucoup trop gros (6Mo) mais ca reste le même principe.

merci de votre aide ! :

[COCONUT2]

Chaque code est copié sur les feuilles car tu utilises Activesheet


Et si tu ne laissais sur chaque feuille que les commandes de bouton qui renverraient vers une macro situé dans le module 1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
sub bouton1_click()
 
MONBOUTON1 (activesheet.name)
 
end sub

tu pourrais dans le module 1 avoir

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
sub MONBOUTON1 (nom as string)
 
'et faire toutes les actions liées à ce bouton en commencant par
 
sheets(nom).cells.....

Comme ca tu réduis considérablement le code lié à chaque feuille et tu recentres à un seul endroit le code (ce qui est plus simple en cas d'évolution)

[Philippe Tulliez]

Bonjour,
Pour avoir un code identique pour un ensemble de feuille, soit tu écris ta procédure dans un module soit tu utilises une procédure événementielle de l'objet ThisWorkBook en lieu et place de celles des feuilles.
Exemple si tu utilises la procédure événementielle Change des feuilles.
Procédure dans toutes les feuilles

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Private Sub Worksheet_Change(ByVal Target As Range)
 MsgBox "Vous avez modifié la cellule " & Target.Address & " de la feuille " & Me.Name
End Sub

Dans ThisWorkbook (Une seule fois)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 MsgBox "Vous avez modifié la cellule " & Target.Address & " de la feuille " & Sh.Name
End Sub

[juan67]

Ok donc si j'ai bien compris le "activesheet" sera remplacé par "sheet(nom)" où nom sera récupérer par le code du bouton imprimer .

J'essai de faire cela tout de suite et si j'y arrive pas pourrais-tu m'en faire un exemple directement pour une feuille ?

J'ai donc fais comme tu me l'a proposé COCONUT2 pour la feuille ("1") cependant il me dit "sub ou function non définie" et même surligne en jaune la 1ère ligne de code "Sub MONBOUTON1(nom As String)" puis en bleu le mot "sheet"

Fichier joint. Peut-tu m'indiquer l'erreur que j'ai faite ?

Merci beaucoup !

Edit : Mon erreur a été corriger c'était sheets et non sheet ! cependant il ne me lance pas l'impression ... après appui sur le bouton rien ne se passe maintenant.

[COCONUT2]

Répondre, maintenant que ta procédure principale est dans le Module1, il faut l'adapter en désignant précisément chaque élement en lui disant par exemple que c'est l'option button de la feuille.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
if sheets(nom).optionbutton1=true then

mais ta procédure ne dit pas qu'elle doit imprimer si c'est l'optionbutton4 qui est sélectionné.

Si c'est le cas, tu peux sortir l'appel de la boite d'impression de la fonction if puisqu'elle est commune à tous

[juan67]

Pour l'impression , elle est commune à tous sauf le radiobutton4 car il permet juste d'afficher le tableau entier. Poour les 3 autres radiobutton , chacun dévoile des colonnes différentes lors de la mise en page d'impression.
D'où l'impossibilité je pense de sortir la boite de dialogue d'impression.

[COCONUT2]

en notant bien

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

if sheets(nom).optionbutton1=true then

ca marche bien chez moi.

[juan67]

Oui oui pas de soucis , ca marche nickel ! je te remercie .

En espérant maintenant que tout ce code va permettre de diminuer la lenteur du fichier ... car la c'est assez horrible.

Je précise que se fichier est lié une base de données Access que j'ai créé et que chaque feuille représente un enregistrement. Donc l'ouverture se fait automatiquement par l'appui d'un bouton à la feuille correspondante repéré par son nom d'où l'importance d'une ouverture assez rapide ...

[COCONUT2]

Dans ce cas note que c'est résolu

[Philippe Tulliez]

Bonjour,
Personnellement, j'aurais pris comme option d'utiliser un UserForm en lieu et place de l'ensemble des boutons qui serait enclenchée par la procédure événementielle Workbook_SheetActivate.
Ainsi, tu n'aurais qu'un seul UserForm et une seule procédure à gérer.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  usfPrintSheet.Show
End Sub

L'argument sh représente la feuille activée

[juan67]

C'est plutot une bonne idée , sauf que mes compétences n'allait pas aussi loin au départ ^^ . Je me suis depuis amélioré.

Donc tu propose que mes 4 radio boutons ainsi que le bouton imprimé soit mis dans un userform qui s'affichera lors de l'affichage d'une feuille ?

EDIT: j'ai créé un userform comme tu me l'a dit avec tous les boutons et mis sur une feuille le code précisé dans ton message. Cependant sur je clique sur la feuille voulu , le userform ne s'affiche pas ...

[Philippe Tulliez]

Bonjour,
Exact, la variable objet nommée sh est l'objet de la feuille qui est activée et donc tu hérites des propriétés et méthodes de la dite feuille.
Ainsi, si tu as une feuille sélectionnée qui ne doit pas voir apparaître le UserForm, tu utilises un Select Case sh.Name par exemple ou un IF pour décider si le UserForm s'affiche ou pas.
De plus, tu peux rendre le UserForm non modal pour permettre à l'utilisateur de faire des modifications dans la feuille quand le UserForm est actif.
Exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Select Case Sh.Name
   Case "Menu", "Param"
   Case Else
      usfPrintSheet.Show vbModeless
  End Select
End Sub

Dans cet exemple, le UserForm nommé usfPrintSheet s'affiche dans tout les cas excepté si la feuille se nomme Menu ou Param

[COCONUT2]

Attention: la fonction proposée par Philippe doit etre copiée dans la feuille Thisworkbook

Effectivement dans ce cas tu n'as plus de code dans aucune des feuilles.
Juste les fonctions de la Userform et la macro du Module 1 (que tu pourrais d'ailleur ramener dans la userform.)

L'inconvénient de cette solution est que la userform d'impression s'affichera une seule fois à chaque fois que tu sélectionneras un onglet.

Ca peut etre pénible si ce n'est pas l'unique raison d'etre de ton fichier

[Philippe Tulliez]

Bonjour,

Attention: la fonction proposée par Philippe doit etre copiée dans la feuille Thisworkbook
Effectivement dans ce cas tu n'as plus de code dans aucune des feuilles.
Juste les fonctions de la Userform et la macro du Module 1 (que tu pourrais d'ailleur ramener dans la userform.)
L'inconvénient de cette solution est que la userform d'impression s'affichera une seule fois à chaque fois que tu sélectionneras un onglet.
Ca peut etre pénible si ce n'est pas l'unique raison d'etre de ton fichier

Absolument.
Une autre option alors serait de créer un bouton dans le ruban (Outils/Personnalisation des rubans) qui lancerait le UserForm ou bien, mais c'est un peu lourd créer un bouton automatiquement à l'ouverture de la feuille.
Enfin il reste une option, c'est de repenser l'organisation des feuilles si elles sont organisées de la même manière et réduire les 250 feuilles à une seule bien organisée où l'on place toutes les données et une feuille de consultation pour obtenir les informations désirées en fonctions de critères.

[juan67]

Ok , vous avez repondu à toutes mes questions et même plus ! je vous en remercie