Error Code Jumping to Sheet module

[Manish Sharma]

Hi Friends,

I have a written few codes in general module and once code in Sheet module. Here I am sorry because the Excel file is not with me, so i cant post you the general module code, but the private module code is

Private Sub ComboBox1_Change()
  ComboBox2.Value = ""
  ComboBox3.Value = ""
End Sub

My Problem is, when every i execute the general module code it jump to the above sheet module. I tried to use the below line of code in general module, but that is also not stopping it to jump to sheet module. Friends any idea why this is happening?

Application.EnableEvents = False
Code...
Application.EnableEvents = true

Thanks & Regards,
Manish

 

[NARAYANK991]

Hi Manish ,

Can you clarify the following ?

1. When do you execute the general module code ?

2. In which sheet is the Combobox1 present ?

3. When you write :

My Problem is, when ever i execute the general module code it jump to the above sheet module

do you mean that whenever you execute the code which you have not posted , it also executes the code which you have posted ?

4. In your general module code , are you doing anything with Combobox1 ?

Narayan

 

[Manish Sharma]

Hi Manish ,

Can you clarify the following ?

1. When do you execute the general module code ?

2. In which sheet is the Combobox1 present ?

3. When you write :



do you mean that whenever you execute the code which you have not posted , it also executes the code which you have posted ?

4. In your general module code , are you doing anything with Combobox1 ?

Narayan

Dear Narayan,

1. General Module code is used to apply auto-filter based on the selections made by using Combobox 1, 2 and 3
2. ComboBox1, 2 and 3 are present in Sheet3
3. Yes every time the sheet module gets executed along with general module, because of which Combobox2 & 3 values gets cleaned up and codes gives error
4. Yes, in General Module I am pulling the value from ComboBox1, 2 & 3 to apply auto-filter

I can understand it is difficult to find out the issue without seeing the General Module code, but I dont have that code with me. It is not with me, sorry for that.

Thanks,
Manish

 

[Debraj]

Just a Suggestion..
instead
Private Sub ComboBox1_Change()
try to use Private Sub ComboBox1_Click()
as when you change something in the linked cell, its also part of event ComboBox1_Change().
Its really hard to decide, the the Change event is triggered by Clicking on ComboBox or changes in Linked Refrence cell.

I tried to catch the event caller, is this by Sheet/Range change or through click, but failed.

 

[NARAYANK991]

Hi Manish ,

I would like to understand in more detail :

1. When you make a change in ComboBox1 , you are clearing ComboBox2 and ComboBox3 ; if these two comboboxes have their own ComboBox2_Change and ComboBox3_Change event procedures , then the way you are doing the clearing is not correct , since you will be triggering their event procedures. Are you having Application.EnableEvents = False in the ComboBox1_Change event procedure ?

2. The general module code is applying the auto-filter based on the selections made in the 3 comboboxes ; can you confirm that this code does not in any way change the comboboxes ?

3. Why is the Sheet3 code getting executed along with the general module code ? What is this Sheet3 code ?

The issue is not the general module code ; the point is what is the interaction between the general module code and the Sheet3 code ? Is the code for the comboboxes not in a module ?

Narayan

 

[Manish Sharma]

Hi Manish ,

I would like to understand in more detail :

1. When you make a change in ComboBox1 , you are clearing ComboBox2 and ComboBox3 ; if these two comboboxes have their own ComboBox2_Change and ComboBox3_Change event procedures , then the way you are doing the clearing is not correct , since you will be triggering their event procedures. Are you having Application.EnableEvents = False in the ComboBox1_Change event procedure ?

2. The general module code is applying the auto-filter based on the selections made in the 3 comboboxes ; can you confirm that this code does not in any way change the comboboxes ?

3. Why is the Sheet3 code getting executed along with the general module code ? What is this Sheet3 code ?

The issue is not the general module code ; the point is what is the interaction between the general module code and the Sheet3 code ? Is the code for the comboboxes not in a module ?

Narayan

Narayan, I will tell you what exactly the codes are doing.
i) I have three Comboboxes places in Sheet3. User first selects his choice in Combobox1 then in Combobox2 & 3. If supposes all the Combobox is filled and user wants to make come change then all the comboxes post that will become empty (suppose user makes some change in CB2 then CB 3 will become blank, then he will go to CB3 and makes his choice)

ii) General Module - The code here performs Autofilter based on the slections made in CB1, 2 & 3. After the filter is applied, autofilter range is copied and pasted into another sheet and it is formated.

iii) When the above code is being executed, I dont know why it jumps into sheet module, and disruts the whole process

iv) Sheet Module - it contains the code which i have posted earlier to clear CB2 & CB3.

v) Now when the code jumps from General module to Sheet module it clears CB 2 & 3 because of which the code in General module stops it functions because CB 2 & 3 don't have any value to apply autofilter.


Now the answers to your questions Narayan

1) I have tried placing Application.EnableEvents = False in ComboBox1_Change event procedure, but it is not helping me. even i tried to place this in General module, but that also doesn't helped me.

2) General Module codes are mot make changes in the Comboxes. It only takes the value selected in the comboxes from it's linked cell.

3) Sheet 3 codes contains only 2 (Combox1_change, Combox2_change) event code which clears the next combox and fills it fill range for the dropdown list.

 

[NARAYANK991]

Hi Manish ,

First things first ; let us not think in terms of solving a problem. Let us think in terms of good practices.

If , as you say , the code within the ComboBox1_Change event procedure is clearing ComboBox2 and ComboBox3 , then , before the code does anything to these two comboboxes , you should have the Application.EnableEvents = False statement. This is a given. Whether it solves your immediate problem or not is immaterial. It is the correct practice to disable event handling if the code that you are executing is likely to trigger unwanted event procedures.

As you say , when the general module code executes , there is no reason why it should execute the ComboBox1_Change event procedure. First , let us try and understand why this happens. If this problem is resolved , you are done.

In the process of applying the AutoFilter , what exactly is happening ? Have you tried manually applying an AutoFilter and seeing whether the ComboBox1_Change event procedure is being triggered ?

Do you have access to the Sheet3 tab ?

Narayan

 

[Manish Sharma]

Narayan, No i have not yet tried applying manually the AutoFilter; and yes i have access to sheet3 tab but currently the workbook is not with me. It is in my office system.

I can try it tomorrow and let you know what happens after applying manually AutoFilter.