Sub Worksheet_Change question 3

[mscallisto]

The following code is in sheet1 and bolds specific changes in cells N1 to T47

Private Sub Worksheet_Change(ByVal Target As Range)
 With Target
  set isect = Application.Intersect(Target,Range("N12:T47"))
   If isect Is Nothing Then
'   do nothing
    else
     If .Formula > .1 And .Formula < 25 Then
      .Font.Bold = True
      Else
      .Font.Bold = False
     End If
   End If
 End With
End Sub

I also have a button on sheet1 that executes code to delete sheet1 and restore it to its original state by copying and pasting from another "template" sheet.

I discovered that when the code deletes sheet1 it triggers
Sub Worksheet_Change and all hell breaks loose (reboot required)!!!

Is there a way around this?

 

[electricpete]

How about erase and write over instead of deleting the sheet.

Or possibly disable events prior to deleting the sheet (not sure about this one)

 

[ck1999]

Here is a possible work around.

1. Create a global variable
2. Copy the procedure above into a module and add another parameter
3. call module from worksheet_change


Global variable

dim resetboolean

Then on worksheet open set resetboolean = true

then in your worksheet_change use
boldrange(target, resetboulean)

and in your copy paste macro the 1st line should be resetboolean = false

and the last line should be resetboolean = true

then your module

Sub boldrange(ByVal Target As Range,preventchange)
if preventchange then
  With Target
  set isect = Application.Intersect(Target,Range("N12:T47"))
   If isect Is Nothing Then
'   do nothing
    else
     If .Formula > .1 And .Formula < 25 Then
      .Font.Bold = True
      Else
      .Font.Bold = False
     End If
   End If
 End With
end if
End Sub

Try this and see if it works!

ck1999

 

[SkipVought]

How about using the ThisWorkbook Object Sheet events?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

You can trap the change on any particluar sheet, using the Sh Object.


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[mscallisto]

Thanks CK1999 that could work but Skip pointed out exactly what I couldn't find

sam

 

[PHV]

Another simple way is to use the Application.EnableEvents property before and after restoring Sheet1.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mscallisto]

Oh PHV I like that !!!