Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Calculate code in same module as Worksheet Change eventgoes astray
Hi, I'm trying to get some code to fire, based on a formula in cell E2
changing value to equal 1. Private Sub Worksheet_Calculate() If Cells(5, 2) = 1 Then MsgBox "Fires ok" 'Application.EnableEvents = False End Sub I can get it to work properly on a blank spreadsheet. However I need to use it on a worksheet that already has a Worksheet Change code. Since it seems like each worksheet can only have one module (page? not sure of the right name) for code, the above code is in the same module as the Change code. Thus, when the entry is made that changes E2 to equal 1, it starts in the above code, then jumps to the Change code and begins running some of that code. If I EnableEvents=False it stops the straying, but still doesn't bring up the MsgBox. Any help would be appreciated. Harold |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Calculate code in same module as Worksheet Change event goes astray
Try your code this way...
If Cells(5, 2) = 1 Then Application.EnableEvents = False MsgBox "Fires ok" Application.EnableEvents = True End If -- Rick (MVP - Excel) "Harold Good" wrote in message ... Hi, I'm trying to get some code to fire, based on a formula in cell E2 changing value to equal 1. Private Sub Worksheet_Calculate() If Cells(5, 2) = 1 Then MsgBox "Fires ok" 'Application.EnableEvents = False End Sub I can get it to work properly on a blank spreadsheet. However I need to use it on a worksheet that already has a Worksheet Change code. Since it seems like each worksheet can only have one module (page? not sure of the right name) for code, the above code is in the same module as the Change code. Thus, when the entry is made that changes E2 to equal 1, it starts in the above code, then jumps to the Change code and begins running some of that code. If I EnableEvents=False it stops the straying, but still doesn't bring up the MsgBox. Any help would be appreciated. Harold |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Calculate code in same module as Worksheet Change eventgoes astray
For some reason this did not work. It works fine on an independent
worksheet with standalone code, but not when entered as a separate procedure below the Worksheet_Change procedure. It still jumps up into that other code, and does not bring up the msgbox. Thanks for any further thoughts you may have. Harold Rick Rothstein wrote: Try your code this way... If Cells(5, 2) = 1 Then Application.EnableEvents = False MsgBox "Fires ok" Application.EnableEvents = True End If Harold Good wrote: Hi, I'm trying to get some code to fire, based on a formula in cell E2 changing value to equal 1. Private Sub Worksheet_Calculate() If Cells(5, 2) = 1 Then MsgBox "Fires ok" 'Application.EnableEvents = False End Sub I can get it to work properly on a blank spreadsheet. However I need to use it on a worksheet that already has a Worksheet Change code. Since it seems like each worksheet can only have one module (page? not sure of the right name) for code, the above code is in the same module as the Change code. Thus, when the entry is made that changes E2 to equal 1, it starts in the above code, then jumps to the Change code and begins running some of that code. If I EnableEvents=False it stops the straying, but still doesn't bring up the MsgBox. Any help would be appreciated. Harold |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Calculate code in same module as Worksheet Change event goes astray
What do you mean "entered as a separate procedure below the Worksheet_Change
procedure"? You have to incorporate my posted code into your existing Worksheet_Change procedure's code... where might depend on when you need it to execute relative to the other code you have. Without seeing the rest of your code, it is kind of hard to give you any more direction than this. -- Rick (MVP - Excel) "Harold Good" wrote in message ... For some reason this did not work. It works fine on an independent worksheet with standalone code, but not when entered as a separate procedure below the Worksheet_Change procedure. It still jumps up into that other code, and does not bring up the msgbox. Thanks for any further thoughts you may have. Harold Rick Rothstein wrote: Try your code this way... If Cells(5, 2) = 1 Then Application.EnableEvents = False MsgBox "Fires ok" Application.EnableEvents = True End If Harold Good wrote: Hi, I'm trying to get some code to fire, based on a formula in cell E2 changing value to equal 1. Private Sub Worksheet_Calculate() If Cells(5, 2) = 1 Then MsgBox "Fires ok" 'Application.EnableEvents = False End Sub I can get it to work properly on a blank spreadsheet. However I need to use it on a worksheet that already has a Worksheet Change code. Since it seems like each worksheet can only have one module (page? not sure of the right name) for code, the above code is in the same module as the Change code. Thus, when the entry is made that changes E2 to equal 1, it starts in the above code, then jumps to the Change code and begins running some of that code. If I EnableEvents=False it stops the straying, but still doesn't bring up the MsgBox. Any help would be appreciated. Harold |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Calculate code in same module as Worksheet Change eventgoes astray
You're right, I don't really know what proper terms to call things. I'll
insert the code here. On the Project Explorer both the procedures below (Worksheet_Change, and Worksheet_Calculate) are in the Sheet 5 (Categories) Object. The bottom one (Worksheet_Calculate) is the problem one. I've set a trap to stop it on the /"If Cells(5, 2) = 1 Then MsgBox "Fires ok"/" line, then I F8 step thru it. When it finishes the End Sub, it jumps to the top of this Worksheet_Change code and starts going thru it. So I'm trying to get it to work properly, then build code around it, ultimately so if it equals 1, then unhide some rows. Thanks again for your kind help. Harold Private Sub Worksheet_Change(ByVal Target As Range) 'Code below catches any changes made to the green 'table on the Categories page. When any change is made 'the code is triggered and it hides the unused rows of the 'budget on the Budget page. Dim r As Range, cell As Range Set t = Target ActiveSheet.Unprotect Password:="budg" Range("E10:N29").Interior.Color = RGB(213, 255, 215) Range("F10").Interior.Color = RGB(255, 255, 189) Range("E10:N29").Locked = False Range("F10").Locked = True If Not Intersect(t, Range("E10:N29")) Is Nothing Then Dim rngEval As Range Dim rngHide As Range Dim rngCell As Range Set rngEval = Sheets("Budget").Range("BudgetRowsForHiding") Application.ScreenUpdating = False For Each rngCell In rngEval.Cells If rngCell.Value = "" Then If rngHide Is Nothing Then Set rngHide = rngCell Debug.Print rngHide.Address Else Set rngHide = Union(rngHide, rngCell) Debug.Print rngHide.Address End If End If Next rngCell 'Debug.Print rngHide.Address rngEval.RowHeight = 12.75 Sheets("Budget").Outline.ShowLevels RowLevels:=1 'Because Hidden rows do not remain hidden on the Budget page when I 'expand the Outline, the only other way to make unused rows remain 'hidden is to use a rowheight of .6. This keeps these unused rows 'out of sight while keeping them also out of sight when expanding 'the Outline on Budget page. Sheets("Budget").Unprotect Password:="budg" 'If there are 30 Account Categories, then don't do the rngHide below. If rngHide Is Nothing Then Else rngHide.RowHeight = 0.6 End If End If Sheets("Budget").Shapes("Group Charts").Visible = True Sheet1.Select Sheet1.Range("NotesRows").Select Selection.EntireRow.Hidden = False Sheets("Budget").Range("H7").Select Sheet5.Select Sheets("Budget").Protect Password:="budg" Application.ScreenUpdating = True Application.EnableEvents = True 'ActiveSheet.Protect Password:="budg" End Sub Private Sub Worksheet_Calculate() Application.EnableEvents = False If Cells(5, 2) = 1 Then MsgBox "Fires ok" Application.EnableEvents = True End Sub Rick Rothstein wrote: What do you mean "entered as a separate procedure below the Worksheet_Change procedure"? You have to incorporate my posted code into your existing Worksheet_Change procedure's code... where might depend on when you need it to execute relative to the other code you have. Without seeing the rest of your code, it is kind of hard to give you any more direction than this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change A Line in A Module Using VBA Code | Excel Programming | |||
Code to change code in a sheet and workbook module | Excel Programming | |||
Pass variables from Worksheet_Calculate sub to Module | Excel Programming | |||
Run worksheet module code from workbook module? | Excel Programming | |||
Create a newworksheet with VBA code and put VBA code in the new worksheet module | Excel Programming |