Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Combo Box On_Change Macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Combo Box On_Change Macro
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 -- Regards, Tom Ogilvy "Randy" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Combo Box On_Change Macro
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Combo Box On_Change Macro
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. -- Regards, Tom Ogilvy "Randy Eastland" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Combo Box On_Change Macro
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" "Randy Eastland" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Combo Box On_Change Macro
Or, of course, set it in code as Tom illustrated.
-- Vasant "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... 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" "Randy Eastland" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Combo Box On_Change Macro
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. -- Regards, Tom Ogilvy "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... Or, of course, set it in code as Tom illustrated. -- Vasant "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... 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" "Randy Eastland" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Combo Box On_Change Macro
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Disable running of SelectionChange macro when in another macro? | Excel Discussion (Misc queries) | |||
Combo Box enable and disable | Excel Worksheet Functions | |||
Disable dropdown list (Combo box -"Form control") | Excel Worksheet Functions | |||
disable user running macro from Tools Macro | Excel Discussion (Misc queries) | |||
On_change , Upper case, file SaveAs | Excel Programming |