Changing a Sub Procedure Name After the Fact

nryan

Board Regular
Joined
Apr 3, 2015
Messages
61
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.
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
36,698
Office Version
  1. 2010
Platform
  1. 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
61
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
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
61
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
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
61
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,010
Messages
5,599,330
Members
414,305
Latest member
scarletX

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top