First of all, calling a sub from a worksheet_change event in order to
avoid duplicate code in each sheet is not necessary.
You can put the event code once in Thisworkbook module..............
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
'event code goes here and will run in whichever sheet is active
End Sub
The breakpoint part I don't understand.............I have never tried
to figure those out<g>
Gord
On Wed, 28 Dec 2011 14:43:13 -0800 (PST), Winfried Kastner
<(E-Mail Removed)> wrote:
>Hello,
>
>I have one big problem because I cannot use the break point
>functionality in Excel for whatever reasons
>
>I have a big Excel workbook (size about 18 MB, around 30 worksheets,
>around used 150.000 formulas), my PC has 4 GB memory and I use Windows
>XP SP3 and Excel 2003 SP3 with all patches.
>
>In around 17 worksheets I have to check changed cell values against
>some plausibility rules. In those 17 worksheets I use the
>"Worksheet_Change" event that looks as follows:
>
>Private Sub Worksheet_Change(ByVal Target As Range)
>
>' Call of the sub for further centralized processing of the
>"Worksheet_Change" event.
>Call Check_Worksheet_Change(ActiveSheet.Name, Target)
>
>End Sub
>
>
>To avoid duplicate code I call a central sub "Check_Worksheet_Change"
>with the active worksheet name and the changed and to be checked cell
>as parameters for further processing. The parameter "Target" is always
>one single cell.
>
>
>This central sub "Check_Worksheet_Change" looks as follows:
>
>Sub Check_Worksheet_Change(ByVal wksName As String, ByVal Target As
>Range)
>
>Call InitVariables
>
>' here follows several code to execute the checks etc.
>
>end sub
>
>
>In order to check my code I want to set a break point to the row "Call
>InitVariables". What happens is that that breakpoint will be ignored
>completely. If you insert a "MsgBox" command or a "Debug.Print Time"
>before or after the "Call InitVariables" you see that all will be
>executed and displayed but the set break point will be ignored
>completely. This of course is very bad when you want respectively have
>to debug your program.
>
>This always happens when I already set another break point in the
>"Worksheet_Change" event.
>
>In my Excel worksheet this mailfunction of the use of a break point
>only happens only for one specific column but this is the most
>important one. The cells in this column have all a conditional
>formatting that looks as follows: "Condition1 is "Formula is"
>"=HasCellAFormula('cell')" with 'cell' contents like R6 or R18. This
>is an own written tiny function that returns whether True or False
>whether this cell contains a formula or not. The VBA code for this
>function is "HasCellAFormula = cell.HasFormula" with cell as parameter
>that shall be checked. But that should not be the reason.
>
>When I do a change in those worksheets in cells of other columns
>beside column "R" all works as it should, i.e. the code stops at
>defined break point.
>
>
>Now my main question: Has somebody any idea why Excel ignores this
>break point? Are there any conditions or known errors when a break
>point will be ignored? I googled the web but I found no hit. If you
>have any idea please let me know.
>
>Thank you in advance.
>Winfried
|
