LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 247
Default 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 ???

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Private Sub Worksheet_Change doesn't work? Sam Kuo[_3_] Excel Programming 2 March 9th 08 08:57 PM
Multiple values in Private Sub Worksheet_Change(ByVal Target As R davemon Excel Discussion (Misc queries) 2 September 21st 07 07:40 PM
Private Sub Worksheet_Change(ByVal Target As Range) Paige Excel Programming 1 May 17th 07 12:16 AM
Private Sub Worksheet_Change(ByVal Target As Range) pd1234321 Excel Programming 5 December 8th 06 04:11 AM
Private Sub Worksheet_Change(ByVal Target As Range) Arturo Excel Programming 1 May 25th 05 03:32 PM


All times are GMT +1. The time now is 11:59 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"