XL 2010 Déplacer liste tri validation de données

[Orson83]

Bonsoir le forum XLD,
Je vous sollicite concernant le tri et le déplacement d'une liste de validation de données. En effet, une macro permet de copier la liste de la feuille "Facture > J5" dans la colonne A et la formule dans la validation de données organise cela :

=INDIRECT("A1:A"&MAX(1;NBVAL(A:A)))

INDIRECT gère un tableau structuré (à supprimer si pas nécessaire).

Lorsque que la feuille n'est pas protégée, tout fonctionne correctement.
Néanmoins, quand la feuille est protégée (même si les cellules sont déverrouillées), un message d'erreur apparait dans VBE.

Mes questions sont les suivantes :
Est-il possible de faire fonctionner cette macro en mode "Feuille protégée ? (MDP="" vide)
... et peut-on déplacer l'affichage en colonne AZ par exemple ou idéalement, laisser l'affichage en colonne A, mais non visible ?
Merci pour votre aide. Je joins un fichier exemple dans ce post.
PS : merci @job75 d'avoir proposé cette macro sur le thread "Faire communiquer 2 tableaux structurés sur 2 feuilles"
Bonne soirée

 

[laurent950]

Bonsoir,
A ) peut-on déplacer l'affichage en colonne AZ par exemple :
modifier With Columns(1) par With Columns(52)
ou
B ) idéalement, laisser l'affichage en colonne A, mais non visible ?
Ajouter au code With Columns(1) cette ligne de code : .NumberFormat = ";;;"

With Columns(1)
    .Clear
    [TabPL].Columns(1).Copy
    .Cells(1).PasteSpecial xlPasteValues ' Coller les valeurs uniquement
    .Sort .Cells, xlAscending, Header:=xlNo 'tri
    .NumberFormat = ";;;" 
End With

Et
C ) Est-il possible de faire fonctionner cette macro en mode "Feuille protégée ? (MDP="" vide)
Worksheets(ActiveSheet.Name).Unprotect
[ °°° Votre Code °°° ] Ici le code B ci-dessus
Worksheets(ActiveSheet.Name).Protect

 

[Orson83]

Bonsoir,

Pour afficher en colonne AZ modifier With Columns(1) par With Columns(52)

Bonsoir @laurent950, le fil,
Merci pour cette information, reste plus qu'à régler le pb de feuille protégée
Bonne soirée.

 

[Orson83]

Bonsoir,
peut-on déplacer l'affichage en colonne AZ par exemple :
modifier With Columns(1) par With Columns(52)
ou
idéalement, laisser l'affichage en colonne A, mais non visible ?
Ajouter au With Columns(1) la ligne ci-dessous
.NumberFormat = ";;;"

@laurent950, le fil,
Pour le déplacement, c'est OK
Pour le format, cela ne fonctionne pas, car il me semble que la fonction .copy de la macro ignore cet argument et copie la colonne telle quelle (je me trompe peut-être )

 

[Phil69970]

Bonjour à tous

Je te propose ce fichier

 

[gbinforme]

bonjour,

voici ton classeur corrigé avec AZ et protection feuille

 

[Orson83]

C'est a dire :
laisser l'affichage en colonne A, mais non visible ?

La liste générée doit être invisible (cellules blanches).
Je vais consulter les réponses de Phil et gbinforme et vous faire un retour dans 30 minutes.

 

[laurent950]

Peut être comme cela suite a votre Poste #1

Option Explicit

Private Sub Worksheet_Activate()
Worksheets(ActiveSheet.Name).Unprotect
Range("J5").Locked = False ' Déverouille la liste déroulante en J5
With Columns(1)
    .Clear
    [TabPL].Columns(1).Copy
    .Cells(1).PasteSpecial xlPasteValues ' Coller les valeurs uniquement
    .Sort .Cells, xlAscending, Header:=xlNo 'tri
    .NumberFormat = ";;;"
End With
Worksheets(ActiveSheet.Name).Protect
End Sub

 

[Orson83]

Bonjour à tous

Je te propose ce fichier

Bonsoir @Phil69970, le fil,
Merci pour cette proposition qui fonctionne parfaitement
Je vais tout de même opter pour la proposition de @laurent950 qui a inclus la fonction qui permet de changer le format des cellules avec :

    .Cells(1).PasteSpecial xlPasteValues ' Coller les valeurs uniquement

Du fait, avec le code ci-dessus, .NumberFormat = ";;;", fonctionne et toute la colonne est modifiée au format personnalisé, donc invisible.
Bonne soirée.

 

[Orson83]

bonjour,

voici ton classeur corrigé avec AZ et protection feuille

Bonsoir @gbinforme, le fil,
Merci pour cette proposition qui fonctionne parfaitement
Je vais tout de même opter pour la proposition de @laurent950 qui a inclus la fonction qui permet de changer le format des cellules avec :

    .Cells(1).PasteSpecial xlPasteValues ' Coller les valeurs uniquement

Du fait, avec le code ci-dessus, .NumberFormat = ";;;", fonctionne et toute la colonne est modifiée au format personnalisé, donc invisible.
Bonne soirée.

 

[Orson83]

Peut être comme cela suite a votre Poste #1

Option Explicit

Private Sub Worksheet_Activate()
Worksheets(ActiveSheet.Name).Unprotect
Range("J5").Locked = False ' Déverouille la liste déroulante en J5
With Columns(1)
    .Clear
    [TabPL].Columns(1).Copy
    .Cells(1).PasteSpecial xlPasteValues ' Coller les valeurs uniquement
    .Sort .Cells, xlAscending, Header:=xlNo 'tri
    .NumberFormat = ";;;"
End With
Worksheets(ActiveSheet.Name).Protect
End Sub

Bonsoir @laurent950, le fil,
Merci pour cette proposition qui correspond en tous points à ma demande initiale
J'ai juste supprimé la ligne :

Range("J5").Locked = False ' Déverouille la liste déroulante en J5

... qui agissait sur la cellule J5 alors qu'elle est déjà déverrouillée par défaut pour permettre l'accès à ma liste déroulante, mais c'est un détail.
Bonne soirée.