Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.EnableEvents help
When a user first opens a template worksheet he clicks a button which runs a
macro containing unput boxes for him to input information. If, during the course of filling out the worksheet, he changes the value in cell B5 or cel E5 I need the input boxes to run again. In order to keep the first input boxes from running continually, I know I need Application.EnableEvents in the worksheet_change code. I can't get the worksheet_change code to work properly, however. The initial input boxes either run continually or the macros listed below don't run at all when B5 or E5 change. Can someone help me fix the below code. Thanks. fyi - I have 3 modules. The first one runs all input boxes. The one referenced below called input_fieldsNoPartNumber runs all input boxes but the partnumber box, which initially populates cell B5 and the module called input_fieldsnochangelevel runs all input boxes except the change level field, which initially populates cell E5. code below is one of the many variations I've tried. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$5" And Range("AA5").Value < "" Then Application.EnableEvents = False Call Input_FieldsNoPartNumber 'Application.EnableEvents = True End If 'Exit Sub If Target.Address = "$E$5" And Range("AA5").Value < "" Then Application.EnableEvents = False Call Input_FieldsNoChangeLevel Application.EnableEvents = True 'Exit Sub End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.EnableEvents help
Private Sub Worksheet_Change(ByVal Target As Range)
On Error goto ErrHandler If Target.Address = "$B$5" And Range("AA5").Value < "" Then Application.EnableEvents = False Call Input_FieldsNoPartNumber Elseif Target.Address = "$E$5" And Range("AA5").Value < "" Then Application.EnableEvents = False Call Input_FieldsNoChangeLevel End If ErrHandler: Application.EnableEvent = True End Sub Would be my suggestion. the procedures shouldn't be called unless you make a change in B5 or E5. -- Regards, Tom Ogilvy "Alex" wrote: When a user first opens a template worksheet he clicks a button which runs a macro containing unput boxes for him to input information. If, during the course of filling out the worksheet, he changes the value in cell B5 or cel E5 I need the input boxes to run again. In order to keep the first input boxes from running continually, I know I need Application.EnableEvents in the worksheet_change code. I can't get the worksheet_change code to work properly, however. The initial input boxes either run continually or the macros listed below don't run at all when B5 or E5 change. Can someone help me fix the below code. Thanks. fyi - I have 3 modules. The first one runs all input boxes. The one referenced below called input_fieldsNoPartNumber runs all input boxes but the partnumber box, which initially populates cell B5 and the module called input_fieldsnochangelevel runs all input boxes except the change level field, which initially populates cell E5. code below is one of the many variations I've tried. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$5" And Range("AA5").Value < "" Then Application.EnableEvents = False Call Input_FieldsNoPartNumber 'Application.EnableEvents = True End If 'Exit Sub If Target.Address = "$E$5" And Range("AA5").Value < "" Then Application.EnableEvents = False Call Input_FieldsNoChangeLevel Application.EnableEvents = True 'Exit Sub End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.EnableEvents help
I've tried this before and it doesn't work. The following code works great;
the user opens a file and clicks the button which runs the initial input boxes, which only run once. Then, if a user changes contents in cell B5, the Input_fieldsNoPartNumber module runs as it should. If I use your code, which I've tried before, the initial input boxes that trigger on the button click run once and then the Input_fieldsNoChangeLevel module runs continually. fyi - cell AA5 is text that is set with the initial input boxes, which I added so that the workseet_change wouldn't trigger when the initial input boxes change the value in cells b5 and e5. Any ideas? thanks. On Error GoTo ErrHandler If Target.Address = "$B$5" And Range("AA5").Value < "" Then Application.EnableEvents = False Call Input_FieldsNoPartNumber End If ErrHandler: Application.EnableEvents = True "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) On Error goto ErrHandler If Target.Address = "$B$5" And Range("AA5").Value < "" Then Application.EnableEvents = False Call Input_FieldsNoPartNumber Elseif Target.Address = "$E$5" And Range("AA5").Value < "" Then Application.EnableEvents = False Call Input_FieldsNoChangeLevel End If ErrHandler: Application.EnableEvent = True End Sub Would be my suggestion. the procedures shouldn't be called unless you make a change in B5 or E5. -- Regards, Tom Ogilvy "Alex" wrote: When a user first opens a template worksheet he clicks a button which runs a macro containing unput boxes for him to input information. If, during the course of filling out the worksheet, he changes the value in cell B5 or cel E5 I need the input boxes to run again. In order to keep the first input boxes from running continually, I know I need Application.EnableEvents in the worksheet_change code. I can't get the worksheet_change code to work properly, however. The initial input boxes either run continually or the macros listed below don't run at all when B5 or E5 change. Can someone help me fix the below code. Thanks. fyi - I have 3 modules. The first one runs all input boxes. The one referenced below called input_fieldsNoPartNumber runs all input boxes but the partnumber box, which initially populates cell B5 and the module called input_fieldsnochangelevel runs all input boxes except the change level field, which initially populates cell E5. code below is one of the many variations I've tried. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$5" And Range("AA5").Value < "" Then Application.EnableEvents = False Call Input_FieldsNoPartNumber 'Application.EnableEvents = True End If 'Exit Sub If Target.Address = "$E$5" And Range("AA5").Value < "" Then Application.EnableEvents = False Call Input_FieldsNoChangeLevel Application.EnableEvents = True 'Exit Sub End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.EnableEvents help
I got it - it was my test. The way to code was organized works great.
thanks again Tom. "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) On Error goto ErrHandler If Target.Address = "$B$5" And Range("AA5").Value < "" Then Application.EnableEvents = False Call Input_FieldsNoPartNumber Elseif Target.Address = "$E$5" And Range("AA5").Value < "" Then Application.EnableEvents = False Call Input_FieldsNoChangeLevel End If ErrHandler: Application.EnableEvent = True End Sub Would be my suggestion. the procedures shouldn't be called unless you make a change in B5 or E5. -- Regards, Tom Ogilvy "Alex" wrote: When a user first opens a template worksheet he clicks a button which runs a macro containing unput boxes for him to input information. If, during the course of filling out the worksheet, he changes the value in cell B5 or cel E5 I need the input boxes to run again. In order to keep the first input boxes from running continually, I know I need Application.EnableEvents in the worksheet_change code. I can't get the worksheet_change code to work properly, however. The initial input boxes either run continually or the macros listed below don't run at all when B5 or E5 change. Can someone help me fix the below code. Thanks. fyi - I have 3 modules. The first one runs all input boxes. The one referenced below called input_fieldsNoPartNumber runs all input boxes but the partnumber box, which initially populates cell B5 and the module called input_fieldsnochangelevel runs all input boxes except the change level field, which initially populates cell E5. code below is one of the many variations I've tried. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$5" And Range("AA5").Value < "" Then Application.EnableEvents = False Call Input_FieldsNoPartNumber 'Application.EnableEvents = True End If 'Exit Sub If Target.Address = "$E$5" And Range("AA5").Value < "" Then Application.EnableEvents = False Call Input_FieldsNoChangeLevel Application.EnableEvents = True 'Exit Sub End If End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.EnableEvents help
Tom,
I thought it was working, but it's not. Do you know what would cause the below code to run when it should if cell B5 was the target, but if I only change the target cell to E5, it runs even if Range("AA5").Value = "". It only runs once though and at the end I get an error box, "application defined or object define error?" Thanks. On Error goto ErrHandler If Target.Address = "$B$5" And Range("AA5").Value < "" Then Application.EnableEvents = False Call Input_FieldsNoPartNumber End If ErrHandler: Application.EnableEvent = True End Sub "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) On Error goto ErrHandler If Target.Address = "$B$5" And Range("AA5").Value < "" Then Application.EnableEvents = False Call Input_FieldsNoPartNumber Elseif Target.Address = "$E$5" And Range("AA5").Value < "" Then Application.EnableEvents = False Call Input_FieldsNoChangeLevel End If ErrHandler: Application.EnableEvent = True End Sub Would be my suggestion. the procedures shouldn't be called unless you make a change in B5 or E5. -- Regards, Tom Ogilvy "Alex" wrote: When a user first opens a template worksheet he clicks a button which runs a macro containing unput boxes for him to input information. If, during the course of filling out the worksheet, he changes the value in cell B5 or cel E5 I need the input boxes to run again. In order to keep the first input boxes from running continually, I know I need Application.EnableEvents in the worksheet_change code. I can't get the worksheet_change code to work properly, however. The initial input boxes either run continually or the macros listed below don't run at all when B5 or E5 change. Can someone help me fix the below code. Thanks. fyi - I have 3 modules. The first one runs all input boxes. The one referenced below called input_fieldsNoPartNumber runs all input boxes but the partnumber box, which initially populates cell B5 and the module called input_fieldsnochangelevel runs all input boxes except the change level field, which initially populates cell E5. code below is one of the many variations I've tried. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$5" And Range("AA5").Value < "" Then Application.EnableEvents = False Call Input_FieldsNoPartNumber 'Application.EnableEvents = True End If 'Exit Sub If Target.Address = "$E$5" And Range("AA5").Value < "" Then Application.EnableEvents = False Call Input_FieldsNoChangeLevel Application.EnableEvents = True 'Exit Sub End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Application.EnableEvents | Excel Worksheet Functions | |||
Application.EnableEvents ... Is Local or Global ? | Excel Programming | |||
application.EnableEvents | Excel Discussion (Misc queries) | |||
application.enableEvents | Excel Programming | |||
Application.EnableEvents | Excel Programming |