Solved

EXCEL: Using a subroutine in several worksheets

Posted on 2013-01-07
6
214 Views
Last Modified: 2013-01-07
I would like place a subroutine in the workbook section of the workbook and use it in multiple worksheets.

In the General Section of the workbook code I have this:
Private Sub GlobalSub(str As String)
blah blah blah
End Sub

Open in new window


In the Click event of a combo box I have this:
Private Sub combobox1_Click()
ThisWorkbook.GlobalSub ("TEXT")
End Sub

Open in new window


I get a Method or Data Member not found error in line 2 of the click event.
Thoughts?
0
Comment
Question by:gpchicago08
  • 4
  • 2
6 Comments
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Change Private to Public
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
eg Public Sub GlobalSub(str As String)
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
It would actually be better to put that sub in a code Module.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Closing Comment

by:gpchicago08
Comment Utility
duh.
0
 

Author Comment

by:gpchicago08
Comment Utility
wouldn't that be in the General section of the workbook?
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Again the best thing to do is to either cut and paste that sub (after changing to Public) to an existing code module (like Module1), or

Alt+f11 to get to the IDE
From the menu do Insert|Module
Paste or create a new Public sub

Having done that the code is available everywhere in the workbook.

In any case I'm glad I was able to help.

Marty - MVP 2009 to 2012
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now