Excel freezing on right-click paste

S

simico

New Member
Joined
May 27, 2011
Messages
30
Hello again helpful people,

Excel 2007 and Windows XP.

How to begin? This is a weird issue. I have a really big spreadsheet with lots of VBA in it. On two of the worksheets, right-click pasting causes Excel to freeze. Ctrl-V does NOT.

On the pages in question there are 7 drop downs that do a lot. One of the things is that they cause the formatting to be changed, with code like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False

Code: 
    If [A44] = "Response Time: Percent Calls under 30 Minutes" Or [A44] = "Response Time: Percent Calls under 60 Minutes" Or [A44] = "Response Time: Percent Calls over 60 Minutes" Or [A44] = "Response Time: Percent of Calls Over 90 Minutes" Or [A44] = "Response Time: Percent of Calls over 120 minutes" Then
            Range("B50:C62").NumberFormat = "0.00%"
        Else
            Range("B50:C62").NumberFormat = "#,##0"
    End If
 
 
    If [A4] = "Payment Amounts" Then
 
        'CHANGE RANGE TO CURRENCY WITH COMMA
        Range("B8:F22").NumberFormat = "$#,##0.00"
 
        'CHANGE RANGE TO TEXT
        Range("E7:F7").NumberFormat = "@"

The other thing they do it activate a bunch of nested vlookups like this:
Code: 
=IFERROR(IF(ISERROR(VLOOKUP(Lookups!$B2&" "&$B$1,AZ_SR_DATA_FACL!$F$4:$K$499218,4,0)),"",IF($A$4=Lookups!$E$1,VLOOKUP(Lookups!$B2&" "&$B$1,AZ_SR_DATA_FACL!$F$4:$K$499218,4,0),IF($A$4=Lookups!$E$2,VLOOKUP(Lookups!$B2&" "&$B$1,AZ_SR_DATA_FACL!$T$4:$W$499218,4,0),IF($A$4=Lookups!$E$3,VLOOKUP(Lookups!$B2&" "&$B$1,AZ_SR_DATA_FACL!$AD$4:$AH$499218,4,0),IF($A$4=Lookups!$E$4,VLOOKUP(Lookups!B2&" "&$B$1,AZ_SR_DATA_FACL!$F$4:$K$499218,6,0)))))),"")

None of the other worksheets cause the freezing, but none of them have all of this code, either. Is there any known reason why this would be happening? Is there anything I can do to stop it from happening?

Thanks all.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
You could try to set calculation to manual while executing the code in the Worksheet_Change eventhandler.
You could also try to add
Application.EnableEvents = False
in the beginning, and
Application.EnableEvents = True
in the end of that same eventhandler.

When you paste information on that sheet now, a cell changes, which fires the eventhandler, but in the eventhandler itself, you change other cells, which again fires the eventhandler, and so on... before everything gets pasted, the eventhandler has to be called a lot of times, which makes Excel appear to be frozen for a while...
The EnableEvents setting avoids 'doublefiring' and should speed things up significantly. The Calculation setting too, if it's on automatic, you will get a recalculate after every cell change, combined with the doublefiring eventhandler, you got a whole lot of things going on for a simple paste operation...

I hope that helps...
 
Upvote 0
Thanks for the reply Hermanito.

Unfortunately, turning the calculations to manual and adding the turning off the events with the code you suggested did not work. It is still freezing whenever I right-click paste anything onto that page. It is so very frustrating.

Any other suggestions for a poor soul?
 
Upvote 0
Could you maybe post the full code of one of the sheets with the freezing issue? Not only the Worksheet_Change eventhandler, but everything, unless that is too much to post here...
 
Upvote 0
Here's the worksheet change one. It's mainly just formatting. The rest is all in formulas....about 100 cells with the nested vlookups like the one in the OP, and 5 or 6 dropdowns off of lists.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
 
    If [A44] = "Response Time: Percent Calls under 30 Minutes" Or [A44] = "Response Time: Percent Calls under 60 Minutes" Or [A44] = "Response Time: Percent Calls over 60 Minutes" Or [A44] = "Response Time: Percent of Calls Over 90 Minutes" Or [A44] = "Response Time: Percent of Calls over 120 minutes" Then
            Range("B50:C62").NumberFormat = "0.00%"
        Else
            Range("B50:C62").NumberFormat = "#,##0"
    End If
 
 
    If [A4] = "Payment Amounts" Then
 
        'CHANGE RANGE TO CURRENCY WITH COMMA
        Range("B8:F22").NumberFormat = "$#,##0.00"
 
        'CHANGE RANGE TO TEXT
        Range("E7:F7").NumberFormat = "@"
 
 
        'TURN HORIZONTAL YEAR CELLS BLUE WITH WHITE LETTERING AND BOLD
        Range("E7:F7").Interior.Pattern = xlSolid
        Range("E7:F7").Interior.PatternColorIndex = xlAutomatic
        Range("E7:F7").Interior.ThemeColor = xlThemeColorLight2
 
        'TURN HORIZONTAL SEPARATION CELLS BLUE WITH WHITE LETTERING AND BOLD
        Range("E9:F9").Interior.Pattern = xlSolid
        Range("E9:F9").Interior.PatternColorIndex = xlAutomatic
        Range("E9:F9").Interior.ThemeColor = xlThemeColorLight2
 
        'TURN HORIZONTAL TOTAL CELLS BLUE WITH WHITE LETTERING AND BOLD
        Range("E22:F22").Interior.Pattern = xlSolid
        Range("E22:F22").Interior.PatternColorIndex = xlAutomatic
        Range("E22:F22").Interior.ThemeColor = xlThemeColorLight2
 
 
        'PLACE THIN BORDER AROUND ALL CELLS IN RANGE
        Range("E7:F22").Borders(xlEdgeLeft).LineStyle = xlContinuous
        Range("E7:F22").Borders(xlEdgeLeft).Weight = xlThin
        Range("E7:F22").Borders(xlEdgeTop).LineStyle = xlContinuous
        Range("E7:F22").Borders(xlEdgeTop).Weight = xlThin
        Range("E7:F22").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("E7:F22").Borders(xlEdgeBottom).Weight = xlThin
        Range("E7:F22").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E7:F22").Borders(xlEdgeRight).Weight = xlThin
        Range("E7:F22").Borders(xlInsideVertical).LineStyle = xlContinuous
        Range("E7:F22").Borders(xlInsideVertical).Weight = xlThin
        Range("E7:F22").Borders(xlInsideHorizontal).LineStyle = xlContinuous
        Range("E7:F22").Borders(xlInsideHorizontal).Weight = xlThin
 
 
        'SINGLE MEDIUM FRAME BORDERS AROUND ENTIRE RANGE
 
        Range("E7:F22").Borders(xlEdgeLeft).LineStyle = xlContinuous
        Range("E7:F22").Borders(xlEdgeLeft).Weight = xlMedium
        Range("E7:F22").Borders(xlEdgeTop).LineStyle = xlContinuous
        Range("E7:F22").Borders(xlEdgeTop).Weight = xlMedium
 
        Range("E7:F22").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("E7:F22").Borders(xlEdgeBottom).Weight = xlMedium
 
        Range("E7:F22").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E7:F22").Borders(xlEdgeRight).Weight = xlMedium
 
        'MEDIUM BORDER AROUND HORIZONTAL YEAR CELLS
        Range("E7:F7").Borders(xlEdgeLeft).LineStyle = xlContinuous
        Range("E7:F7").Borders(xlEdgeLeft).Weight = xlMedium
        Range("E7:F7").Borders(xlEdgeTop).LineStyle = xlContinuous
        Range("E7:F7").Borders(xlEdgeTop).Weight = xlMedium
 
        Range("E7:F7").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("E7:F7").Borders(xlEdgeBottom).Weight = xlMedium
 
        Range("E7:F7").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E7:F7").Borders(xlEdgeRight).Weight = xlMedium
 
 
        'MEDIUM BORDER AROUND HORIZONTAL SEPARATOR CELLS
        Range("E9:F9").Borders(xlEdgeLeft).LineStyle = xlContinuous
        Range("E9:F9").Borders(xlEdgeLeft).Weight = xlMedium
        Range("E9:F9").Borders(xlEdgeTop).LineStyle = xlContinuous
        Range("E9:F9").Borders(xlEdgeTop).Weight = xlMedium
 
        Range("E9:F9").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("E9:F9").Borders(xlEdgeBottom).Weight = xlMedium
 
        Range("E9:F9").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E9:F9").Borders(xlEdgeRight).Weight = xlMedium
 
 
        'MEDIUM BORDER AROUND HORIZONTAL TOTAL CELLS
        Range("E22:F22").Borders(xlEdgeLeft).LineStyle = xlContinuous
        Range("E22:F22").Borders(xlEdgeLeft).Weight = xlMedium
        Range("E22:F22").Borders(xlEdgeTop).LineStyle = xlContinuous
        Range("E22:F22").Borders(xlEdgeTop).Weight = xlMedium
 
        Range("E22:F22").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("E22:F22").Borders(xlEdgeBottom).Weight = xlMedium
 
        Range("E22:F22").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E22:F22").Borders(xlEdgeRight).Weight = xlMedium
    Else
 
        'SET RANGE TO NUMERIC
        Range("B8:F22").NumberFormat = "#,##0"
 
        'HIDE VALUES IN RANGE
        Range("E6:F22").NumberFormat = ";;;"
 
        'SET BORDERS TO NOTHING
        Range("E6:F22").Borders(xlDiagonalDown).LineStyle = xlNone
        Range("E6:F22").Borders(xlDiagonalUp).LineStyle = xlNone
        Range("E6:F22").Borders(xlEdgeLeft).LineStyle = xlNone
        Range("E6:F22").Borders(xlEdgeTop).LineStyle = xlNone
        Range("E6:F22").Borders(xlEdgeBottom).LineStyle = xlNone
        Range("E6:F22").Borders(xlEdgeRight).LineStyle = xlNone
        Range("E6:F22").Borders(xlInsideVertical).LineStyle = xlNone
        Range("E6:F22").Borders(xlInsideHorizontal).LineStyle = xlNone
 
        'SET INTERIOR COLORS TO NOTHING
        Range("E6:F22").Interior.Pattern = xlNone
        Range("E6:F22").Interior.TintAndShade = 0
        Range("E6:F22").Interior.PatternTintAndShade = 0
    End If
    If [H4] = "Go Rate" Then
            Range("I8:J22").NumberFormat = "0.00%"
        Else
            Range("I8:J22").NumberFormat = "#,##0"
    End If
 
    If [H4] = "Battery Replacements" Or [A24] = "Battery Dispatch %" Or [A24] = "Conversion Rate" Or [A24] = "Warranty Rate" Then
            Range("B28:C42").NumberFormat = "0.00%"
        Else
            Range("B28:C42").NumberFormat = "#,##0"
    End If
 
 
End Sub
 
Upvote 0
just out of curiosity: why do you have all that formatting code in the Worksheet_Change event? Do you really need to reformat all that, every single time something in the sheet changes??

Could you also show where you put the statements for calculation and enableevents, just to be sure you put them in the right spots?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Function to repeatedly replace string one-at-a-time via a lookup table
Replies
4
Views
454
smallxyz
S
A
  • Question Question
Abstract the Quantity Consumption
Replies
1
Views
96
Rijnsent
Rijnsent
S
  • Question Question
Creating a random sequence of items from a weighted list with no duplicates
Replies
6
Views
293
shg
shg
D
  • Question Question
Rounding Negative time to the nearest minute in Excel
Replies
4
Views
703
Scott Huish
Scott Huish
M
  • Question Question
Customer Ageing Different Formulas
Replies
3
Views
262
Alex Blakenburg
A

Forum statistics

Threads
1,224,559
Messages
6,179,513
Members
452,921
Latest member
BBQKING

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top