Determina se l’utente sta aggiungendo o eliminando le righe

Ho una macro VBA che convalida i dati immessi dall’utente (non ho usato la convalida dei dati / la formattazione condizionale di proposito).

Sto usando l’evento Worksheet_Change per triggersre il codice, il problema che sto affrontando ora è, quando ci sono cambiamenti di riga. Non so se si tratta di eliminare / inserire righe.

C’è comunque da distinguere tra questi due?

È ansible definire un nome di intervallo come RowMarker =$A$1000

Quindi questo codice sul tuo evento di modifica memorizzerà la posizione di questo marcatore rispetto alla sua posizione precedente e segnalerà ogni cambiamento (quindi memorizzerà la nuova posizione)

 Private Sub Worksheet_Change(ByVal Target As Range) Static lngRow As Long Dim rng1 As Range Set rng1 = ThisWorkbook.Names("RowMarker").RefersToRange If lngRow = 0 Then lngRow = rng1.Row Exit Sub End If If rng1.Row = lngRow Then Exit Sub If rng1.Row < lngRow Then MsgBox lngRow - rng1.Row & " rows removed" Else MsgBox rng1.Row - lngRow & " rows added" End If lngRow = rng1.Row End Sub 

Prova questo codice:

 Private Sub Worksheet_Change(ByVal Target As Range) Dim lNewRowCount As Long ActiveSheet.UsedRange lNewRowCount = ActiveSheet.UsedRange.Rows.Count If lOldRowCount = lNewRowCount Then ElseIf lOldRowCount > lNewRowCount Then MsgBox ("Row Deleted") lOldRowCount = lNewRowCount ElseIf lOldRowCount < lNewRowCount Then MsgBox ("Row Inserted") lOldRowCount = lNewRowCount End If End Sub 

Aggiungilo anche nel modulo ThisWorkBook:

 Private Sub Workbook_Open() ActiveSheet.UsedRange lOldRowCount = ActiveSheet.UsedRange.Rows.Count End Sub 

E poi questo nel suo modulo:

 Public lOldRowCount As Long 

Il codice presuppone che tu abbia dati nella riga 1. Nota la prima volta che lo esegui ottieni un risultato falso, perché il codice deve impostare lRowCount sulla variabile corretta. Una volta fatto dovrebbe andare bene da allora in poi.

Se non si desidera utilizzare la variabile pubblica e l'evento di apertura del foglio di lavoro, è ansible utilizzare un intervallo denominato sul foglio di lavoro da qualche parte e memorizzare il conteggio delle righe (lRowCount) lì.

Assunzione : che “distinguere i due” significa distinguere l’aggiunta / eliminazione di una riga da qualsiasi altro tipo di modifica. Se intendevi, come sapere se la modifica è stata una riga aggiunta o elimina riga, quindi ignora la mia risposta di seguito.

Nel caso di inserimento o cancellazione di una riga, target.cells.count sarà tutte le celle nella riga. Quindi puoi usare questa istruzione If per catturarla. Si noti che utilizzo cells.columns.count poiché potrebbe essere diverso per ogni file. Si innesca anche se l’utente seleziona un’intera riga e fa clic su “cancella” (per cancellare i valori), quindi è necessario codificare una soluzione alternativa, sebbene …

 Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Cells.Count = Cells.Columns.Count Then MsgBox "Row added or deleted" End If End Sub 

Dopo aver cercato un pò ho deciso di risolverlo da solo. Nel modulo del foglio di lavoro (ad es. Foglio1 sotto Oggetti di Microsoft Excel nell’Editor VBA) inserisci quanto segue:

 Private usedRowsCount As Long 'use private to limit access to var outside of sheet 'Because select occurs before change we can record the current usable row count Private Sub Worksheet_SelectionChange(ByVal Target As Range) usedRowsCount = Target.Worksheet.UsedRange.rows.count 'record current row count for row event detection End Sub 'once row count recorded at selection we can compare the used row count after change occurs 'with the Target.Address we can also detect which row has been added or removed if you need to do further mods on that row Private Sub Worksheet_Change(ByVal Target As Range) If usedRowsCount < Target.Worksheet.UsedRange.rows.count Then Debug.Print "Row Added: ", Target.Address ElseIf usedRowsCount > Target.Worksheet.UsedRange.rows.count Then Debug.Print "Row deleted: ", Target.Address End If End Sub 

Parte del risultato finale della distinzione tra inserimenti e eliminazioni determinerà il modo in cui si desidera procedere una volta identificato un inserimento o una cancellazione. Quanto segue può probabilmente essere ridotto sostanzialmente, ma ho cercato di coprire ogni ansible scenario.

 Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo bm_Safe_Exit Application.EnableEvents = False Application.ScreenUpdating = False Dim olr As Long, nlr As Long, olc As Long, nlc As Long With Target.Parent.Cells nlc = .Find(what:=Chr(42), after:=.Cells(1), LookIn:=xlValues, lookat:=xlPart, _ SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column nlr = .Find(what:=Chr(42), after:=.Cells(1), LookIn:=xlValues, lookat:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Application.Undo 'undo the last change event olc = .Find(what:=Chr(42), after:=.Cells(1), LookIn:=xlValues, lookat:=xlPart, _ SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column olr = .Find(what:=Chr(42), after:=.Cells(1), LookIn:=xlValues, lookat:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Application.Repeat 'redo the last change event End With If nlr <> olr Or nlc <> olc Then Select Case nlr Case olr - 1 Debug.Print "One (1) row has been deleted" Case Is < (olr - 1) Debug.Print (olr - nlr) & " rows have been deleted" Case olr + 1 Debug.Print "One (1) row has been inserted" Case Is > (olr + 1) Debug.Print (nlr - olr) & " rows have been inserted" Case olr Debug.Print "No rows have been deleted or inserted" Case Else 'don't know what else could happen End Select Select Case nlc Case olc - 1 Debug.Print "One (1) column has been deleted" Case Is < (olc - 1) Debug.Print (olc - nlc) & " columns have been deleted" Case olc + 1 Debug.Print "One (1) column has been inserted" Case Is > (olc + 1) Debug.Print (nlc - olc) & " columns have been inserted" Case olc Debug.Print "No columns have been deleted or inserted" Case Else 'don't know what else could happen End Select Else 'deal with standard Intersect(Target, Range) events here End If bm_Safe_Exit: Application.EnableEvents = True Application.ScreenUpdating = True End Sub 

In sostanza, questo codice identifica l’ultima cella in termini di colonne e l’ultima cella di celle in termini di righe. Quindi annulla l’ultima operazione e controlla di nuovo. Confrontando i due risultati è ansible determinare se una riga / colonna è stata inserita / cancellata. Una volta eseguite le quattro misurazioni, viene ripetuta l’ultima operazione in modo che sia ansible elaborare altre operazioni di Worksheet_Change standard.

Ci sono due un altro un altro approccio entrambi basati sul seguente modello.

  1. Definire un modulo o una variabile del modulo di class di tipo Range .
  2. “Pin” un intervallo speciale assegnandolo alla variabile usando l’indirizzo assoluto e salvando il suo indirizzo o dimensione (dipende dall’approccio).
  3. Per determinare un sottotipo di azione dell’utente manipolare con la variabile in un gestore eventi di cambio foglio.

Nel primo approccio l’intero intervallo di interesse è assegnato alla variabile e la dimensione della gamma viene salvata. Quindi, in un gestore eventi di cambio foglio, devono essere elaborati i seguenti casi:

  • si verifica un’eccezione quando si accede alla proprietà Address => l’intervallo aggiunto non esiste più;
  • l’indirizzo della cella modificata è inferiore all’intervallo aggiunto => un inserimento era => aggiorna la variabile
  • una nuova dimensione della gamma bloccata è diversa da quella salvata (più piccola => qualcosa è stato eliminato, più grande => è stato inserito qualcosa).

Nel secondo approccio viene assegnato un intervallo “marker” alla variabile (vedere l’esempio sotto) e l’indirizzo range viene salvato per determinare movimenti o spostamenti in qualsiasi direzione . Quindi, in un gestore di eventi di cambio foglio, devono essere elaborati i seguenti casi:

  • si verifica un’eccezione quando si accede a Proprietà Address => l’intervallo “marcatore” bloccato non esiste più;
  • l’indirizzo della cella modificata è al di sotto di allora intervallo “marker” => un inserimento era => aggiorna la variabile
  • c’è una differenza in qualsiasi direzione, ovvero abs(new_row - saved_row) > 0 or abs(new_col-saved_col) > 0 => l’intervallo bloccato è stato spostato o spostato.

Professionisti:

  • Il nome definito dall’utente non viene utilizzato
  • UsedRange proprietà UsedRange non è utilizzata
  • Un intervallo bloccato viene aggiornato in base alle azioni dell’utente anziché presupporre che un’azione dell’utente non si verifichi sotto la riga 1000.

Contro:

  • La variabile deve essere assegnata in un gestore di eventi open della cartella di lavoro per utilizzarla in un gestore di eventi di cambio foglio.
  • La variabile e una variabile di oggetti WithEvents devono essere assegnate a Nothing in un gestore eventi di chiusura della cartella di lavoro per annullare l’iscrizione all’evento.
  • È imansible determinare le operazioni di ordinamento a causa della modifica del valore dell’intervallo anziché delle righe di scambio.

Il seguente esempio mostra che entrambi gli approcci potrebbero funzionare. Definisci in un modulo:

 Private m_st As Range Sub set_m_st() Set m_st = [$A$10:$F$10] End Sub Sub get_m_st() MsgBox m_st.Address End Sub 

Quindi esegui set_m_st (posiziona semplicemente un cursore nel sottotitolo e chiama Run azione) per bloccare l’intervallo $A$10:$F$10 . Inserisci o elimina una riga o una cella sopra di essa (non confondere con il cambiamento del valore delle celle). Esegui get_m_st per vedere un indirizzo modificato dell’intervallo get_m_st . Elimina l’intervallo get_m_st per ottenere l’eccezione “Oggetto richiesto” in get_m_st .

Cattura le aggiunte e le eliminazioni di righe nell’evento worksheet_change.

Creo un intervallo denominato denominato “CurRowCnt”; formula: = ROWS (Tabella 1). Accesso in codice VBA con:

 CurRowCnt = Evaluate(Application.Names("CurRowCnt").RefersTo) 

Questo intervallo denominato terrà sempre il numero di righe “dopo” l’inserimento di una riga (s) o la cancellazione. Trovo che fornisca un CurRowCnt più stabile rispetto all’utilizzo di una variabile di livello globale o di modulo, migliore per la programmazione, il test e il debug.

Salvare CurRowCnt in una proprietà documento personalizzata, nuovamente per motivi di stabilità.

 ThisWorkbook.CustomDocumentProperties("RowCnt").Value = Evaluate(Application.Names("CurRowCnt").RefersTo) 

La mia struttura di eventi Worksheet_Change è la seguente:

 Dim CurRowCnt as Double CurRowCnt = Evaluate(Application.Names("CurRowCnt").RefersTo) Select Case CurRowCnt '' ########## ROW(S) ADDED Case Is > ThisWorkbook.CustomDocumentProperties("RowCnt").Value Dim r As Range Dim NewRow as Range ThisWorkbook.CustomDocumentProperties("RowCnt").Value = _ Evaluate(Application.Names("CurRowCnt").RefersTo) For Each r In Selection.Rows.EntireRow Set NewRow = Intersect(Application.Range("Table1"), r) 'Process new row(s) here next r '' ########## ROW(S) DELETED Case Is < ThisWorkbook.CustomDocumentProperties("RowCnt").Value ThisWorkbook.CustomDocumentProperties("RowCnt").Value = _ Evaluate(Application.Names("CurRowCnt").RefersTo) 'Process here '' ########## CELL CHANGE 'Case Is = RowCnt 'Process here '' ########## PROCESSING ERROR Case Else 'Should happen only on error with CurRowCnt or RowCnt 'Error msg here End Select