Comment puis-je améliorer mon code de débutant ? (éviter des répétitions)

Bonjour,

Je fais mes toutes premières armes en macro, j'aurai besoin d'un peu d'aide
;)

J'ai une feuille avec environ 150 boutons dessus, et je voudrais savoir s'il
est possible d'optimiser mon code pour éviter d'y faire de lourdes
répétitions.


Mon contexte

Une feuille de 20 colonnes et 100 à 150 lignes.
Les lignes sont indépendantes les unes des autres.

Les données sont lues par un utilisateur, il saisi qq données, ça en génère
d'autres (par formules) sur la même ligne.
Au bout de chaque ligne je met un bouton qui génère un email en piochant
parmi les données de la ligne.

J'ai besoin de mettre un bouton par ligne car les destinataires des emails
et les données sont très variant d'une ligne à l'autre.

Tout ça ca fonction bien :)
Il ne s'agit pas de débugguer mais voir s'il est possible de faire moins
"bourrin" que je n'ai su le faire ;)

Mon code est constitué de 3 parties :
- une fonction associée à chaque bouton pour gérer le clic, donc 150
fonctions !
- une fonction unique qui génère les emails, ça au moins j'ai réussi à n'en
faire qu'une ;)
- et un IF pour chaque bouton (150 IF consécutifs ! ) pour cacher/montrer
les boutons.



Les boutons :
-
Une fonction par bouton, donc 150 fonctions pour appeler la fonction qui
génère l'email.
La seule chose qui change d'une procédure à l'autre est la référence au nom
du bouton cliqué
(CommandButton1, CommandButton2, CommandButton3, etc)

y a t il mieux à faire ?

Private Sub CommandButton1_Click()
Dim Adr_bouton As String
Adr_bouton = CommandButton1.TopLeftCell.Address
Envoi_Email (Adr_bouton)
End Sub

Private Sub CommandButton2_Click()
Dim Adr_bouton As String
Adr_bouton = CommandButton2.TopLeftCell.Address
Envoi_Email (Adr_bouton)
End Sub

Etc, répété 150 fois



Fonction de génération de l'email
-
Elle reçoit la référence de la ligne où se trouve le bouton cliqué
Elle pioche les éléments nécessaires sur la ligne, et génère l'email.
Elle n'est surement pas élégante, mais bon elle fonctionne, je n'y vois rien
d'important à changer.
Je mets le code ici juste pour être complet.

Public Function Envoi_Email(Pos_bouton As String)

Dim LigneOffset As Integer
Dim ColonneOffsetDestinataire As Integer
Dim ColonneOffsetSujet As Integer
Dim ColonneOffsetCorps As Integer
Dim ColonneOffsetNbEmail As Integer
Dim ColonneOffsetDateEmail As Integer
Dim Compteur As Integer

Dim ol As New Outlook.Application
Dim olmail As MailItem

LigneOffset = 0 'décallage de ligne (normalement aucun !)
ColonneOffsetDestinataire = -11 'décallage colonne, email du destinataire
ColonneOffsetSujet = -1 'décallage colonne, sujet du mail
ColonneOffsetCorps = -2 'décallage colonne, corps du mail
ColonneOffsetNbEmail = 1 'décallage colonne, Compteur de mail
envoyés
ColonneOffsetDateEmail = 2 'décallage colonne, date envoi dernier
email

Set ol = New Outlook.Application
Set olmail = ol.CreateItem(olMailItem)
With olmail
.To = Range(Pos_bouton).Offset(LigneOffset,
ColonneOffsetDestinataire).Value
.Subject = Range(Pos_bouton).Offset(LigneOffset, ColonneOffsetSujet).Value
.Body = Range(Pos_bouton).Offset(LigneOffset, ColonneOffsetCorps).Value
.Display 'Send
End With

Compteur = Range(Pos_bouton).Offset(LigneOffset, ColonneOffsetNbEmail).Value
Range(Pos_bouton).Offset(LigneOffset, ColonneOffsetNbEmail).Value = Compteur
+ 1
Range(Pos_bouton).Offset(LigneOffset, ColonneOffsetDateEmail).Value =
"=NOW()"

End Function



Fonction de masquage / démasquage des boutons.
-
Enfin j'ai aussi un IF par bouton pour cacher ou activer individuellement
les boutons selon que les lignes soient utilisées ou pas.

La aussi, 150 IF quasi identiques, ce qui est vraiment lourd !
Il doit être possible de faire plus optimisé non ? :)

Private Sub Worksheet_Change(ByVal Target As Range)
' cache ou montre le bouton d'envoi d'email selon la présence de données
dans une colonne.
With Target

If .Address = "$A$2" Then
If .Value <> "" Then
CommandButton1.Visible = True
Else
CommandButton1.Visible = False
End If
End If

If .Address = "$A$3" Then
If .Value <> "" Then
CommandButton2.Visible = True
Else
CommandButton2.Visible = False
End If
End If

If .Address = "$A$4" Then
If .Value <> "" Then
CommandButton3.Visible = True
Else
CommandButton3.Visible = False
End If
End If

. etc. etc. répété 150 fois !! :( misère. :(

End With
End Sub


Pouvez-vous me donner quelques conseils ? :)

Merci par avance,

Patrick.