Disable Combo Box On_Change Macro

[Randy]

I have a procedure that works out of an .XLA file that makes various
changes to another workbook. In the workbook, there is a combo box
(from the Control Toolbox, not the Forms) that has an "on_change"
macro associated with it.

My dilemma arises from the fact that the XLA file is modifying the
named range that is assigned to the combo box in the ListFillRange
(it's source of data for the drop down list). When this happens,
control passes over the Private Sub ComboBox1_Change() and this
procedure then takes over temporarily. These two procedures conflict
with one another and my main procedure does not run correctly.

What I am trying to do is to simply disable the on change macro from
the combo box. When the XLA file opens the workbook containing the
combo box, I am trying to find a way to disable any macros in this
workbook. I tried to use Application.EnableEvents = False, but this
didn't change anything. The combo box on change macro still ran and
screwed everything up.

Can anybody help?

TIA
Randy Eastland

 

[Tom Ogilvy]

Private sub Combobox1_change()
Dim bGo as boolean
bGo = [cboFlag]
if not bGo exit sub
' current code
End Sub


Have your Addin set the value of the defined name cboFlag

Activesheet.Names("cboFlag").RefersTo:="=False"

at the end of our work
Activesheet.Names("cboFlag").RefersTo:="=True"


For example the macro to alter values in the listfillrange:

Sub CCC()
ActiveWorkbook.Names("cboFlag").RefersTo = "=False"
With Worksheets("Added2")
Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
End With
For Each Cell In rng
Cell.Value = Int(Rnd() * 15 + 1)
Next
ActiveWorkbook.Names("cboFlag").RefersTo = "=True"

End Sub

 

[Randy Eastland]

Thanks, Tom. I appreciate your help.

I'm having problems applying your suggestion. When I get to the line of
code that I think sets the flag to false, I get an "Application defined
or object defined error". Here is my code in the addin file:

Sub UpdateViewList()

Application.EnableEvents = False

'First, select the set of custom view names in row one
Worksheets("Settings").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

'Line inserted per advice of Tom Ogilvy
ActiveSheet.Names("CustomViewList").RefersTo = "=False"

'Paste this list (transposed) so that the list box can pull from it
Application.Goto Reference:="TopViewList"
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True

'Update the named range "CustomViewList" so that the drop down list
pulls the correct values
Selection.CurrentRegion.Name = "CustomViewList"

Application.EnableEvents = True
ActiveSheet.Names("CustomViewList").RefersTo = "=True"

End Sub


The range name is called "CustomViewList". Did I apply this correctly?
It would seem not. It is on the paste special line that the on_change
event is activated. That is what I am trying to suppress.

Thanks again, Tom. Your help is appreciated.
Randy Eastland

 

[Tom Ogilvy]

As I illustrated, the name is a special name only associated with telling
the "on_change" routine if it should immediately exit or continue on.

Customview seems to have something to do with your data, so you shouldn't
use it. Use a dedicated name as I illustrated. Make it a book level name

Activeworkbook.Names.Add:="cboList", RefersTo:="=False"



ActiveWorkbook.Names("cboList").RefersTo:="=True"

in the Event code

Private Sub cboList_Change()
if Not [cboList] then exit sub


another approach might be to use the click event rather than the change
event. This might not fire if you change the listfillrange.

Or you could try changing the listfillrange but I believe this would fire
the event.

 

[Vasant Nanavati]

I think Tom may have left out a step. You will need to initially set the
defined name cboFlag:

Insert | Name | Define | Names in Workbook: cboFlag | Refers To: =FALSE | OK

And use that name in the code rather than "CustomViewList".

--

Vasant



ActiveWorkbook.Names.Add "cboFlag", "=False"

 

[Vasant Nanavati]

Or, of course, set it in code as Tom illustrated.

 

[Tom Ogilvy]

No, you are correct. It needs to be defined before any code is run since
the user may be making selections from the combobox before the Addin does
its work. I just chose to use the ADD method in the second email just for
variety.

 

[Randy Eastland]

Thanks guys. I didn't realize in your first response that cboFlag was
referring to a named range. Vasant's comment made it all clear. Great
suggestion, Tom. It works perfectly!

Randy Eastland