Merci de votre aide

[chris]

Bonjour à tous,
Je viens vous demander à tous un coup de main pour mener à bien mon projet.
Je suis en train de créer un style de base de données et j'aimerais un coup de pouce pour rendre
ce programme plus pratique. Et pour compliqué le tout je ne peux pas envoyer le fichier joint car
il pèse déjà plus de 5 MO.
Il y a 11 onglets masqués (via options/général/masquer les onglets) afin que les personnes qui se
serviront de la base ne puissent pas renommer les onglets. Le premier onglet qui s'apelle "Menu"
contient 10 rectangles associés à des macros pour accéder aux différents onglets et dans chaque
onglet un rectangle associé à une macro afin de revenir sur le premier onglet "Menu".

J'aimerais pouvoir faire :

1/ Ouvrir le classeur automatiquement sur l'onglet "Menu" même s'il a été enregitré et fermé à
partir d'un autre onglet.

2/ créer une macro qui sélectionne dans tous les onglets sauf dans "Menu" l'insertion d'une ligne
pour rajouter un nouveau personnel mais à l'endroit que je désire car c'est classé par ordre de
grade et alphabétique.

3/ Effectuer une sauvegarde auto en fin de journée dans un répertoire précis afin de récupérer
le classeur en cas de plantage du pc.

4/ Une formule qui me donnerait H 704 si dans la cellule de gauche le n° (7 chiffres) se termine entre 00 à 17
H 702 se termine entre 18 à 40
H 701 se termine entre 41 à 64
H 703 se termine entre 65 à 81
H 705 se termine entre 82 à 99.

5/ et enfin une macro que je lancerai à partir de l'onglet "Menu" afin de faire une recherche dans tous les onglets
suivant le nom de la personne situé en colonne B et de choisir à l'issue l'onglet désiré.

Merci à tous de vos talents et de votre patience. Je reste à votre disposition pour tous renseignements complémentaires.

Chris.

 

[tatiak]

pour le 1/ : placer dans le module ThisWorkbook la macro :

Private Sub Workbook_Open()
Worksheets("menu").Activate
End Sub

pour le 3/ : placer dans le même module :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
dim nomfichier as string
nomfichier ="c:\Mes documents\archive\sauvegarde" & Format(Now, "yyyymmdd")
ThisWorkBook.save
ThisWorkbook.saveAs(nomfichier)
End Sub

(le nomfichier est à adapter, la fin correspond à la date du jour de sauvegarde, ex : sauvegarde20041121, cet ordre dans la date permet de trier facilement les fichiers de sauvegarde)

 

[tatiak]

pour le 4/, dans B1 pour tester A1 :
=SI(ET(CNUM(DROITE(A1;2))>=18;CNUM(DROITE(A1;2))<=40);"H 702";SI(ET(CNUM(DROITE(A1;2))>=41;CNUM(DROITE(A1;2))<=64);"H 701";SI(ET(CNUM(DROITE(A1;2))>=65;CNUM(DROITE(A1;2))<=81);"H 703";SI(ET(CNUM(DROITE(A1;2))>=82;CNUM(DROITE(A1;2))<=99);"H 705";"H 704"))))

 

[chris]

Merci tatiak,
Je m'en vais teste tout cà et je te tiens au courant.
Merci encore pour tes deux réponses aussi rapides mais surtout que cela ne coupent pas les autres dans leur élan.

@ bientôt j'espère.

Chris.

 

[tatiak]

pour le 2/: je suggère de rajouter le nouveau à la suite de la liste puis de trier la nouvelle liste suivant le tri souhaité (ici tri sur colonne A puis B, par ex). En supposant que les onglets soit feuil1, feuil2, ...feuil10 :

Sub nouveaunom()
nom = InputBox("Entrez le nouveau nom :")
For i = 1 To 10
Sheets("Feuil" & i).Activate
ligne = Sheets(i).Range("A65536").End(xlUp).Row
Cells(ligne + 1, 1).Select
ActiveCell.Value = nom
Range(Cells(1, 1), Cells(ligne + 1, 10)).Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Next i
End Sub

 

[Robert]

Salut Chris, Tatiak, le forum,

Hou la, rapide et efficace le Tatiak !

Voici donc, après épuration, une proposition pour le 5/.

Public Sub cherch()
'déclaration des variable
Dim ch As String
Dim sh As Worksheet
Dim f As Long
Dim plage As Range
Dim cel As Range
ch = InputBox("Tapez le nom", "Recherche") 'définit la variable ch (boite de texte)
'boucle 1 sur tous les onglets
For Each sh In Sheets
Application.ScreenUpdating = False 'n'affiche pas le changement d'onglet
'non actif dans l'onglet menu
If sh.Name = "Menu" Then
GoTo suite 'va au prochain onglet via la balise "Suite"
End If
sh.Select 'sélectionne l'onglet
f = sh.Range("B65536").End(xlUp).Row 'définit la variable f (dernière ligne de la colonne B)
Set plage = sh.Range("B1:B" & f) 'définit la varaible plage (toutes les cellules non vides de la colonne B)
'boucle 2 sur toutes les cellules de la plage
For Each cel In plage
'condition : la cellule a la même valeur que la boite de texte
If cel.Value = ch Then
Application.ScreenUpdating = True 'affiche le changement d'onglet
sh.Activate 'active l'onglet
cel.Select 'célectionne la cellule
Exit Sub 'sort de la procédure
End If 'fin de la condition
Next cel 'prochaine cellule de la plage (boucle 2)
suite: 'balise
Next sh 'prochain onglet de la boucle 1
End Sub

À plus,

Robert

 

[jp]

Bonsoir chris, Tatiak, Robert et le Forum,

A mettre dans thisWorkbook, après un "Alt" F11 pour entrer en VBA,

Private Sub Workbook_beforesave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.Worksheets("mENU").Activate

End Sub

Cela doit fonctionner, sinon tu nous dis.

Bonne fin de week-end.

jp

 

[jp]

Re le fil

Tatiack, je n'avais pas vu ta première réponse, j'en suis désolé. Avec toutes mes excuses et bonne soirée.

jp

 

[chris]

Merci à tous,
Quelques petites précisions : Tatiak a résolu le 1, le 3 et le 4.
Pour le 2 pas encore testé mais je serais plutôt pour une macro qui sélectionne toutes les feuilles voulues d'un clic (ce qui correspond en fait à ctrl et à cliquer sur les onglets un à un) pour qu'une nouvelle ligne soit insérée au même endroit dans tous les onglets car il y a aussi des formules dans ces derniers.
Quant au 5 merci Robert mais je me suis peut être mal exprimé ou je n'ai pas été assez clair : pourrait-on modifier ton code afin que l'on puisse choisir l'onglet après la sélection du nom (si le mot est trouvé choisir l'onglet 1, 2, 3 ...) ou alors faire une recherche multi-critères (par exemple après avoir rentré le nom choisir dans une liste un des noms des onglets présents dans le classeur). En esperant avoir été plus clair, je vous remercie tous les deux pour vos solutions qui m'ont bien fait avancer. Merci encore.
@+

Chris.

 

[chris]

Encore un petit truc : il y a t'il un moyen de forcer les utilisateurs du classeur de quitter via un bouton dans la feuille menu et uniquement par ce bouton.
Sans bien sûr que cela n'affecte la macro d'enregistrement automatique.
Merci.

Chris.

 

[Robert]

Re Chris, Tatiak, Jp, le forum,

Pour le dernier point (6/), je pense que ce que tu voudrais c'est que le classeur soit toujours fermé sur la feuille "Menu". Si c'est cela, tu pourrais simplement rajouter la ligne en gras à la macro de Tatiak :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Menu").Select
dim nomfichier as string
nomfichier ="c:\Mes documents\archive\sauvegarde" & Format(Now, "yyyymmdd")
ThisWorkBook.save
ThisWorkbook.saveAs(nomfichier)
End Sub

Ensuite pour le point 5/ je t'envoie un exemple en pièce jointe que tu devras adapter.
Il utilise un bouton qui lance un UserForm contenant deux ComboBox.
La première te propose tous les noms du classeur (sans doublons). Tu sélectionnes un Nom et alors la deuxième te propose tous les onglets dans lequel ce nom apparaît. Tu sélectionnes l'onglet et tu valides...

À plus,

Robert

 

[chris]

Merci à toi Robert,
Concernant le 5 tu as vraiment compris ce que je voulais : c'est exactement ce que je cherchais et il me reste plus qu'à l'adapter sur mon classeur.
Quant au 6 l'idée est bonne mais je voulais surtout désactiver la croix rouge en haut à droite, la commande alt + F4 et la commande "quitter" du menu fichier : c'est à dire que l'utilisateur soit vraiment obligé de quitter par un bouton sur l'onglet "Menu". J'espère avoir été explicite et en attendant de te lire à nouveau, je te souhaite une excellente soirée.

@+

Chris.

 

[Robert]

Re Chris, bonsoir le forum,

Je te propose cette macro à la fermeture du classeur, adapte le nom du bouton dans le message.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "vous devez utiliser le bouton " & Chr(34) & "Sortir" & Chr(34) & " pour fermer de ce classeur !"
Sheets("Menu").Select
Cancel = True
End Sub

À plus,

Robert

 

[Robert]

Re...

Tu auras rectifié toi-même, non pas de ce classeur mais ce classeur suffira...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "vous devez utiliser le bouton " & Chr(34) & "Sortir" & Chr(34) & " pour fermer ce classeur !"
Sheets("Menu").Select
Cancel = True
End Sub

À plus,

Robert

 

[Robert]

Re, re, re...

Erreur grave ! J'ai oublié quelques truc car dans l'état actuel tu n'en sortiras jamais de ton classeur...

1/ en tout début d'un module rajoute cette délcaration publique de variable :

Public test As Boolean

2/ dans le code du bouton de l'onglet menu rajoutes ces deux ligne à la fin:

test = True
ActiveWorkbook.Close

3/ remplace la dernière macro que je t'ai donné par celle-ci :
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If test = True Then Exit Sub
test = False
MsgBox "vous devez utiliser le bouton " & Chr(34) & "Sortir" & Chr(34) & " pour fermer ce classeur !"
Sheets("Menu").Select
Cancel = True
End Sub

Maintenant, ça devrait marcher...

À plus,

Robert