facturation excel base client et base fruit
exemple de facture de démonstration
les rectangles bleus sont affectés à des procédures VBA ou Macro
Matrice Facturation
exemple de base de données
BDD 01 base de données fruits
BDD 02 base de données clients
BDD 03 liste des factures en cours
Macro VBA
macro qui efface tout plage de données de B10 à D19 feuille facturation
on peut ajouter
Sheets("facture").Select
Range("A1").Select par exemple pour affiner la procédure sub erase
gérer les comptes bancaires sous excel VBA devis facture
exemple de formulaire de login et de mot de passe non affiché
option envoi de devis et facture en PDF, stockage dans un dossier local et envoi par mail
commande écrire dans une cellule deux méthodes
code vba qui affiche ou n’affiche pas le mot de passe en fonction de la case cochée
Code VBA pour la case à cochée
Connexion avec autorisation administrateur ou utilisateur simple
VBA pour contrôler les autorisations de l’utilisateur avant la connexion
user form super admin
Gestion des autorisations en mode connexion userform
le bouton autorisation contrôle le mot de passe saisi
si le mot de passe administrateur est ok
on peut faire des recherches sur un utilisateur, son mot de passe et son rôle
la case à cocher Administrateur est cochée et désactivée (grisée)
Affichage du statut administrateur (tous les droits)
ou Affichage du statut élève (moins de droits)
bouton afficher ou masquer et propose un mot de passe pour déverrouiller des feuilles de F1 à F10
zone avec onglets de feuilles d’excel avec verrouillage
zone avec onglets de feuilles d’excel sans verrouillage
code VBA pour afficher ou masquer et changer le contenu du bouton
Inputbox pour saisir le mot de passe
protection ou dé protection des feuilles de F1 à F10
voir pour positionner un user form à tel endroit dans excel
Espace login pour les utilisateurs excel
dans l’exemple l’interface propose une seule feuille Logins
en fonction du mot de passe vous avez accès ou pas d’accès
possibilité d’étendre des droits administrateurs ou utilisateurs normaux
rôle admin ou user
interface avec mot de passe par exemple 123456789 (démonstration pédagogique)
interface sans autorisation
interface avec autorisation sur toutes les feuilles
interface avec autorisation partielle sur les feuilles
ou
exemple de saisie de mot de passe pour débloquer l’interface
si le mot de passe ok alors on affiche le texte Bravo
sinon les pages sont bloquées et on affiche Vous n’êtes pas autorisé
seule la page Logins est disponible
Contrôle du mot de passe et procédure masquer ou afficher les feuilles souhaitées
on aurait pu stocker le mot de passe dans une feuille masquée ou dans un worksbook_open()
suite de la procédure qui finit par End Sub
pour masquer le mot de passe utilisez la variable * avec la propriété PasswordChar
Si le mot de passe n’est pas le bon alors les feuilles sont bloquées (procédure avec le bouton Validation)
fin de la procédure vérification du mot de passe
Les feuilles sont masquées sauf la feuille Logins
Si le mot de passe est saisi correctement alors les feuilles sont débloquées
Boite de message ou msgbox
Le mot est correct les feuilles sont affichées
On peut améliorer la procédure, en contrôlant le login et le mot de passe
si les deux variables correspondent à la base de données alors on affiche sinon on vérouille
et on ferme excel sans enregistrer et on envoi un mail pour informer d’une ouverture douteuse par exemple.
Amélioration possibles avec les rôles d’une base de données : login / password / rôles
De plus si l’utilisateur n’est pas administrateur il a seulement accès aux feuilles autorisées par l’administrateur de l’application
Exemple Administrateur DELAPORTE avec le mot de passe 123456789
on peut ajouter une case à cocher se connecter en tant que administrateur
on vérifier le login et le mot de passe ainsi que les droits admin et la case à cocher si ok on autorise ….
dans cet exemple, on autorise les feuilles
lors du contrôle du mot de passe les pages sont bloquées, on affiche le texte Bravo et on ouvre les autorisations …
Les feuilles sont à nouveau disponibles
mot de passe masqué (on peut mettre *, !, – , +, @ etc …
en 0 on n’a le nom de la TextBox soit soit saisie_mot_de_passe.TextBox
en 1 le type d’affichage de la saisie (ici on n’a des étoiles en cours de saisie) soit saisie_mot_de_passe.PasswordChar
en 2 on affiche un texte par défaut pour informer soit text.value
en 3 on à le contenu de la saisie soit saisie_mot_de_passe.value
Les deux triangles Vert et Rouge permettent d’afficher ou de masquer la feuille utilisateurs
Amélioration possible du formulaire de connexion
création de menu en VBA avec un userform (pédagogie en cours de création)
exemple de userform en vba avec 4 boutons
Boite de dialogue avec message Hello MSGBOX
utilisation de la procédure show et hide sur le userform2
en cliquant sur ok le formulaire ne s’affiche plus
ou par exemple avec ce message de fermeture
en cliquant sur le bouton menu en bleu, on affiche le menu avec les boutons janvier février mars etc …
En cliquant sur les boutons, on atteint la feuille souhaitée ou on peut fermer le formulaire
code source vba
code pour afficher le menu
affichage du userform2
code pour atteindre la feuille janvier à l’aide du bouton Janvier nommé btn01
code pour atteindre la feuille février
code pour atteindre la feuille mars
code pour le bouton fermer du formulaire menu calendrier
comment afficher le formulaire sous excel
menu formulaire fini
zone d’onglets feuilles de janvier à décembre)
couleur de l’onglet dans excel
Par défaut, la couleur des onglets des feuilles de calcul Excel est grise. Pour changer la couleur d’un onglet, un clic droit permet d’accéder à la palette de couleur :
Cette action manuelle peut également être réalisée par programmation en VBA à l’aide de l’instruction .Tab.
Par exemple, le code suivant change la couleur de l’onglet de la première feuille de calcul (en partant de la gauche) en vert :With ActiveWorkbook.Sheets(1).Tab .Color = 5296274 .TintAndShade = 0 End With
Les couleurs peuvent être codifiées à partir de la fonction RGB (pour Rouge / Vert / Bleu) ; par exemple : RGB(0, 255, 0).
paramètres complémentaires des couleurs d’onglets ou propriétés
Les paramètres de l’instruction .Tab sont les suivants :
exemple de classeur avec plusieurs onglets de feuille
si j’ajoute une feuille dans le classeur
la feuille active est colorée en vert clair
les autres couleurs ne changent pas
code vba à mettre en place dans la rubrique ThisWorkbook
c’est une procédure à l’activation de la feuille et la désactivation de la feuille.
code source faire un copier coller
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.Names.Add “CoulTab”, Sh.Tab.Color
Sh.Tab.Color = &HFF00&
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
On Error Resume Next
If Sh.Tab.Color <> &HFF00& Then
Sh.Names.Add “CoulTab”, Sh.Tab.Color
Else
Sh.Tab.Color = Sh.[CoulTab]: End If
End Sub
code pour afficher ou masquer un user form
29-10-2023 DELAPORTE LAURENT
exercice 001 chiffre aléatoire sur un plage de 0 à 100 avec liste déroulante et un rond bleu pour faire apparaître les chiffres de façon aléatoire
exemple d’aperçu avec excel 2013 et office 365
Consignes
réaliser un procédure vba qui permet d’afficher un chiffre entre 0 et 10, 0 et 20, 0 et 30 etc
par la suite mettre une variable maximale à l’aide d’une liste déroulante
en gros
si je choisit la valeur 40 dans la liste déroulante, en appuyant sur le rond bleu
j’aurai des chiffres aléatoires compris entre 0 et 40
si je choisit la valeur 20 dans la liste déroulante, en appuyant sur le rond bleu
j’aurai des chiffres aléatoires compris entre 0 et 20
Correction VBA procédure
insérer une liste déroulante à l’aide du ruban Développeur, puis insérer et choisir le deuxième dessin avec une liste déroulante (Contrôles de formulaires dessin numéro 2 sur la ligne 1)
insérer la liste déroulante ou vous le souhaitez et modifier les paramètres de la liste déroulante
clic droit souris format de contrôle sur la liste déroulante
propriété de la liste déroulante (format de contrôle de la liste déroulante)
la plage d’entrée constitue l’ensemble de variable que l’on veut afficher
par exemple les chiffres de 1 à 100 à l’aide de la plage de cellule Q2 à Q102
la cellule liée correspond à la cellule M40 dans notre exemple
elle correspond au numéro de la valeur choisi dans la liste déroulante
exemple vous avez trois mot un cochon un chien et un chat
le cochon va renvoyer le chiffre 1
le chien va renvoyer le chiffre 2
et le chat va renvoyer le chiffre 3 et ainsi de suite…
si je choisis la valeur 100 j’aurai le chiffre 100 dans la cellule M2 ou le chiffre 40 si je choisit la valeur 40
plage avec la liste des variables cellule colonne Q2 a Q102 par exemple
dans la procédure j’utilise des valeurs absolues et relatives
A1 est une valeur absolue
R(-3) C(11) est un valeur relative
Il ne faut pas oublier d’insérer un bouton ou une image qu’on affecte à la macro qu’on n’a créer (chiffre aléatoire)
il faut sélectionner l’image ou le rond bleu et faire clic droit souris et affecter à une macro
on choisit le nom de la macro par exemple nombrealeatoire ou lolo
déroulez la liste de déroulante pour faire apparaître le nom de la macro et cliquez sur le bouton ok
site web pour avoir les fonctions traduites en anglais selon la version de l’office 365 fr ou office 365 us
contenu suivant à ne pas prendre en compte exercice sur la recherchev() ou randbetween()
contenu de la macro avec valeur relative pour la fonction recherchev en anglais
je recherche le contenu d’une cellule à l’aide d’un tableau ou base de données et j’affiche le contenu de la colonne désirée
=recherchev(1;2;3;4)
=recherchev(valeur cherchée, à partir d’une base de données; et j’affiche le contenu de la colonne x, valeur 0 ou 1 ou true ou false)
=randbetween(valeur cherchée, base de donnée, ce que je veux afficher, valeur par defaut 1 ou 0 true or false)
exercice 02
exercice 003 gagnant aléatoire
affiche le nom d’un gagnant aléatoire à partir d’une base de données A2:A9
le gagnant peut être pierre ou paul …. ou laurent ou sandrine ou emma
le bouton lancer permet d’afficher le résultat du gagnant dans la cellule C2
fonction excel
Procédure macro gagnant()
Mettre un peu de couleur
Procédure macro vba affiche le nom du gagnant
Exercice sur les doublons
dans une zone avec plusieurs cellule soit une plage de cellule, il faut supprimer tous les doublons
contenu du texte avec la procédure doublon()
Sub doublons()
‘
‘ doublons Macro
‘
Sheets(“existant”).Range(“A2”, “A” & Sheets(“existant”).Range(“A” & Rows.Count).End(xlUp).Row + 1).Clear
i = Range(“A” & Rows.Count).End(xlUp).Row
Do While i > 1
ligne = 0
On Error Resume Next
ligne = Sheets(“existant”).Columns(“A:A”).Find(what:=Range(“A” & i), After:=Sheets(“existant”).Range(“A1”), LookIn:=xlValues, LookAt:=xlWhole).Row
If ligne = 0 Then
Sheets(“existant”).Range(“A” & Rows.Count).End(xlUp).Offset(1, 0).Value = Range(“A” & i)
Else
Rows(i & “:” & i).Delete
End If
i = i – 1
Loop
‘
End Sub
aperçu en image
démonstration feuille avec plusieurs doublons,
en cliquant sur le bouton doublons on obtient le résultat suivant
Exercice atteindre la dernière ligne d’un enregistrement dans une base de données
procédure
on atteint la cellule A2 de la feuille souhaitée range(“A2”)
on atteint la dernière ligne avec une saisie (.End(xlDown)
et on se décale de une ligne vers le bas et de zéro colonne (.Offset(1,0)
contenu de la procédure VBA
On affecte la macro atteindrederniereligne() au bouton bleu
On choisit le nom de la macro (1) et on clique sur le bouton OK (2)
On peut tester la macro en appuyant sur le rond bleu
la souris va se positionner sur la cellule A12 pour effectuer un nouvel enregistrement en A12, B12.
deux codes possibles en VBA excel
