blocca e sblocca più fogli di lavoro con l'istruzione di VBA.

[tempusfugitPartecipante tempusfugit]

Buonasera, ho

creato una macro che mi crea una copia di sicurezza di un file e poi mi blocca anche la copia di backup, vorrei sapere come faccio a dirgli quali fogli mi deve bloccare e quali no?

Option Explicit

Sub Pulisci()
Application.ScreenUpdating = False
Dim Prova
    
    ActiveSheet.Unprotect ("pippo01")
    ActiveSheet.Protect ("pippo01")
    Prova = MsgBox("ATTENZIONE TUTTI I DATI SARANNO CANCELLATI!! Vuoi proseguire ?", vbYesNo)
        If Prova = vbYes Then
        
    Worksheets("Viaggi").Copy After:=Worksheets("Viaggi")
    
    ActiveSheet.Unprotect ("pippo01")
    
    Range("D9:D40").Select
    Selection.ClearContents
    Range("E9:E14").Select
    Selection.ClearContents
    Range("E16").Select
    Selection.ClearContents
    Range("E18:E22").Select
    Selection.ClearContents
    Range("E24:E27").Select
    Selection.ClearContents
    Range("E29:E30").Select
    Selection.ClearContents
    Range("E32").Select
    Selection.ClearContents
    Range("E34").Select
    Selection.ClearContents
    Range("E36").Select
    Selection.ClearContents
    Range("E38").Select
    Selection.ClearContents
    Range("E40").Select
    Selection.ClearContents
    Range("F9:F40").Select
    Selection.ClearContents
     
    Range("H9:H14").Select
    Selection.ClearContents
    Range("H16").Select
    Selection.ClearContents
    Range("H18:H22").Select
    Selection.ClearContents
    Range("H24:H27").Select
    Selection.ClearContents
    Range("H29:H30").Select
    Selection.ClearContents
    Range("H32").Select
    Selection.ClearContents
    Range("H34").Select
    Selection.ClearContents
    Range("H36").Select
    Selection.ClearContents
    Range("H38").Select
    Selection.ClearContents
    Range("H40").Select
    Selection.ClearContents
    Range("I9:I40").Select
   
    Selection.ClearContents
    Range("K9:K14").Select
    Selection.ClearContents
    Range("K16").Select
    Selection.ClearContents
    Range("K18:K22").Select
    Selection.ClearContents
    Range("K24:K27").Select
    Selection.ClearContents
    Range("K29:K30").Select
    Selection.ClearContents
    Range("K32").Select
    Selection.ClearContents
    Range("K34").Select
    Selection.ClearContents
    Range("K36").Select
    Selection.ClearContents
    Range("K38").Select
    Selection.ClearContents
    Range("K40").Select
    Selection.ClearContents
    Range("L9:L40").Select
    Selection.ClearContents
     
    Range("N9:N14").Select
    Selection.ClearContents
    Range("N16").Select
    Selection.ClearContents
    Range("N18:N22").Select
    Selection.ClearContents
    Range("N24:N27").Select
    Selection.ClearContents
    Range("N29:N30").Select
    Selection.ClearContents
    Range("N32").Select
    Selection.ClearContents
    Range("N34").Select
    Selection.ClearContents
    Range("N36").Select
    Selection.ClearContents
    Range("N38").Select
    Selection.ClearContents
    Range("N40").Select
    Selection.ClearContents
    Range("O9:O40").Select
    
    Range("Q9:Q14").Select
    Selection.ClearContents
    Range("Q16").Select
    Selection.ClearContents
    Range("Q18:Q22").Select
    Selection.ClearContents
    Range("Q24:Q27").Select
    Selection.ClearContents
    Range("Q29:Q30").Select
    Selection.ClearContents
    Range("Q32").Select
    Selection.ClearContents
    Range("Q34").Select
    Selection.ClearContents
    Range("Q36").Select
    Selection.ClearContents
    Range("Q38").Select
    Selection.ClearContents
    Range("Q40").Select
    Selection.ClearContents
    Range("R9:R40").Select
    Selection.ClearContents
    
    Range("T9:T14").Select
    Selection.ClearContents
    Range("T16").Select
    Selection.ClearContents
    Range("T18:T22").Select
    Selection.ClearContents
    Range("T24:T27").Select
    Selection.ClearContents
    Range("T29:T30").Select
    Selection.ClearContents
    Range("T32").Select
    Selection.ClearContents
    Range("T34").Select
    Selection.ClearContents
    Range("T36").Select
    Selection.ClearContents
    Range("T38").Select
    Selection.ClearContents
    Range("T40").Select
    Selection.ClearContents
    Range("U9:U40").Select
    Selection.ClearContents
    
    Range("W9:W14").Select
    Selection.ClearContents
    Range("W16").Select
    Selection.ClearContents
    Range("W18:W22").Select
    Selection.ClearContents
    Range("W24:W27").Select
    Selection.ClearContents
    Range("W29:W30").Select
    Selection.ClearContents
    Range("W32").Select
    Selection.ClearContents
    Range("W34").Select
    Selection.ClearContents
    Range("W36").Select
    Selection.ClearContents
    Range("W38").Select
    Selection.ClearContents
    Range("W40").Select
    Selection.ClearContents
    Range("X9:X40").Select
    Selection.ClearContents
    ActiveSheet.Protect ("pippo01")
    
        End If
       
Application.ScreenUpdating = True


End Sub

[alfrimpaPartecipante alfrimpa Karma: 33 pts]

Non c’è bisogno di usare tutti quei select e selection.

Si può scrivere direttamente ad esempio

Range(“A1”).ClearContents

Per la protezione dei fogli puoi impostare un ciclo For che scansioni i fogli della cartella e protegga o meno il foglio in base al nome dello stesso.

[Marius44Moderatore Marius44 Karma: 58 pts]

Ciao

Se ho capito bene, devi creare un ciclo che spazzoli tutti i Fogli e se il nome del Foglio è ="pippo01" allora non bloccarlo (ovviamente vedi tu quali sono i fogli che non vuoi bloccare).  

 

Un'altra cosa. Noto che per cancellare i dati nei vari intervalli utilizzi

Range("D9:D40").Select

Selection.ClearContents

Range("E9:E14").Select

Selection.ClearContents

Range("E16").Select

Selection.ClearContents

eccetera, eccetera ......

Premesso che non è necessario selezionare l'intervallo per cancellarne il contenuto, potresti (ho preso le prime tre righe per farti l'esempio) fare così:

Range("D9:D40,E9:E14,E16").ClearContents

 

Ciao,

Mario

 

 

Edit: Alfredo è stato più veloce di me.  

[alfrimpaPartecipante alfrimpa Karma: 33 pts]

Mario two is megl che one   

[Autore]

Articoli