code werkt goed, application screenupdating niet

westerp · 31 aug 2015

Ik heb een code, die naar wens werk, het scherm blijft echter "flikkeren" ondanks het gebruik van application.screenupdating.
Kan iemand mij helpen dit op te lossen?
Ik verwacht daarnaast dat mijn code korter en minder omslachtig kan, ik weet echter niet hoe, maar zou via dit forum hier graag van leren.
alvast bedankt

Code:

Sub Vervolgkeuzelijst23_BijWijzigen()

ActiveSheet.Unprotect Password:="tent"
Application.ScreenUpdating = False

    If Range("a60") = 1 Then
    Range("h27").FormulaR1C1 = "0,3"
    Range("f26:f29").ClearContents
    
     Range("H21:H24").Copy
     Range("F21").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("d26").FormulaR1C1 = "115,00"
     Range("d27").FormulaR1C1 = "0,3"
     Range("d28").FormulaR1C1 = "0,00"
     Range("d29").FormulaR1C1 = "0,00"
       Application.CutCopyMode = False
     Range("B66:C68").NumberFormat = "General"
    Range("B66:C68").NumberFormat = "0"
   
    Else
    Application.ScreenUpdating = False
    If Range("a60") = 2 Then
    Range("h27").Select
    ActiveCell.FormulaR1C1 = "0,3"
    Range("f26:f29").ClearContents
    
     Range("H21:H24").Copy
     Range("F21").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

     Range("d26").FormulaR1C1 = "90,00"
     Range("d27").FormulaR1C1 = "0,3"
     Range("d28").FormulaR1C1 = "0,00"
     Range("d29").FormulaR1C1 = "0,00"
     Application.CutCopyMode = False
     
    Range("B66:C68").NumberFormat = "General"
    Range("B66:C68").NumberFormat = "0"

       
     Else
     Application.ScreenUpdating = False
    If Range("a60") = 3 Then
    Range("h27").FormulaR1C1 = "2"
    
    Range("f26:f29").ClearContents
        
     Range("H21:H24").Copy
     Range("F21").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     Application.CutCopyMode = False
     
    Range("d26").FormulaR1C1 = "24,80"
     Range("d27").FormulaR1C1 = "2,0"
     Range("d28").FormulaR1C1 = "28,5"
     Range("d29").FormulaR1C1 = "1,60"
     
     
    Range("B66:C68").NumberFormat = "General"
    Range("B66:C68").NumberFormat = "0"
 
     Else
     Application.ScreenUpdating = False
    If Range("a60") = 4 Then
    Range("h27").FormulaR1C1 = "1"
  
    Range("f26:f29").ClearContents
    
     Range("H21:H24").Copy
     Range("F21").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Range("d26").FormulaR1C1 = "24,50"
     Range("d27").FormulaR1C1 = "1,0"
     Range("d28").FormulaR1C1 = "28,5"
     Range("d29").FormulaR1C1 = "0,8"
     Application.CutCopyMode = False
     
   
    Range("B66:C68").NumberFormat = "General"
    Range("B66:C68").NumberFormat = "0"

      Else
     Application.ScreenUpdating = False
    
    If Range("a60") = 0 Then
    Range("h27").FormulaR1C1 = ""
    Range("f26").FormulaR1C1 = "0,15"
     Range("f27").FormulaR1C1 = "25"
     Range("f28").FormulaR1C1 = "28,5"
     Range("f29").FormulaR1C1 = "1,45"
    
    Range("d26").FormulaR1C1 = "0,15"
     Range("d27").FormulaR1C1 = "25"
     Range("d28").FormulaR1C1 = "28,5"
     Range("d29").FormulaR1C1 = "1,45"
    Application.ScreenUpdating = False

      Range("d21").Copy
       Range("F21").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
      Range("H22:H24").Copy

    Range("F22:f24").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     Application.CutCopyMode = False
       
             
     Range("B66:C68").NumberFormat = "$ #,##0.00"
    Range("B66:C68").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
          ActiveSheet.Protect Password:="tent"
         Range("d11").Select
  Else
    End If
    End If
    End If
    End If
    End If
     Range("d11").Select
   ActiveSheet.Protect Password:="tent"
     Application.ScreenUpdating = True
End Sub

edmoor · 31 aug 2015

Door totaal onjuist/geen gebruik van inspringpunten is die code maar heel slecht te volgen. Daarnaast is het voldoende om 1x Application.ScreenUpdating = False te gebruiken. Het staat er nu een aantal keren in terwijl alleen de eerste voldoende is.

westerp · 31 aug 2015

ik heb de code aangepast, hopelijk is de code nu beter leesbaar.

Code:

Sub Vervolgkeuzelijst23_BijWijzigen()

ActiveSheet.Unprotect Password:="tent"
Application.ScreenUpdating = False

 If Range("a60") = 1 Then
    Range("h27").FormulaR1C1 = "0,3"
    Range("f26:f29").ClearContents
    
    Range("H21:H24").Copy
    Range("F21").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("d26").FormulaR1C1 = "115,00"
    Range("d27").FormulaR1C1 = "0,3"
    Range("d28").FormulaR1C1 = "0,00"
    Range("d29").FormulaR1C1 = "0,00"
       Application.CutCopyMode = False
    Range("B66:C68").NumberFormat = "General"
    Range("B66:C68").NumberFormat = "0"
   
Else

If Range("a60") = 2 Then
   Range("h27").Select
   ActiveCell.FormulaR1C1 = "0,3"
   Range("f26:f29").ClearContents
    
   Range("H21:H24").Copy
   Range("F21").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

   Range("d26").FormulaR1C1 = "90,00"
   Range("d27").FormulaR1C1 = "0,3"
   Range("d28").FormulaR1C1 = "0,00"
   Range("d29").FormulaR1C1 = "0,00"
   Application.CutCopyMode = False
     
   Range("B66:C68").NumberFormat = "General"
   Range("B66:C68").NumberFormat = "0"

Else
If Range("a60") = 3 Then
   Range("h27").FormulaR1C1 = "2"
    
   Range("f26:f29").ClearContents
        
   Range("H21:H24").Copy
   Range("F21").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   Application.CutCopyMode = False
     
   Range("d26").FormulaR1C1 = "24,80"
   Range("d27").FormulaR1C1 = "2,0"
   Range("d28").FormulaR1C1 = "28,5"
   Range("d29").FormulaR1C1 = "1,60"
     
   Range("B66:C68").NumberFormat = "General"
   Range("B66:C68").NumberFormat = "0"
  
Else
If Range("a60") = 4 Then
   Range("h27").FormulaR1C1 = "1"
   Range("f26:f29").ClearContents
    
   Range("H21:H24").Copy
   Range("F21").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
   Range("d26").FormulaR1C1 = "24,50"
   Range("d27").FormulaR1C1 = "1,0"
   Range("d28").FormulaR1C1 = "28,5"
   Range("d29").FormulaR1C1 = "0,8"
   Application.CutCopyMode = False
     
   
   Range("B66:C68").NumberFormat = "General"
   Range("B66:C68").NumberFormat = "0"

Else
       
If Range("a60") = 0 Then
   Range("h27").FormulaR1C1 = ""
   Range("f26").FormulaR1C1 = "0,15"
   Range("f27").FormulaR1C1 = "25"
   Range("f28").FormulaR1C1 = "28,5"
   Range("f29").FormulaR1C1 = "1,45"
    
   Range("d26").FormulaR1C1 = "0,15"
   Range("d27").FormulaR1C1 = "25"
   Range("d28").FormulaR1C1 = "28,5"
   Range("d29").FormulaR1C1 = "1,45"
    
   Range("d21").Copy
   Range("F21").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
   Range("H22:H24").Copy
   Range("F22:f24").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
                 
   Range("B66:C68").NumberFormat = "$ #,##0.00"
   Range("B66:C68").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
         
 Else
 End If
 End If
 End If
 End If
 End If
    
    Range("d11").Select
    ActiveSheet.Protect Password:="tent"
    Application.ScreenUpdating = True
End Sub

jkpieterse · 31 aug 2015

Geen idee waarom screenupdating niet werkt, maar in ieder geval kan je code leesbaarder gemaakt worden met behulp van Select Case:

Code:

Sub Vervolgkeuzelijst23_BijWijzigen()

    ActiveSheet.Unprotect Password:="tent"
    Application.ScreenUpdating = False

    Select Case Range("a60").Value
    Case 1
        Range("h27").FormulaR1C1 = "0,3"
        Range("f26:f29").ClearContents

        Range("H21:H24").Copy
        Range("F21").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                           :=False, Transpose:=False
        Range("d26").FormulaR1C1 = "115,00"
        Range("d27").FormulaR1C1 = "0,3"
        Range("d28").FormulaR1C1 = "0,00"
        Range("d29").FormulaR1C1 = "0,00"
        Application.CutCopyMode = False
        Range("B66:C68").NumberFormat = "General"
        Range("B66:C68").NumberFormat = "0"

    Case 2
        Range("h27").Select
        ActiveCell.FormulaR1C1 = "0,3"
        Range("f26:f29").ClearContents

        Range("H21:H24").Copy
        Range("F21").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                           :=False, Transpose:=False

        Range("d26").FormulaR1C1 = "90,00"
        Range("d27").FormulaR1C1 = "0,3"
        Range("d28").FormulaR1C1 = "0,00"
        Range("d29").FormulaR1C1 = "0,00"
        Application.CutCopyMode = False

        Range("B66:C68").NumberFormat = "General"
        Range("B66:C68").NumberFormat = "0"


    Case 3
        Range("h27").FormulaR1C1 = "2"

        Range("f26:f29").ClearContents

        Range("H21:H24").Copy
        Range("F21").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                           :=False, Transpose:=False
        Application.CutCopyMode = False

        Range("d26").FormulaR1C1 = "24,80"
        Range("d27").FormulaR1C1 = "2,0"
        Range("d28").FormulaR1C1 = "28,5"
        Range("d29").FormulaR1C1 = "1,60"


        Range("B66:C68").NumberFormat = "General"
        Range("B66:C68").NumberFormat = "0"

    Case 4
        Range("h27").FormulaR1C1 = "1"

        Range("f26:f29").ClearContents

        Range("H21:H24").Copy
        Range("F21").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                           :=False, Transpose:=False

        Range("d26").FormulaR1C1 = "24,50"
        Range("d27").FormulaR1C1 = "1,0"
        Range("d28").FormulaR1C1 = "28,5"
        Range("d29").FormulaR1C1 = "0,8"
        Application.CutCopyMode = False


        Range("B66:C68").NumberFormat = "General"
        Range("B66:C68").NumberFormat = "0"

    Case 0
        Application.ScreenUpdating = False
        Range("h27").FormulaR1C1 = ""
        Range("f26").FormulaR1C1 = "0,15"
        Range("f27").FormulaR1C1 = "25"
        Range("f28").FormulaR1C1 = "28,5"
        Range("f29").FormulaR1C1 = "1,45"

        Range("d26").FormulaR1C1 = "0,15"
        Range("d27").FormulaR1C1 = "25"
        Range("d28").FormulaR1C1 = "28,5"
        Range("d29").FormulaR1C1 = "1,45"
        Application.ScreenUpdating = False

        Range("d21").Copy
        Range("F21").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                           :=False, Transpose:=False

        Range("H22:H24").Copy

        Range("F22:f24").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                               :=False, Transpose:=False
        Application.CutCopyMode = False


        Range("B66:C68").NumberFormat = "$ #,##0.00"
        Range("B66:C68").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"

    End Select
    Range("d11").Select
    ActiveSheet.Protect Password:="tent"
    Application.ScreenUpdating = True
End Sub

Ik zie ook dat bij iedere case de coderegels nauwelijks verschillen, mogelijk dat die herhaling van statements voorkomen kan worden, maar omdat ik het bijbehorende bestand niet ken heb ik dat niet gedaan.

snb · 31 aug 2015

Excel is niet de enige die bij deze code moest knipperen.
Zonder application.screenupdating kan het waarschijnlijk ook.
Vermijd 'copy'.

Code:

Sub Vervolgkeuzelijst23_BijWijzigen()
    ActiveSheet.Unprotect "tent"

    Range("f26:f29").ClearContents
    Range("F21:F24")=Range("H21:H24").Value
    Range("B66:C68").NumberFormat = "0"
    Cells(27,8) = choose(cells(60,1)+1,"",.3,.3,2,1)

    Select Case cells(60,1)
    Case 1
        Range("d26:D29") = application.transpose(array(115,.3, 0,0))
    Case 2
        Range("d26:D29") = application.transpose(array(90,.3, 0,0))
     Case 3
        Range("d26:D29") = application.transpose(array(24.80,2.0, 28.5, 1.6))
    Case 4
        Range("d26:D29") = application.transpose(array(24.5,1, 28.5, .8))
    Case 0
        Range("D26:D29") = application.transpose(array(.15,25, 28.5, 1.45))
        Range("F26:F29") = Range("D26:D29").value

        Range("d21")=Range("F21").value
        Range("B66:C68").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
    End Select
 
    ActiveSheet.Protect "tent"
End Sub

westerp · 31 aug 2015

super!
ik heb de code nog iets aangepast, deze werkt nu goed. bedankt.
het flikkeren blijft. dit treedt op bij cellen met een voorwaardelijke opmaak. kan dat wellicht de oorzaak zijn?
zoja is hier een oplossing voor?

Code:

Sub Vervolgkeuzelijst23_BijWijzigen()
    ActiveSheet.Unprotect "tent"

Application.ScreenUpdating = False

    Range("f26:f29").ClearContents
    Range("F21:F24") = Range("H21:H24").Value
    Range("B66:C68").NumberFormat = "0"
    Cells(27, 8) = Choose(Cells(60, 1) + 1, "", 0.3, 0.3, 2, 1)

    Select Case Cells(60, 1)
    Case 1
        Range("d26:D29") = Application.Transpose(Array(115, 0.3, 0, 0))
    Case 2
        Range("d26:D29") = Application.Transpose(Array(90, 0.3, 0, 0))
     Case 3
        Range("d26:D29") = Application.Transpose(Array(24.8, 2#, 28.5, 1.6))
    Case 4
        Range("d26:D29") = Application.Transpose(Array(24.5, 1, 28.5, 0.8))
    Case 0
        
        
        
        Range("D26:D29") = Application.Transpose(Array(0.15, 25, 28.5, 1.45))
        Range("F26:F29") = Range("D26:D29").Value
         Range("F22:F24") = Range("D22:D24").Value
        Range("f21") = 25
        Range("d21") = Range("F21").Value
        Range("B66:C68").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End Select

Application.ScreenUpdating = False
 ActiveSheet.Protect "tent"
End Sub

snb · 31 aug 2015

Code:

Range("D21") = 25
Range("F21:F24") = Range("D21:D24").Value

Ik vermoed dat er andere code in je werkboek staat die dit veroorzaakt.

westerp · 31 aug 2015

er zijn verder geen codes in het werkboek, maar het knipperen is nu acceptabel.
Iedereen bedankt:thumb:

code werkt goed, application screenupdating niet

westerp

Gebruiker

edmoor

Inventaris

westerp

Gebruiker

jkpieterse

Terugkerende gebruiker

snb

Verenigingslid

westerp

Gebruiker

snb

Verenigingslid

westerp

Gebruiker

Nieuwste berichten

Wij waarderen jouw privacy