Help with Abort Procedure

  • Thread starter Thread starter Casey
  • Start date Start date
C

Casey

Good Morning,
I have an event procedure that locks up when the copy and paste
commands are used on the worksheet. I looked up some code I remembered
from John Walkenbach's book "Excel 2000 Power Programming with VBA"
about stopping endless loops and found the code for an AbortProc but I
have been unable to make it work for my code. Could somebody let me
know if I'm even on the right path or have I just not applied John's
code correctly to my code. Also, is there a way using the AutoFit
method to set a minimum column width to not go below? Thanks for the
help.

Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

Static AbortProc As Boolean

If AbortProc Then Exit Sub

If Not Intersect(Target, Range("C:F")) Is Nothing Then
AbortProc = False
Application.ScreenUpdating = False
ActiveSheet.Unprotect ("geekk")
With ActiveSheet
AbortProc = True
..Cells.Columns("F:L").AutoFit
AbortProc = False
End With
ActiveSheet.Protect ("geekk")
Application.ScreenUpdating = False
AbortProc = False
End If
AbortProc = False
End Sub
 
I think this is what you want

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Range("C:F")) Is Nothing Then
Application.ScreenUpdating = False
ActiveSheet.Unprotect ("geekk")
Me.Cells.Columns("F:L").AutoFit
ActiveSheet.Protect ("geekk")
Application.ScreenUpdating = True
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Bob,
Thank you very much. I pasted the code and checked with 4 or 5 rounds
of copy/paste and it works fine. Do you have any ideas on maintaining a
minimum column width while still using the AutoFit method. As an example
not allowing the width to go below 10 or so. Something like
Me.Cells.Columns ("F:L"). ColumnWidth=10 as a minimum?
Again thank you for the great fix.
 
The only thing I can think of is to do an AUtofit, and then check the column
width and up to 10 if necessary.

Columns("F:L").AutoFit
For i = 6 To 12
If Columns(i).ColumnWidth > 10 Then
Columns(i).ColumnWidth = 10
End If
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Bob
Perfect. As many pieces of VBA as I have hack together, I still hav
trouble with visualizing ways to represent simple varaibles. Your cod
work great although I did have to turn the operator around to achiev
what I needed and after running the code I chose 8 instead of 10 as
minimum column width. Thank you so much for your help.
Here is my final Code.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Range("C:F")) Is Nothing Then
Application.ScreenUpdating = False
ActiveSheet.Unprotect ("geekk")
Me.Cells.Columns("F:L").AutoFit
For i = 6 To 12
If Columns(i).ColumnWidth < 8 Then
Columns(i).ColumnWidth = 8
End If
Next i
ActiveSheet.Protect ("geekk")
Application.ScreenUpdating = True
End If

ws_exit:
Application.EnableEvents = True
End Su
 
Casey,

That's good. Pity I couldn't understand ... not allowing the width to go
below 10 ... <vbg>

Regards

Bob
 
Back
Top