Rallentando il mio foglio e cancella tutti gli altri contenuti dopo la funzione if in vba

Aug 22 2020

Ho un foglio che è protetto e bloccato tutte le celle senza la cella di frutta selezionata come se B2. Ho scritto un codice come questo: Se inserisco "Mango" nella cella, B2il foglio non sarà protetto e sbloccherà la cella J2e proteggerà nuovamente il foglio di lavoro. Quindi, quando inserisco un valore numerico nella cella J2come "15", inserirà una riga nella riga 15. Quindi unisci A15:C15. Funziona bene. Ma il problema è che quando inserisce una riga, tutti gli altri contenuti sotto quella riga scompaiono. Inoltre in questo codice manca il mio foglio. Cosa posso fare. Per favore aiuto. Il codice viene fornito come riferimento:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim inputCell As Integer
Dim copyRange As String
Dim ws As Worksheet
Dim validateCell As String
Dim fruitType As String

inputCell = Range("J2").Value
copyRange = "A" & inputCell
Set ws = Worksheets("Sheet1")
validateCell = Range("B2").Value
fruitType = "Mango"

If validateCell = fruitType Then
   ws.Unprotect Password:="mehedi"
   Range("J2").Locked = False
   ws.Protect Password:="mehedi"
End If

If Range("J2").Value >= 5 Then
   ws.Unprotect Password:="mehedi"
   Range(copyRange).EntireRow.Insert
   Range(copyRange & ":C" & inputCell).Merge
   Range(copyRange & ":C" & inputCell).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
   Range("D" & inputCell & ":F" & inputCell).Merge
   Range("D" & inputCell & ":F" & inputCell).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
End If
End Sub

Aggiornamento per @FlexYourData

Risposte

2 FlexYourData Aug 22 2020 at 13:13

È importante quando si utilizza Worksheet_Changeper fare riferimento alla Targetgamma.

Ho impostato alcuni dati fittizi. J2era vuoto. Ho digitato Mango B2e sono entrato nella routine Sub. Nota che quando iniziamo, inputCell è zero (perché J2è vuoto).

Quindi, copyRangesarà "A0", che non è un indirizzo valido. Questa non è la causa del tuo problema qui, ma potrebbe causare problemi altrove.

Possiamo usare Targetper verificare quale cella è stata modificata. Quindi per la tua situazione, useresti qualcosa del genere:

If Target.AddressLocal = "$J$2" Then
    'do something when J2 was changed
    
ElseIf Target.AddressLocal = "$B$2" Then
    'do something when B2 was changed
    
Else
    'do something else, or nothing

End If

Ogni volta che il codice corrente chiama .EntireRow.Insert, l' Worksheet_Changeevento viene chiamato di nuovo, il che causa l'inserimento di un'altra riga, il richiamo dell'evento, la creazione di un'altra riga e così via. In effetti, sospetto che il tuo codice continuerà a inserire righe come questa, facendo sembrare i tuoi dati sottostanti come se fossero scomparsi. È probabile che anche questo causi lentezza.

Quindi, dovresti sempre fare riferimento a in Targetmodo che la macro agisca solo quando hai modificato celle specifiche (e non quando stai inserendo la riga).

Un altro elemento che potresti prendere in considerazione è mettere la tua password come costante in questo foglio o come costante globale in un altro modulo. Se quindi non vuoi che gli utenti vedano quella password, dovrai bloccare il progetto VBA, come descritto qui . Questo ti aiuterà un po ', penso poiché dovrai digitarlo solo una volta nel progetto VBA, quindi ci sono meno opportunità di errori di battitura.

Verifica se questo funziona per te incollandolo nel codice dietro il foglio di lavoro. Option Explicitdi solito è una buona idea in quanto forza il sistema a non accettare variabili a meno che non siano Dim'd

Option Explicit

Const pwd As String = "mehedi"

Private Sub Worksheet_Change(ByVal Target As Range)

Dim inputCell As Integer
Dim copyRange As String
Dim ws As Worksheet
Dim validateCell As String
Dim fruitType As String

'this is just giving a short name for the current sheet
Set ws = ActiveSheet

'set this at the top
fruitType = "Mango"

If Target.AddressLocal = "$J$2" Then
    'do something when J2 was changed
    inputCell = Target

    'only take action if the condition is met
    If inputCell >= 5 Then
        copyRange = "A" & inputCell
        
        ws.Unprotect Password:=pwd
        
        ws.Range(copyRange).EntireRow.Insert 'causes this event to fire again, so be careful~
        
        ws.Range(copyRange & ":C" & inputCell).Merge
        ws.Range(copyRange & ":C" & inputCell).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
        
        ws.Range("D" & inputCell & ":F" & inputCell).Merge
        ws.Range("D" & inputCell & ":F" & inputCell).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
        
        'perhaps you need this here as well?
        ws.Protect Password:=pwd
    End If
    
ElseIf Target.AddressLocal = "$B$2" And Target.Value = fruitType Then
    'only take action if the right fruit was entered into the right cell
    
        ws.Unprotect Password:=pwd
        ws.Range("J2").Locked = False
        ws.Protect Password:=pwd


End If

End Sub

MODIFICARE:

Il codice sopra elencato funziona perfettamente in questa serie di passaggi:

  1. tutte le celle tranne B2 sono bloccate
  2. inserire "Mango" in B2
  3. Il foglio non è protetto, J2 è sbloccato, il foglio è protetto
  4. immettere 15 in J2
  5. la riga viene inserita sopra la riga corrente 15, A15: C15 vengono uniti e gli viene assegnato un bordo, D15: F15 vengono uniti e gli viene assegnato un bordo

L'unica cosa che posso pensare in questo momento è che ci sono alcuni dettagli sul tuo problema che non hai indicato. In tal caso, sarà necessario eseguire il debug di questo problema.