| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Private Sub Worksheet_change ... Procedures jamming up
Still trying to figure this out.
For purposes of this question "active" means NOT commented out. There are 5 named "Trigger" Cells and 5 named Ranges. Trigger Cell "X" is to Hide ALL 5 of the named Ranges. Trigger Cell "1", is to hide Named Ranges 2 thru 5 Trigger Cell "2" is to hide Named Ranges 1, and 3 thru 5. etc............... Problem: When only ONE (ANY one) of the "groupings" is Not Commented Out, it works great!!! When the "ALL" group and # 1 Group are Active Select Trigger Cell for "ALL" - Nothing seems to Happen Select Trigger Cell for # 1 - It only Hides Group 5, NOT 2 thru 5 When Groups "ALL, # 1 and # 2" are active Select Trigger Cell for "ALL" - Nothing Seems to Happen Select Trigger Cell for #1 - Nothing Seems to Happen Select Trigger Cell for # 2 - It only Hides Group Range 5, NOT 1, 3 to 5. etc.............. Again - each one on their own works as intended. 'BY CALL METHOD 'ALL Application.EnableEvents = False If Range("FlushBins") = 1 Then 'The following calls all five ranges Call FLBIN_ALL_H Else If Range("FlushBins") = 0 Then Call FLBIN_ALL_UH End If End If Application.EnableEvents = True ' # 1 Application.EnableEvents = False If Range("FBSMINT") = 1 Then ' The following Calls to hide range 2 thru 5 Call FLBIN_SMI_H Else If Range("FBSMINT") = 0 Then ' The following Calls the OTHER FOUR Ranges Call FLBIN_SMI_UH End If End If Application.EnableEvents = True ' # 2 ' If Range("FBSAINT") = 1 Then ' The following Calls to hide Ranges 1, 3 - 5 ' Call FLBIN_SAI_H ' Else ' If Range("FBSAINT") = 0 Then ' Call FLBIN_SAI_UH ' End If ' End If ' Application.EnableEvents = True ' # 3 ' Application.EnableEvents = False ' If Range("FBPART_EXT") = 1 Then ' The following Calls to HIDE Ranges 1, 2, 4, 5 ' Call FLBIN_Part_ExtLid_H ' Else ' If Range("FBPART_EXT") = 0 Then ' Call FLBIN_Part_ExtLid_UH ' End If ' End If ' Application.EnableEvents = True ' # 4 ' Application.EnableEvents = False ' If Range("FBPART_INT") = 1 Then ' The following Calls to Hide Ranges 1 - 3, 5 ' Call FLBIN_Part_IntLid_H ' Else ' If Range("FBPART_INT") = 0 Then ' Call FLBIN_Part_IntLid_UH ' End If ' End If ' Application.EnableEvents = True ' # 5 Application.EnableEvents = False If Range("FBEXTMNT") = 1 Then ' The following Calls to Hide Ranges 1 - 4. Call FLBIN_M_ExtMnt_H Else If Range("FBEXTMNT") = 0 Then Call FLBIN_M_ExtMnt_UH End If End If Application.EnableEvents = True My module "layout" is as follows: I have a std. module I called Range_Groupings: Sample: Sub FB_SMI_H() Range("FBSMINT_S").EntireRow.Hidden = True End Sub Sub FB_SMI_UH() Range("FBSMINT_S").EntireRow.Hidden = False End Sub etc............................ I have a module I've named CALLS sample Sub FLBIN_ALL_H() ' Flush Bins - ALL ' ALL to be Hidden Call FB_SMI_H Call FB_SAI_H Call FB_Part_MIMEL_H Call FB_Part_MIMIL_H Call FB_MEXM_H End Sub Sub FLBIN_ALL_UH() ' Flush Bins - ALL ' ALL to be UN-Hidden Call FB_SMI_UH Call FB_SAI_UH Call FB_Part_MIMEL_UH Call FB_Part_MIMIL_UH Call FB_MEXM_UH End Sub ETC......................... So: My worksheet module makes ONE Call of my "CALL" module, which CALLS as many Ranges as required from my "Range_Grouping" module. This "layout" seems to give me good oversight and control of my project. HOWEVER: When the project doesn't work as intended, it is not of much use. I did try moving the enableEvents true false statements around, but with no sucess. I am now officially desperate. Help please ??? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Private Sub Worksheet_change ... Procedures jamming up
Have you tried putting a breakpoint early in the Worksheet_change and
stepping through it line by line to find the problem? -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "BEEJAY" wrote: Still trying to figure this out. For purposes of this question "active" means NOT commented out. There are 5 named "Trigger" Cells and 5 named Ranges. Trigger Cell "X" is to Hide ALL 5 of the named Ranges. Trigger Cell "1", is to hide Named Ranges 2 thru 5 Trigger Cell "2" is to hide Named Ranges 1, and 3 thru 5. etc............... Problem: When only ONE (ANY one) of the "groupings" is Not Commented Out, it works great!!! When the "ALL" group and # 1 Group are Active Select Trigger Cell for "ALL" - Nothing seems to Happen Select Trigger Cell for # 1 - It only Hides Group 5, NOT 2 thru 5 When Groups "ALL, # 1 and # 2" are active Select Trigger Cell for "ALL" - Nothing Seems to Happen Select Trigger Cell for #1 - Nothing Seems to Happen Select Trigger Cell for # 2 - It only Hides Group Range 5, NOT 1, 3 to 5. etc.............. Again - each one on their own works as intended. 'BY CALL METHOD 'ALL Application.EnableEvents = False If Range("FlushBins") = 1 Then 'The following calls all five ranges Call FLBIN_ALL_H Else If Range("FlushBins") = 0 Then Call FLBIN_ALL_UH End If End If Application.EnableEvents = True ' # 1 Application.EnableEvents = False If Range("FBSMINT") = 1 Then ' The following Calls to hide range 2 thru 5 Call FLBIN_SMI_H Else If Range("FBSMINT") = 0 Then ' The following Calls the OTHER FOUR Ranges Call FLBIN_SMI_UH End If End If Application.EnableEvents = True ' # 2 ' If Range("FBSAINT") = 1 Then ' The following Calls to hide Ranges 1, 3 - 5 ' Call FLBIN_SAI_H ' Else ' If Range("FBSAINT") = 0 Then ' Call FLBIN_SAI_UH ' End If ' End If ' Application.EnableEvents = True ' # 3 ' Application.EnableEvents = False ' If Range("FBPART_EXT") = 1 Then ' The following Calls to HIDE Ranges 1, 2, 4, 5 ' Call FLBIN_Part_ExtLid_H ' Else ' If Range("FBPART_EXT") = 0 Then ' Call FLBIN_Part_ExtLid_UH ' End If ' End If ' Application.EnableEvents = True ' # 4 ' Application.EnableEvents = False ' If Range("FBPART_INT") = 1 Then ' The following Calls to Hide Ranges 1 - 3, 5 ' Call FLBIN_Part_IntLid_H ' Else ' If Range("FBPART_INT") = 0 Then ' Call FLBIN_Part_IntLid_UH ' End If ' End If ' Application.EnableEvents = True ' # 5 Application.EnableEvents = False If Range("FBEXTMNT") = 1 Then ' The following Calls to Hide Ranges 1 - 4. Call FLBIN_M_ExtMnt_H Else If Range("FBEXTMNT") = 0 Then Call FLBIN_M_ExtMnt_UH End If End If Application.EnableEvents = True My module "layout" is as follows: I have a std. module I called Range_Groupings: Sample: Sub FB_SMI_H() Range("FBSMINT_S").EntireRow.Hidden = True End Sub Sub FB_SMI_UH() Range("FBSMINT_S").EntireRow.Hidden = False End Sub etc............................ I have a module I've named CALLS sample Sub FLBIN_ALL_H() ' Flush Bins - ALL ' ALL to be Hidden Call FB_SMI_H Call FB_SAI_H Call FB_Part_MIMEL_H Call FB_Part_MIMIL_H Call FB_MEXM_H End Sub Sub FLBIN_ALL_UH() ' Flush Bins - ALL ' ALL to be UN-Hidden Call FB_SMI_UH Call FB_SAI_UH Call FB_Part_MIMEL_UH Call FB_Part_MIMIL_UH Call FB_MEXM_UH End Sub ETC......................... So: My worksheet module makes ONE Call of my "CALL" module, which CALLS as many Ranges as required from my "Range_Grouping" module. This "layout" seems to give me good oversight and control of my project. HOWEVER: When the project doesn't work as intended, it is not of much use. I did try moving the enableEvents true false statements around, but with no sucess. I am now officially desperate. Help please ??? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Private Sub Worksheet_change ... Procedures jamming up
Hi
First test if any of the target cells have been changed, second I would set EnableEvents = False at the begininng of the macro and then reset it at the end. Try if this will do it: Private Sub Worksheet_Change(ByVal Target As Range) Call Method Application.EnableEvents = False Select Case Target.Address 'ALL Case Is = Range("FlushBins").Address If Range("FlushBins") = 1 Then 'The following calls all five ranges Call FLBIN_ALL_H ElseIf Range("FlushBins") = 0 Then Call FLBIN_ALL_UH End If ' # 1 Case Is = Range("FBSMINT").Address If Range("FBSMINT") = 1 Then ' The following Calls to hide range 2 thru 5 Call FLBIN_SMI_H ElseIf Range("FBSMINT") = 0 Then ' The following Calls the OTHER FOUR Ranges Call FLBIN_SMI_UH End If ' # 2 Case Is = Range("FBSAINT").Address If Range("FBSAINT") = 1 Then ' The following Calls to hide Ranges 1, 3 - 5 Call FLBIN_SAI_H ElseIf Range("FBSAINT") = 0 Then Call FLBIN_SAI_UH End If ' # 3 Case Is = Range("FBPART_EXT").Address If Range("FBPART_EXT") = 1 Then ' The following Calls to HIDE Ranges 1, 2, 4, 5 Call FLBIN_Part_ExtLid_H ElseIf Range("FBPART_EXT") = 0 Then Call FLBIN_Part_ExtLid_UH End If ' # 4 Case Is = Range("FBPART_INT").Address If Range("FBPART_INT") = 1 Then ' The following Calls to Hide Ranges 1 - 3, 5 Call FLBIN_Part_IntLid_H ElseIf Range("FBPART_INT") = 0 Then Call FLBIN_Part_IntLid_UH End If ' # 5 Case Is = Range("FBEXTMNT").Address If Range("FBEXTMNT") = 1 Then ' The following Calls to Hide Ranges 1 - 4. Call FLBIN_M_ExtMnt_H ElseIf Range("FBEXTMNT") = 0 Then Call FLBIN_M_ExtMnt_UH End If End Select Application.EnableEvents = True End Sub Regards, Per On 23 Okt., 18:23, BEEJAY wrote: Still trying to figure this out. For purposes of this question "active" means NOT commented out. There are 5 named "Trigger" Cells and 5 named Ranges. Trigger Cell "X" is to Hide ALL 5 of the named Ranges. Trigger Cell "1", is to hide Named Ranges 2 thru 5 Trigger Cell "2" is to *hide Named Ranges 1, and 3 thru 5. etc............... Problem: When only ONE (ANY one) of the "groupings" is Not Commented Out, it works great!!! When *the "ALL" group and # 1 Group are Active * *Select Trigger Cell for "ALL" - Nothing seems to Happen * *Select Trigger Cell for # 1 - It only Hides Group 5, NOT 2 thru 5 When Groups "ALL, # 1 and # 2" are active * *Select Trigger Cell for "ALL" - Nothing Seems to Happen * *Select Trigger Cell for #1 - Nothing Seems to Happen * *Select Trigger Cell for # 2 - It only Hides Group Range 5, *NOT 1, 3 to 5. etc.............. Again - each one on their own works as intended. 'BY CALL METHOD 'ALL * * Application.EnableEvents = False * * * * If Range("FlushBins") = 1 Then 'The following calls all five ranges * * * * Call FLBIN_ALL_H * * Else * * If Range("FlushBins") = 0 Then * * * * Call FLBIN_ALL_UH * * * * End If * *End If * *Application.EnableEvents = True ' # 1 * * Application.EnableEvents = False * * * *If Range("FBSMINT") = 1 Then ' The following Calls to hide range 2 thru 5 * * * * Call FLBIN_SMI_H * * Else * * If Range("FBSMINT") = 0 Then ' The following Calls the OTHER FOUR Ranges * * * *Call FLBIN_SMI_UH * * * *End If * * End If * * Application.EnableEvents = True ' # 2 ' * * * *If Range("FBSAINT") = 1 Then ' The following Calls to hide Ranges 1, 3 - 5 ' * * * Call FLBIN_SAI_H ' * *Else ' * *If Range("FBSAINT") = 0 Then ' * * * Call FLBIN_SAI_UH ' * * * End If ' * *End If ' * *Application.EnableEvents = True ' # 3 ' * *Application.EnableEvents = False ' * *If Range("FBPART_EXT") = 1 Then ' The following Calls to HIDE Ranges 1, 2, 4, 5 ' * * * Call FLBIN_Part_ExtLid_H ' * *Else ' * *If Range("FBPART_EXT") = 0 Then ' * * * Call FLBIN_Part_ExtLid_UH ' * * * End If ' * *End If ' * *Application.EnableEvents = True ' # 4 ' * *Application.EnableEvents = False ' * *If Range("FBPART_INT") = 1 Then ' The following Calls to Hide Ranges 1 - 3, 5 ' * * * *Call FLBIN_Part_IntLid_H ' * *Else ' * *If Range("FBPART_INT") = 0 Then ' * * * *Call FLBIN_Part_IntLid_UH ' * * * *End If ' * *End If ' * *Application.EnableEvents = True ' # 5 * * Application.EnableEvents = False * * If Range("FBEXTMNT") = 1 Then ' The following Calls to Hide Ranges 1 - 4. * * * * Call FLBIN_M_ExtMnt_H * * Else * * If Range("FBEXTMNT") = 0 Then * * * * Call FLBIN_M_ExtMnt_UH * * * * End If * * End If * * Application.EnableEvents = True My module "layout" is as follows: I have a std. module I called Range_Groupings: Sample: Sub FB_SMI_H() * * * * Range("FBSMINT_S").EntireRow.Hidden = True End Sub Sub FB_SMI_UH() * * * * Range("FBSMINT_S").EntireRow.Hidden = False End Sub etc............................ I have a module I've named CALLS sample Sub FLBIN_ALL_H() ' * Flush Bins - ALL ' * ALL to be Hidden * * * * Call FB_SMI_H * * * * Call FB_SAI_H * * * * Call FB_Part_MIMEL_H * * * * Call FB_Part_MIMIL_H * * * * Call FB_MEXM_H End Sub Sub FLBIN_ALL_UH() ' * Flush Bins - ALL ' * ALL to be UN-Hidden * * * * Call FB_SMI_UH * * * * Call FB_SAI_UH * * * * Call FB_Part_MIMEL_UH * * * * Call FB_Part_MIMIL_UH * * * * Call FB_MEXM_UH End Sub ETC......................... So: My worksheet module makes ONE Call of my "CALL" module, which CALLS as many Ranges as required from my "Range_Grouping" module. This "layout" seems to give me good oversight and control of my project. HOWEVER: When the project doesn't work as intended, it is not of much use. I did try moving the enableEvents true false statements around, but with no sucess. I am now officially desperate. Help please ??? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Private Sub Worksheet_change ... Procedures jamming up
Thank-you.
I "uncommented" all 6 of the groupings. Put in a Break-Point (right on sub worksheet_change.......) Entered in a "1" in Cell "ALL". Then stepped thru it with F8. and stepped ....... and stepped...... It wouldn't quit. It not only went thru all 6 Groupings, but also into the "rest" of my coding. My limited understanding was of the impression that the EnableEvents False/True would not allow this to happen. I did try to change things by commenting out the EnableEvents = True at the end of each of the 6 groupings, but that didn't seem to make a difference. I then commented out group 2 thru 6. Set break-point. F8 thru Group 1. Worked great!! BUT!!! At the end of it, it still kept on stepping.......... I was not expecting this. I was of the impression that the triggered cell would do only its particular instructions, then STOP. IS there a way that it can be made to operate the way I am expecting? I have another 5 or 6 grouping sets that obviously going to give me the same problem. "Barb Reinhardt" wrote: Have you tried putting a breakpoint early in the Worksheet_change and stepping through it line by line to find the problem? -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "BEEJAY" wrote: Still trying to figure this out. For purposes of this question "active" means NOT commented out. There are 5 named "Trigger" Cells and 5 named Ranges. Trigger Cell "X" is to Hide ALL 5 of the named Ranges. Trigger Cell "1", is to hide Named Ranges 2 thru 5 Trigger Cell "2" is to hide Named Ranges 1, and 3 thru 5. etc............... Problem: When only ONE (ANY one) of the "groupings" is Not Commented Out, it works great!!! When the "ALL" group and # 1 Group are Active Select Trigger Cell for "ALL" - Nothing seems to Happen Select Trigger Cell for # 1 - It only Hides Group 5, NOT 2 thru 5 When Groups "ALL, # 1 and # 2" are active Select Trigger Cell for "ALL" - Nothing Seems to Happen Select Trigger Cell for #1 - Nothing Seems to Happen Select Trigger Cell for # 2 - It only Hides Group Range 5, NOT 1, 3 to 5. etc.............. Again - each one on their own works as intended. 'BY CALL METHOD 'ALL Application.EnableEvents = False If Range("FlushBins") = 1 Then 'The following calls all five ranges Call FLBIN_ALL_H Else If Range("FlushBins") = 0 Then Call FLBIN_ALL_UH End If End If Application.EnableEvents = True ' # 1 Application.EnableEvents = False If Range("FBSMINT") = 1 Then ' The following Calls to hide range 2 thru 5 Call FLBIN_SMI_H Else If Range("FBSMINT") = 0 Then ' The following Calls the OTHER FOUR Ranges Call FLBIN_SMI_UH End If End If Application.EnableEvents = True ' # 2 ' If Range("FBSAINT") = 1 Then ' The following Calls to hide Ranges 1, 3 - 5 ' Call FLBIN_SAI_H ' Else ' If Range("FBSAINT") = 0 Then ' Call FLBIN_SAI_UH ' End If ' End If ' Application.EnableEvents = True ' # 3 ' Application.EnableEvents = False ' If Range("FBPART_EXT") = 1 Then ' The following Calls to HIDE Ranges 1, 2, 4, 5 ' Call FLBIN_Part_ExtLid_H ' Else ' If Range("FBPART_EXT") = 0 Then ' Call FLBIN_Part_ExtLid_UH ' End If ' End If ' Application.EnableEvents = True ' # 4 ' Application.EnableEvents = False ' If Range("FBPART_INT") = 1 Then ' The following Calls to Hide Ranges 1 - 3, 5 ' Call FLBIN_Part_IntLid_H ' Else ' If Range("FBPART_INT") = 0 Then ' Call FLBIN_Part_IntLid_UH ' End If ' End If ' Application.EnableEvents = True ' # 5 Application.EnableEvents = False If Range("FBEXTMNT") = 1 Then ' The following Calls to Hide Ranges 1 - 4. Call FLBIN_M_ExtMnt_H Else If Range("FBEXTMNT") = 0 Then Call FLBIN_M_ExtMnt_UH End If End If Application.EnableEvents = True My module "layout" is as follows: I have a std. module I called Range_Groupings: Sample: Sub FB_SMI_H() Range("FBSMINT_S").EntireRow.Hidden = True End Sub Sub FB_SMI_UH() Range("FBSMINT_S").EntireRow.Hidden = False End Sub etc............................ I have a module I've named CALLS sample Sub FLBIN_ALL_H() ' Flush Bins - ALL ' ALL to be Hidden Call FB_SMI_H Call FB_SAI_H Call FB_Part_MIMEL_H Call FB_Part_MIMIL_H Call FB_MEXM_H End Sub Sub FLBIN_ALL_UH() ' Flush Bins - ALL ' ALL to be UN-Hidden Call FB_SMI_UH Call FB_SAI_UH Call FB_Part_MIMEL_UH Call FB_Part_MIMIL_UH Call FB_MEXM_UH End Sub ETC......................... So: My worksheet module makes ONE Call of my "CALL" module, which CALLS as many Ranges as required from my "Range_Grouping" module. This "layout" seems to give me good oversight and control of my project. HOWEVER: When the project doesn't work as intended, it is not of much use. I did try moving the enableEvents true false statements around, but with no sucess. I am now officially desperate. Help please ??? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Private Sub Worksheet_change ... Procedures jamming up
THANK-YOU!!! THANK-YOU!!! THANK-YOU!!!!
This is VERY EXCITING!!! It worked like a dream. Exactly what I needed it to do. I did a step thru on it. It was neat to watch it process the code in the required section, then "end if", and then right down to "end select", by-passing the non-required sections. I double checked the operation of all my "standard" type processes and it all worked great. I then created another "grouping" similar to the first one. It also worked perfectly AND the 1st "grouping" still worked as required AND all my other process still worked as required. I have another 6 (or more) of these groupings to do. I now feel confident that they also will work as expected. Again Thank-you!!! "Per Jessen" wrote: Hi First test if any of the target cells have been changed, second I would set EnableEvents = False at the begininng of the macro and then reset it at the end. Try if this will do it: Private Sub Worksheet_Change(ByVal Target As Range) Call Method Application.EnableEvents = False Select Case Target.Address 'ALL Case Is = Range("FlushBins").Address If Range("FlushBins") = 1 Then 'The following calls all five ranges Call FLBIN_ALL_H ElseIf Range("FlushBins") = 0 Then Call FLBIN_ALL_UH End If ' # 1 Case Is = Range("FBSMINT").Address If Range("FBSMINT") = 1 Then ' The following Calls to hide range 2 thru 5 Call FLBIN_SMI_H ElseIf Range("FBSMINT") = 0 Then ' The following Calls the OTHER FOUR Ranges Call FLBIN_SMI_UH End If ' # 2 Case Is = Range("FBSAINT").Address If Range("FBSAINT") = 1 Then ' The following Calls to hide Ranges 1, 3 - 5 Call FLBIN_SAI_H ElseIf Range("FBSAINT") = 0 Then Call FLBIN_SAI_UH End If ' # 3 Case Is = Range("FBPART_EXT").Address If Range("FBPART_EXT") = 1 Then ' The following Calls to HIDE Ranges 1, 2, 4, 5 Call FLBIN_Part_ExtLid_H ElseIf Range("FBPART_EXT") = 0 Then Call FLBIN_Part_ExtLid_UH End If ' # 4 Case Is = Range("FBPART_INT").Address If Range("FBPART_INT") = 1 Then ' The following Calls to Hide Ranges 1 - 3, 5 Call FLBIN_Part_IntLid_H ElseIf Range("FBPART_INT") = 0 Then Call FLBIN_Part_IntLid_UH End If ' # 5 Case Is = Range("FBEXTMNT").Address If Range("FBEXTMNT") = 1 Then ' The following Calls to Hide Ranges 1 - 4. Call FLBIN_M_ExtMnt_H ElseIf Range("FBEXTMNT") = 0 Then Call FLBIN_M_ExtMnt_UH End If End Select Application.EnableEvents = True End Sub Regards, Per On 23 Okt., 18:23, BEEJAY wrote: Still trying to figure this out. For purposes of this question "active" means NOT commented out. There are 5 named "Trigger" Cells and 5 named Ranges. Trigger Cell "X" is to Hide ALL 5 of the named Ranges. Trigger Cell "1", is to hide Named Ranges 2 thru 5 Trigger Cell "2" is to hide Named Ranges 1, and 3 thru 5. etc............... Problem: When only ONE (ANY one) of the "groupings" is Not Commented Out, it works great!!! When the "ALL" group and # 1 Group are Active Select Trigger Cell for "ALL" - Nothing seems to Happen Select Trigger Cell for # 1 - It only Hides Group 5, NOT 2 thru 5 When Groups "ALL, # 1 and # 2" are active Select Trigger Cell for "ALL" - Nothing Seems to Happen Select Trigger Cell for #1 - Nothing Seems to Happen Select Trigger Cell for # 2 - It only Hides Group Range 5, NOT 1, 3 to 5. etc.............. Again - each one on their own works as intended. 'BY CALL METHOD 'ALL Application.EnableEvents = False If Range("FlushBins") = 1 Then 'The following calls all five ranges Call FLBIN_ALL_H Else If Range("FlushBins") = 0 Then Call FLBIN_ALL_UH End If End If Application.EnableEvents = True ' # 1 Application.EnableEvents = False If Range("FBSMINT") = 1 Then ' The following Calls to hide range 2 thru 5 Call FLBIN_SMI_H Else If Range("FBSMINT") = 0 Then ' The following Calls the OTHER FOUR Ranges Call FLBIN_SMI_UH End If End If Application.EnableEvents = True ' # 2 ' If Range("FBSAINT") = 1 Then ' The following Calls to hide Ranges 1, 3 - 5 ' Call FLBIN_SAI_H ' Else ' If Range("FBSAINT") = 0 Then ' Call FLBIN_SAI_UH ' End If ' End If ' Application.EnableEvents = True ' # 3 ' Application.EnableEvents = False ' If Range("FBPART_EXT") = 1 Then ' The following Calls to HIDE Ranges 1, 2, 4, 5 ' Call FLBIN_Part_ExtLid_H ' Else ' If Range("FBPART_EXT") = 0 Then ' Call FLBIN_Part_ExtLid_UH ' End If ' End If ' Application.EnableEvents = True ' # 4 ' Application.EnableEvents = False ' If Range("FBPART_INT") = 1 Then ' The following Calls to Hide Ranges 1 - 3, 5 ' Call FLBIN_Part_IntLid_H ' Else ' If Range("FBPART_INT") = 0 Then ' Call FLBIN_Part_IntLid_UH ' End If ' End If ' Application.EnableEvents = True ' # 5 Application.EnableEvents = False If Range("FBEXTMNT") = 1 Then ' The following Calls to Hide Ranges 1 - 4. Call FLBIN_M_ExtMnt_H Else If Range("FBEXTMNT") = 0 Then Call FLBIN_M_ExtMnt_UH End If End If Application.EnableEvents = True My module "layout" is as follows: I have a std. module I called Range_Groupings: Sample: Sub FB_SMI_H() Range("FBSMINT_S").EntireRow.Hidden = True End Sub Sub FB_SMI_UH() Range("FBSMINT_S").EntireRow.Hidden = False End Sub etc............................ I have a module I've named CALLS sample Sub FLBIN_ALL_H() ' Flush Bins - ALL ' ALL to be Hidden Call FB_SMI_H Call FB_SAI_H Call FB_Part_MIMEL_H Call FB_Part_MIMIL_H Call FB_MEXM_H End Sub Sub FLBIN_ALL_UH() ' Flush Bins - ALL ' ALL to be UN-Hidden Call FB_SMI_UH Call FB_SAI_UH Call FB_Part_MIMEL_UH Call FB_Part_MIMIL_UH Call FB_MEXM_UH End Sub ETC......................... So: My worksheet module makes ONE Call of my "CALL" module, which CALLS as many Ranges as required from my "Range_Grouping" module. This "layout" seems to give me good oversight and control of my project. HOWEVER: When the project doesn't work as intended, it is not of much use. I did try moving the enableEvents true false statements around, but with no sucess. I am now officially desperate. Help please ??? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Private Sub Worksheet_change ... Procedures jamming up
Thanks for your reply.
I'm glad to help Regards, Per On 24 Okt., 19:10, BEEJAY wrote: THANK-YOU!!! * *THANK-YOU!!! * * THANK-YOU!!!! This is VERY EXCITING!!! It worked like a dream. * Exactly what I needed it to do. I did a step thru on it. It was neat to watch it process the code in the required section, then "end if", and then right down to "end select", by-passing the non-required sections. I double checked the operation of all my "standard" type processes and it all worked great. I then created another "grouping" similar to the first one. It also worked perfectly AND the 1st "grouping" still worked as required AND all my other process still worked as required. I have another 6 (or more) of these groupings to do. I now feel confident that they also will work as expected. Again Thank-you!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Private Sub Worksheet_Change doesn't work? | Excel Programming | |||
| Multiple values in Private Sub Worksheet_Change(ByVal Target As R | Excel Discussion (Misc queries) | |||
| Private Sub Worksheet_Change(ByVal Target As Range) | Excel Programming | |||
| Private Sub Worksheet_Change(ByVal Target As Range) | Excel Programming | |||
| Private Sub Worksheet_Change(ByVal Target As Range) | Excel Programming | |||
Linear Mode
