Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

Eseguire una macro al variare di un valore in una colonna

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

Eseguire una macro al variare di un valore in una colonna

Postdi erika_i » 03/06/15 09:10

Ciao a tutti e grazie a chiunque potrà darmi un aiuto.
Sono nuova nel forum e mi scuso in anticipo per gli errori ed inesattezze che scriverò.
Il mio problema è il seguente:

in un foglio di lavoro excel (versione 2007) devo rendere automatica la seguente istruzione:
all'inserimento di un numero nella colonna B, viene scritta nella corrispondente riga della colonna U la data odierna (=quella di inserimento del numero in colonna B), e di seguito la cella viene bloccata.

Mi spiego meglio: apro il foglio excel, inserisco un numero i.e. nella cella B7, automaticamente in cella U7 appare la data odierna e viene bloccato il contenuto della cella U7.

Di seguito il mio tentativo, che ovviamente non funziona:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

Set KeyCells = Range("b5:b1000")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
Call Macro1
End If
End Sub

Con Macro1 scritta come segue:

Sub Macro1()
'
' Macro1 Macro
'

'
Range("U5:U1000").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("U5:U1000").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub


:oops:

Grazie per qualsiasi aiuto mi potrete dare.
Erika
erika_i
Utente Junior
 
Post: 12
Iscritto il: 03/06/15 08:55

Sponsor
 

Re: Eseguire una macro al variare di un valore in una colonn

Postdi Anthony47 » 03/06/15 14:47

Ciao erika_i, benvenuta nel forum.
Penso che questa versione di Sub Worksheet_Change dovrebbe fare quel che chiedi:
Codice: Seleziona tutto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
'
Set KeyCells = Range("b5:b1000")
'
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing _
    And Cells(Target.Row, "U") = "" And IsNumeric(Target.Value) Then
    ActiveSheet.Unprotect
    With Cells(Target.Row, "U")
        .Value = Int(Now)
        .Locked = True
    End With
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End Sub
Presuppone che in modo preventivo le celle del foglio siano state formattate rimuovendo, nel tab Protezione, la spunta a "Bloccata" e "Nascosta".
La macro non impedisce di modificare il contenuto delle celle Bxx, ma il contenuto della colonna Uxx viene compilato con la data odierna solo la prima volta che Bxx viene compilata con un dato numerico.
Macro1 non e' piu' necessaria.

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13891
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Eseguire una macro al variare di un valore in una colonn

Postdi erika_i » 03/06/15 15:34

Grazie Anthony,
funziona perfettamente! Grazie davvero.
Volendo bloccare anche la cella B, una volta scritta, come dovrei completare il codice?

Grazie ancora
Erika
erika_i
Utente Junior
 
Post: 12
Iscritto il: 03/06/15 08:55

Re: Eseguire una macro al variare di un valore in una colonn

Postdi ricky53 » 03/06/15 15:47

Ciao,
utilizza la stessa istruzione che Anthony ti ha proposto per bloccare le celle in colonna "U" ossia
Codice: Seleziona tutto
    Cells(Target.Row, "B").Locked = True


ATTENZIONE: va inserita nella macro "Worksheet_Change(ByVal Target As Range)" prima della protezione del foglio
Dice il vecchio saggio provare e riprovare è l'unica strada per imparare

Più chiara è la vostra spiegazione
Più immediata sarà la nostra soluzione


. . . . . . . . . .
S.O. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Eseguire una macro al variare di un valore in una colonn

Postdi erika_i » 03/06/15 16:49

Ciao ricky53 e grazie per l'aiuto.
Adesso però blocca tutte le celle della colonna B e non solo quella in cui vado a scrivere.
Ho riscritto l'istruzione come di seguito, in cosa sbaglio?
Grazie

Codice: Seleziona tutto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
'
Set KeyCells = Range("b5:b1000")
'
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing _
    And Cells(Target.Row, "U") = "" And IsNumeric(Target.Value) Then
   
        ActiveSheet.Unprotect
    With Cells(Target.Row, "U")
        .Value = Int(Now)
        .Locked = True
    End With

    With Cells(Target.Row, "B")
        .Locked = True
    End With
   
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
   
End If
erika_i
Utente Junior
 
Post: 12
Iscritto il: 03/06/15 08:55

Re: Eseguire una macro al variare di un valore in una colonn

Postdi Anthony47 » 03/06/15 21:15

La modifica proposta da Ricky va a bloccare la sola cella in B5:B1000 che viene scritta, anche nella versione complicata da erika_i.
Se "blocca tutte le celle della colonna B e non solo quella in cui vado a scrivere" deve esserci un' altra ragione; ad esempio potresti non aver preventivamente formattato il foglio rimuovendo la spunta a "Bloccata" e "Nascosta".

Infine invece di Cells(Target.Row, "B").Locked = True si puo' scrivere direttamente Target.Locked = True

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13891
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Eseguire una macro al variare di un valore in una colonn

Postdi erika_i » 04/06/15 11:01

Grazie Anthony, effettivamente il motivo è proprio quello.
Vi chiedo un'ultima cosa, se non è troppo...come evitare di dover togliere la spunta dalla casella "bloccata o nascosta" ogni volta che si riapre il file?
Vi spiego il problema: il file verrà utilizzato da diversi utenti, che andranno ad inserire dei valori nella colonna "b" e colonna"c" - e quindi attiveranno la macro (anzi, in realtà saranno due macro); vorrei poi bloccare le celle interessate dalle macro (sono 4 celle in tutto) con password, in modo che gli utenti non possano "cancellare" eventuali errori di inserimento nelle celle "B" e "C"; ma lasciando però sbloccate tutte le altre celle.
Vorrei in ogni caso evitare che l'utente debba, all'apertura del foglio, dover togliere la spunta dalla casella "bloccata e nascosta".
E' possibile?

Grazie davvero.

PS: di seguito il codice completo che mi avete aiutato a scrivere :)



Codice: Seleziona tutto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
'
Set KeyCells = Range("c5:c1000")
'
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing _
    And Cells(Target.Row, "V") = "" And IsNumeric(Target.Value) Then
   
        ActiveSheet.Unprotect
    With Cells(Target.Row, "V")
        .Value = Int(Now)
        .Locked = True
    End With
  Cells(Target.Row, "c").Locked = True
  Cells(Target.Row, "b").Locked = True
End If
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing _
    And Cells(Target.Row, "a") = "" And IsNumeric(Target.Value) Then
   
        ActiveSheet.Unprotect
    With Cells(Target.Row, "a")
         .FormulaR1C1 = _
        "=CONCATENATE(YEAR(RC[21]),""_"",MONTH(RC[21]),""_"",RC[1],""_"",RC[2])"
       .Locked = True
    End With
   
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
   
End Sub
erika_i
Utente Junior
 
Post: 12
Iscritto il: 03/06/15 08:55

Re: Eseguire una macro al variare di un valore in una colonn

Postdi Anthony47 » 04/06/15 14:03

in modo che gli utenti non possano "cancellare" eventuali errori di inserimento nelle celle "B" e "C"
Alquanto sadico.. e anche un po' masochista visto che ti ritroverai a gestire dati la cui esattezza la garantisce babbo natale.

Comunque se prepari il file con le celle non bloccate e non nascoste e poi lo distribuisci agli utenti, nessuno dovra' ritogliere la spunta a quelle impostazioni; se questo succede e' perche' c'e' dell'altro che modifica la formattazione del foglio.

Per favore quando inserisci nei messaggi codice o formule usa il "tag" Code: Selezioni le righe di codice, premi il tasto Code che trovi in testa al box in cui stai scrivendo il tuo messaggio. La lettura del codice e del messaggio risultera' piu' agevole.

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13891
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Eseguire una macro al variare di un valore in una colonn

Postdi erika_i » 04/06/15 14:55

:) è un sadismo imposto...
scusami se ho sbagliato a riportare il codice, lezione imparata :)

in ogni caso, il codice che ho scritto non funziona. Si blocca un po' tutto indiscriminatamente...mentre quello che vorrei bloccare io sono le celle delle colonne "A", "B", "C" e "V" della riga corrispondente alla cella "C" modificata.
E non tutto il resto (il che costringe, ad ogni insermento, di sbloccare il foglio, rendendo inutile la macro..)

non so proprio cosa sia sbagliato, in un primo momento sembrava funzionare.
Qualcuno sa aiutarmi?
Riporto di seguito il codice:

Codice: Seleziona tutto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
'
Set KeyCells = Range("c5:c1000")
'
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing _
    And Cells(Target.Row, "V") = "" And IsNumeric(Target.Value) Then
   
        ActiveSheet.Unprotect
    With Cells(Target.Row, "V")
        .Value = Int(Now)
        .Locked = True
    End With
  Cells(Target.Row, "c").Locked = True
  Cells(Target.Row, "b").Locked = True
End If
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing _
    And Cells(Target.Row, "a") = "" And IsNumeric(Target.Value) Then
   
        ActiveSheet.Unprotect
    With Cells(Target.Row, "a")
         .FormulaR1C1 = _
        "=CONCATENATE(YEAR(RC[21]),""_"",MONTH(RC[21]),""_"",RC[1],""_"",RC[2])"
       .Locked = True
    End With
   
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
end sub



Grazie e scusatemi :( ma sono davvero imbranata.
erika_i
Utente Junior
 
Post: 12
Iscritto il: 03/06/15 08:55

Re: Eseguire una macro al variare di un valore in una colonn

Postdi Anthony47 » 05/06/15 00:49

In genere quando mi consultano per avere una valutazione di un processo e trovo una situazione come questa (l' utente non e' autorizzato a correggere dati introdotti) io commino 5 frustate a chi ha avuto questa bella idea; e mi pagano pure.
Cio' detto (detto invano, immagino):
-ho creat un nuovo file
-ho impostato tutte le celle "non bloccate e non nascoste"
-ho inserito la tua (vedi nota **) Private Sub Worksheet_Change all' interno del Foglio1
-ho protetto Foglio1
Tutte le celle risultano liberamente compilabili

Compilando a caso celle non in C5:C1000 la situazione non cambia

Compilata la cella C6 la Private Sub Worksheet_Change e' intervenuta inserendo la data in V6 e una formula in A6, e bloccando le celle A6, B6, C6, V6; le altre celle di riga sono sempre liberamente compilabili

Compilata la cella C10 il comportamento e' ancora come descritto per C6.

Nota** ho eliminato un End If e il successivo If, poiche' superflui.

C'e' tuttavia un comportamento potenzialmente pericoloso, legato all' eventuale Copia /Incolla di blocchi di piu' celle nell' intervallo C5:C1000: in questo caso il test IsNumeric(Target.Value) risulta Falso e inibisce l' intervento della Private Sub Worksheet_Change; che pero' interviene se si cancellano "singolarmente" le celle incollate, con blocco delle colonne A, B, C, V (sulla riga cancellata).

Ho pertanto inserito un Foglio2 su cui ho variato il codice della Private Sub Worksheet_Change:
a) per gestire la modifica contemporanea di piu' celle in C5:C1000
b) per evitare che la macro si attivi su celle vuote (anche se dovrebbe essere superfluo dopo la modifica del punto a)
Il codice della Sub Worksheet_Change di Foglio2 e:
Codice: Seleziona tutto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range, Cella As Range
'
Set KeyCells = Range("c5:c1000")
'
For Each Cella In Target
    If Not Application.Intersect(KeyCells, Cella) Is Nothing _
        And Cells(Cella.Row, "V") = "" And IsNumeric(Cella.Value) And Cella.Value <> "" Then
       
            ActiveSheet.Unprotect
        With Cells(Cella.Row, "V")
            .Value = Int(Now)
            .Locked = True
        End With
      Cells(Cella.Row, "c").Locked = True
      Cells(Cella.Row, "b").Locked = True
    'End If
    'If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing _
        And Cells(Target.Row, "a") = "" And IsNumeric(Target.Value) Then
       
    '        ActiveSheet.Unprotect
        With Cells(Cella.Row, "a")
             .FormulaR1C1 = _
            "=CONCATENATE(YEAR(RC[21]),""_"",MONTH(RC[21]),""_"",RC[1],""_"",RC[2])"
           .Locked = True
        End With
       
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If
Next Cella
End Sub

Ho eliminato Bloccato e Nascosto, e ho protetto il foglio.

Ho compilato liberamente le celle A2:A4 con valori numerici. Poi ho copiato questo blocco e l' ho incollato in C4:C6; le date sono state inserite in V5:V6, le formule in A5:A6; le celle A5, B5, C5, V5 e A6, B6, C6, V6 sono bloccate e le altre sono liberamente compilabili.

Un file di esempio, allineato a quanto descritto, e' disponibile qui:
https://www.dropbox.com/s/ahddok349d014 ... .xlsm?dl=0

Insomma io non vedo quel blocco a caso di cui tu parlavi; anche con la macro originale a venire bloccate erano sempre le celle della riga su cui si variava la cella C.
Se hai sempre un esito diverso allora descrivi i passi che fai per consentirmi di venirti dietro.

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13891
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Eseguire una macro al variare di un valore in una colonn

Postdi erika_i » 05/06/15 09:45

Ciao Anthony e grazie davvero per la disponibilità e pazienza.
Ho provato a fare come hai provato tu, creando un file nuovo, togliendo la spunta bloccato/nascosto dal foglio, copiato il tuo codice paro paro, protetto il foglio.
Però non riesco a scrivere in colonna "C" perchè esce il messaggio: rimuovi protezione foglio.

A questo punto mi sa che tenterò la strada da te indicatami...ovvero far passare l'idea del blocco celle come cretina!

Grazie ancora per la disponibilità
Erika
erika_i
Utente Junior
 
Post: 12
Iscritto il: 03/06/15 08:55

Re: Eseguire una macro al variare di un valore in una colonn

Postdi Anthony47 » 05/06/15 14:27

Ho provato a fare come hai provato tu, creando un file nuovo, togliendo la spunta bloccato/nascosto dal foglio, copiato il tuo codice paro paro, protetto il foglio.
Però non riesco a scrivere in colonna "C" perchè esce il messaggio: rimuovi protezione foglio
C'e' qualche cosa che fai male, perche' se togli la spunta a "Bloccata" e "Nascosta" allora quelle celle non sono protette, per definizione.
Secondo me quando applichi quella formattazione non hai selezionato tutte le celle del foglio. Il modo piu' semplice e' di cliccare il quadratino che si trova sopra la numerazione di righe / a sinistra della numerazione di colonna, e tutto il foglio sara' visibilmente selezionato; a quel punto: tasto dx, scegli Formato celle, selezioni il tab Protezione e togli le spunte "Bloccata" e "Nascosta".

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13891
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Eseguire una macro al variare di un valore in una colonn

Postdi erika_i » 08/06/15 08:33

Ciao Anthony, faccio proprio come descrivi per togliere la spunta bloccata/nascosta.
Forse il problema nasce quando proteggo il foglio alla fine della procedura?
erika_i
Utente Junior
 
Post: 12
Iscritto il: 03/06/15 08:55

Re: Eseguire una macro al variare di un valore in una colonn

Postdi Anthony47 » 08/06/15 22:33

Dando per scontato che Microsoft rispetti le sue specifiche su come effettuare la protezione celle, se alleghi il file con cui hai fatto le tue prove potremo spiegarci meglio l'arcano; per come fare vedi viewtopic.php?f=26&t=103893&p=605487#p605487
Oppure usa il file su cui ho fatto io le prove con esito diverso dal tuo (vedi viewtopic.php?f=26&t=104771&p=611584#p611480)

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13891
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Eseguire una macro al variare di un valore in una colonn

Postdi erika_i » 09/06/15 09:11

Ciao Anthony, credo che il problema sia legato al fatto che io il foglio lo blocco con password.
Perchè lo scopo finale sarebbe che impedire all'utente di rimuovere la protezione foglio.
...si tratta sempre di sadismo imposto.
:lol:
erika_i
Utente Junior
 
Post: 12
Iscritto il: 03/06/15 08:55

Re: Eseguire una macro al variare di un valore in una colonn

Postdi Anthony47 » 09/06/15 12:51

Se lavori con una macro costruita da te partendo da quella che ti ho suggerito allora confermo che solo guardando al tuo file e alla tua macro si potra' chiarire l'arcano.

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13891
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Eseguire una macro al variare di un valore in una colonn

Postdi erika_i » 09/06/15 15:13

Ciao Anthony e grazie per l'attenzione.
Non mi sono spiegata bene: io ora sto proprio lavorando sul file che mi hai inviato tu. Non ho toccato la macro, semplicemente nel momento di proteggere il foglio, ho aggiunto una password.
Di conseguenza non riesco più ad inserire valori in colonna C (a meno di non inserire la password per lo sblocco).
erika_i
Utente Junior
 
Post: 12
Iscritto il: 03/06/15 08:55

Re: Eseguire una macro al variare di un valore in una colonn

Postdi Anthony47 » 09/06/15 23:24

Nel file che ho pubblicato, anche inserendo una password il comportamento rimane quello da me descritto.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13891
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Eseguire una macro al variare di un valore in una colonn

Postdi erika_i » 15/06/15 08:18

Ciao Anthony,
scusa se rispondo solo ora. Il problema persiste: se inserisco una password nella maschera "proteggi il foglio di lavoro", e poi tento di inserire un dato in Colonna C”, mi compare la maschera “ Rimuovi Protezione foglio”.
erika_i
Utente Junior
 
Post: 12
Iscritto il: 03/06/15 08:55

Re: Eseguire una macro al variare di un valore in una colonn

Postdi Anthony47 » 16/06/15 01:49

Non so come dirtelo, ma se a me funziona e a te no allora serve esaminare il tuo file...
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13891
Iscritto il: 21/03/06 16:03
Località: Ivrea

Prossimo

Torna a Applicazioni Office Windows


Topic correlati a "Eseguire una macro al variare di un valore in una colonna":


Chi c’è in linea

Visitano il forum: Nessuno e 4 ospiti