Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does a loop start ?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does a loop start ?
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 wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does a loop start ?
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 "Trevor Shuttleworth" skrev i meddelandet ... 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 wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does a loop start ?
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. "Trevor Shuttleworth" skrev i meddelandet ... 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 wrote in message ... 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 " |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does a loop start ?
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 wrote in message ... 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. "Trevor Shuttleworth" skrev i meddelandet ... 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 wrote in message ... 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 " |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OT :Start your own online business today !start making dollars | Excel Discussion (Misc queries) | |||
Start spreadsheet with WinXP start | Excel Worksheet Functions | |||
Worksheet_Change - loop within a loop | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming | |||
From worksheet enter DO-Loop start & end code | Excel Programming |