Changing a Sub Procedure Name After the Fact

[nryan]

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]

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]

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]

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:

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.

 

[JoeMo]

Right-click the forms control button and select "Assign Macro ..." then assign the button to the new procedure name.

 

[nryan]

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:

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]

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:

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]

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.