0

I am writing a program that executes a macro after pressing a button.

The code depends on the selection of the drop down box, however I do not want to run any macros after changing the drop down box.

I get this message:

Cannot run the macro "Test.xlsm'!DropDown22_Change" The macro may not be available in this workbook or all macros may be disabled.

How can I disable this attempt to run a macro after I change the selection?

Community
  • 1
  • 1
R S
  • 61
  • 2
  • 11
  • @YowE3K How do I remove the Change event code? I'm not sure where to find it. I haven't written any code with change that I'm aware of. – R S Jan 10 '17 at 19:45
  • @YowE3K Test.xlsm sorry - corrected now. – R S Jan 10 '17 at 19:46
  • Can you right-click on the drop down, and select `Assign Macro`. If it shows something in the `Macro name:` field, delete it, then press OK. – YowE3K Jan 10 '17 at 19:49
  • If it doesn't have `Assign Macro` when you right-click on it, see if it has a `View Code` option. – YowE3K Jan 10 '17 at 19:51
  • Do you have the file Test.xlsm open? Your code might not be able to find the file, because it cannot locate the macro. – VBA Pete Jan 10 '17 at 19:54
  • Thank you! I deleted the Macro name after clicking "Assign Macro" and that helped. My next problem is trying to create an IF statement for the selection of the macro. This is what I'm trying so far: ThisWorkbook.Sheets("Sheet1").Shapes("Drop Down 22").ControlFormat.List.Value = "Option 1" Then.... But I think the format is wrong – R S Jan 10 '17 at 19:57
  • 1
    I assume you haven't assigned the drop-down's `Cell Link`? If so, see http://stackoverflow.com/q/9578038/6535336 (e.g. `ThisWorkbook.Sheets("Sheet1").Shapes("Drop Down 22").oleformat.object.list(ThisWorkbook.Sheets("Sheet1").Shapes("Drop Down 22").oleformat.object.listindex)`). If you **have** assigned the cell link, just access the cell (e.g. `ThisWorkbook.Sheets("Sheet1").Range("B2").Value` if you linked to cell B2) which will give the index - if `"Option 1"` is the first item in the list, the linked cell will contain `1`. – YowE3K Jan 10 '17 at 20:09
  • @YowE3K I added the items to the list of the drop box using VBA without linking them to cells. I had code working for my ActiveX ComboBox where I had an IF statement depending on the "text" selected in the drop down box. Do you know if this is not possible with Form control combo boxes? – R S Jan 10 '17 at 20:15
  • @YowE3K Thank you again! I went to the link you sent and it worked. I used dd.List(dd.ListIndex) as the current selected text in the drop down box. – R S Jan 10 '17 at 20:22
  • I'm glad you sorted it out - I very rarely use drop-downs, etc, so I was getting out of my depth in trying to assist. – YowE3K Jan 10 '17 at 20:25
  • @YowE3K How can I give you a +1 point or something? You were a lot of help. – R S Jan 10 '17 at 20:27
  • I've posted an answer to the original question. (I haven't mentioned anything in the answer about the second question you had, because that should really have been asked in its own question rather than here. And we probably would have then marked that new question as a "duplicate", so let's pretend it never happened. :D ) – YowE3K Jan 10 '17 at 21:06

1 Answers1

0

Assuming your control is a Form Control, and not an ActiveX Control:

To disable the Change event for DropDown22, right-click on the drop-down control, select Assign Macro, and delete the contents of the Macro name: field. Then select OK.

Following these steps, the event should not fire when the user changes the selection within the drop-down.

YowE3K
  • 23,852
  • 7
  • 26
  • 40