Error when calling a 'sheet macro' inside a workbook wide macro

[Rhino_dance]

I have a 'sheet macro' named Tabname_1 which simply renames the sheet tab based upon a cell value in the sheet. (By 'sheet macro' I mean that special kind of macro you create by right clicking on the tab name and selecting 'View Code'. It only applies to the current sheet.) Now I want to run this sheet macro within another larger macro that exists in the same workbook. But when I put 'Call Tabname_1' in the main macro I get the error: 'Sub or function not defined'. I also tried simply putting Tabname_1 instead of Call Tabname_1. But I get the same error.

Is there some special syntax to call a sheet macro within a normal macro? Thanks

[xladept]

Hi Rhino_dance,

Could you post your code or even better:Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Rhino_dance]

Here is my main macro:

Please Login or Register  to view this content.

And here is the 'Sheet Macro' named TabName_1 that I am trying to run inside my main macro:

Please Login or Register  to view this content.

Thanks.

Moderator Note: I have added code tags to your VBA. Please read the rules that you agreed to when you joined this forum and adhere to them in the future.

[xladept]

TabName_1() needs to be in a standard, not a sheet, module - is it?

[TMS]

Try

Please Login or Register  to view this content.

[Rhino_dance]

Thanks that worked.

[xladept]

You're welcome and thanks for the rep!

I assume that Trevor's worked as well

[Rhino_dance]

Yes, I used Trevor's answer. I appreciate everyone's input and help on this. -John

[xladept]

Well the only problem I anticipate with Trevor's answer is that the host sheet must be named Sheet1

[TMS]

You're welcome. Thanks for the rep.

xladept is quite right. My solution is using the Code Name for Sheet1 which is, by default, Sheet1. The end user cannot change that from the worksheet ... but you or they could in the VB Editor. Unlikely though.

Strictly speaking, although your routine is only used in relation to Sheet1/Tab1, it is NOT a worksheet macro; it is not an Event handler, or related to a control, etc. So, by rights, it should be in a Standard Module ... then everything can find it.


To create a Standard Module:

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.How to install your new code