Changing a Sub Procedure Name After the Fact

nryan

Board Regular
Joined
Apr 3, 2015
Messages
56
Hello,

I just renamed a sub procedure in a VB macro from "Sub DropDowns()" to "Sub ProbeSelect()". The macro is written to control a series of Form Control Combo Boxes (hence the name DropDowns) that were embedded in the worksheet (not on a Userform) but I changed a few of the Combo Boxes to Form Control List Boxes so I felt like the Sub procedure name ought to be changed to something more appropriate. Any, that was a mistake because now the macro won't run.

I get a pop up in the workbook that says:

"Cannot run macro WorkbookFileName.xlsm'!DropDowns'. The macro may not be available in this workbook or all macros may have been disabled."

I can change the Sub procedure back to the original name and it goes back to working fine. I'll give up on this if it's too complicated and keep the original name but if there is an easy fix please let me know.

Thank you.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,666
When you say the macro won't run, how are you trying to run it - a button or ...? Why would you expect it to run if you have changed the name of the procedure?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
Try this... in the VB editor, call up the Replace dialog box (key in CTRL+H or click Edit/Replace on menu bar), put Dropdowns in the "Find what" field, put ProbeSelect in the "Replace with" field, select the "Current Project" option button and put a check mark in the "Find Whole Word Only" check box, then click the Replace All button.
 

nryan

Board Regular
Joined
Apr 3, 2015
Messages
56
Thanks for your replies.

JoeMo,

There is a button, but I don't think the button initializes the macro. Anyway the pop up message occurs when the user tries to select an option in any of the Form Control List Boxes or Combo Boxes before the button is clicked. Also, the button is Form Control (not ActiveX) so there is no code for the button that I can find in the VB editor. To answer your question, I guess I expected the procedure to work even though I changed the name because other than the first line of code in the macro:

HTML:
Sub DropDowns()
I don't find any ties to that name in the code. So I thought the name of the Sub was arbitrary, but now I know that's not the case.

In Design Mode I can right click on the List Boxes and Combo Boxes and there is an option "Assign Macro...". I tried assigning all form controls (list boxes, combo boxes and buttons) to the newly renamed Sub "ProbeSelect()" but that didn't help.


Rick,

That method didn't fix it.

Thanks guys.
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,666
Right-click the forms control button and select "Assign Macro ..." then assign the button to the new procedure name.
 

nryan

Board Regular
Joined
Apr 3, 2015
Messages
56
Thanks Joe. I assigned macros to the button and boxes. The workbook can now find the macro but there is an error that occurs on the first line of the code:

HTML:
Sub ProbeSelect()
Compile error: Object Required (Error 424).

I think I'm home free once I get past this problem. The only option I would pursue without help would be to save the VBA code and scrap the workbook and start over, getting the Sub names how I want them the first time. Problem is that would take a day or more to copy the workbook since the original is fairly complex. Any thoughts how I can fix it?

Thanks.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,666
Thanks Joe. I assigned macros to the button and boxes. The workbook can now find the macro but there is an error that occurs on the first line of the code:

HTML:
Sub ProbeSelect()
Compile error: Object Required (Error 424).

I think I'm home free once I get past this problem. The only option I would pursue without help would be to save the VBA code and scrap the workbook and start over, getting the Sub names how I want them the first time. Problem is that would take a day or more to copy the workbook since the original is fairly complex. Any thoughts how I can fix it?

Thanks.
Are you saying the error occurs on the Sub ProbeSelect() line?
 

nryan

Board Regular
Joined
Apr 3, 2015
Messages
56
Hi Joe,

Correct. But I solved the problem by doing the option I talked about, scrapping the workbook and keeping the code. Now I'm dealing with some other problems. I might start a thread on it later, but i just had a coworker who is much more familiar with VBA than I check out the current problems I'm having and after 15 minutes he couldn't figure it out. The original code was written by a mechanical engineering intern a few years ago and I think he brute forced the code and it sort of works by coincidence. I think trying to change it too much is a lost cause.

Thank you.
 

Forum statistics

Threads
1,081,729
Messages
5,360,930
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top