Why does a loop start ?

G

Guest

Please explain the reson why "Act" become = 0 after the pastespecial
operation and ws_change starts? The whole reason with the Act thing is that
it _should_prevent_a_loop_ when event ws_change happens. Look at the mark at
FormatTemp sub where it all go wrong....

I can't use "Application.eventenable = false" approach because it seem to
wipe out the clipboard... . I have no other worksheet_event etc in the
workbook.

/Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
' *********
' Start a format restore operation when any change take place
' Act is used as a variable to prevent a loop when ForamtTemp causes any
changes
' *********
Dim Act As Integer
' "After the pastespecial in FormatTemp causes a change, a
' msgbox show Act = 0 here - why ?????"
If Not Act = 1 Then
Call FormatTemp
Else:
Exit Sub
End If
Act = 0
End Sub


Sub FormatTemp()
'**********
'SetSaveLoc and GetSaveLoc log and activate the current worksheet, workbook
and range
'**********
Dim Act As Integer
Act = 1
ActiveSheet.Unprotect
Call SetSaveLoc
Blad200.Unprotect
Blad200.Cells.Copy
ActiveSheet.Cells.Select ' Act = 1
Selection.PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

' HERE does the above action trigg worksheet_activate
' and make Act = 0 causing a loop of some reson - why? ????

ActiveSheet.Protect AllowFormattingCells:=False
ActiveSheet.EnableSelection = xlUnlockedCells
Call GetSaveLoc
End Sub
 
T

Trevor Shuttleworth

Because you define Act in each of the modules a) it is effectively a
different variable in each module and b) its life ends when the module
exits. You need to define Act once only before all of the modules so that
the value can be passed from one to the next.

Regards

Trevor
 
G

Guest

Thank you, I learned something there. I'm new with using ws_Change and find
now that where ever I place the "dim act", I get the loop. I tried to create
a new sub for evaluation etc with no luck etc. Can you just give me a more
precise hint of where/how to place the dim Act?

/Thanks again
 
G

Guest

Trevor,

If I leave out the "Dim Act " moment compleatly, the Act value still resets
to "empty" when the pastespecial command trigger the ws_Change sub.
A msgbox before pastespecial show act = 1, but a msgbox in the beginning of
ws_change show act= empty.

I there a logic into this?

/Nob.
 
T

Trevor Shuttleworth

Yes, there is a logic to it. Leaving out the definition altogether has a
similar effect to defining the variable in each module. It will, however
default to a variant and if you haven't set the variable in the code it will
be empty.

This should work:

Dim Act As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
' *********
' Start a format restore operation when any change take place
' Act is used as a variable to prevent a loop when ForamtTemp causes any
changes
' *********
:
If Not Act = 1 Then
Call FormatTemp
Else:
Exit Sub
End If
Act = 0
End Sub

Sub FormatTemp()
'**********
'SetSaveLoc and GetSaveLoc log and activate the current worksheet, workbook
and range
'**********
Act = 1
:
:
End Sub

Alternatively, or if it doesn't work, try defining Act as a Public variable
in a General module

I'm not keen on using negative conditions where there are only two options.
It is better, in my opinion, to use the positive condition ... it makes the
logic easier to follow and should identify any design errors:

If Act = 0 Then
Call FormatTemp ' This routine sets Act = 1
Else:
Exit Sub
End If
Act = 0 ' Here we set Act = 0 again

I'm afraid I'm not sure what you want to achieve given that every time there
is a Worksheet change exits you'll have reset Act to 0. So, I'm a bit
confused !

Regards

Trevor
 
Top