Solved

Need Excel sheets to share a macro

Posted on 2001-08-07
16
306 Views
Last Modified: 2006-11-17
Many sheets in my workbook have their own copy of the same macro.  To reduce space, I'd like there to be only one copy that they all can share.  The macro begins:

"Option Explicit"
"Private Sub Worksheet_Change(ByVal Target As Range)"

It goes into action if there is input in certain cells.

Can it be modified and put in a common area where it will be accessible to all sheets?

Thanks
0
Comment
Question by:ksander
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 2
  • +2
16 Comments
 
LVL 8

Expert Comment

by:starl
ID: 6360433
sure, get rid of the private and put it in a module. you can create/add a module by (in the VB editor) right-click on the project and choose INSERT-MODULE.
course, depending on how your progs written, you may have to make some other changes...
0
 
LVL 13

Expert Comment

by:cri
ID: 6360530
Put them in an add-in (*.xla), especially if you want/need to call them 'manually'. Easiest way I know:
http://www.j-walk.com/ss/excel/tips/tip53.htm
0
 

Author Comment

by:ksander
ID: 6360531
I have already tried putting the code into another module and removing 'Private'. Also tried removing 'Option Explicit'. Didn't work.

The only other way I see that it differs from other macros that are available to all sheets is the "(ByVal Target As Range)" wording. So I removed that too - still didn't work.

I think the answer must be to "make some other changes" but I don't know how.
0
Office 365 Training for Admins

Learn how to provision tenants, synchronize on-premise Active Directory, and implement Single Sign-On with these master level course.  Only from Platform Scholar

 

Author Comment

by:ksander
ID: 6360584
cri - this macro isn't to be called manually. It works fine now - it just watches certain cells for input. If there is, it automatically performs caclulations and stores the results in other cells.  
0
 

Author Comment

by:ksander
ID: 6360631
Also - The macro has lines like:

"If Target.Column <> 4 Then Exit Sub"

that break if "(ByVal Target As Range)" is taken out.

I believe what's happening is that if the "ByVal" code is there, the macro can't be seen, but, it's required for functions within the macro.

So some other changes have to be made.  I don't have a clue what they would be.
0
 
LVL 8

Expert Comment

by:starl
ID: 6360739
post the code.
btw - how is it not working w/the changes? compilation error? Since it was originally a worksheet script, we may just have to be more explicit in certain areas since it's moving out to the workbook.
also, there may be some variables you have to declare as Const
0
 

Author Comment

by:ksander
ID: 6360774
Also - The macro has lines like:

"If Target.Column <> 4 Then Exit Sub"

that break if "(ByVal Target As Range)" is taken out.

I believe what's happening is that if the "ByVal" code is there, the macro can't be seen, but, it's required for functions within the macro.

So some other changes have to be made.  I don't have a clue what they would be.
0
 

Author Comment

by:ksander
ID: 6360911
If I remove 'Private' and/or 'Option Explicit', it just isn't seen - the worksheet doesn't react and when I go to Tools-Macro it isn't there.

When I remove "ByVal" I can see it in Macros. If I enter something that should cause a reaction nothing happens. If I try to Run from the Macro list I get "Run-time error - object required".  That's the "break" I mentioned.

Here's the original code:
***********************
Option Explicit
--------------
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Long       'Row of changed value
  Dim c As Long       'Currently handled column in loop
  Dim x As Double     'Value entered into column 4
  Dim d As Integer    'Number of days in the month
  Dim leftover As Long  'Leftover to be distributed
     
  If Target.Column <> 4 Then Exit Sub
  If Target.Row >= 13 And Target.Row <= 35 Then GoTo UseIt
  Exit Sub
UseIt:
  If Target.Areas.Count <> 1 Then Exit Sub
  If Target.Cells.Count <> 1 Then Exit Sub
  ' Find row number of changed cell
  r = Target.Row
  ' Find #of days in the month (highest value of row 9)
  d = WorksheetFunction.Max(Range("9:9"))
  ' Read the value in column D, store as variable x.
  On Error Resume Next
  x = CDbl(Cells(r, 4))
  If x = 0 Then
    'Reset number format to default
     Cells(r, 4).NumberFormat = "0_);(0)"
    'Save 0 for all days of the month
     Cells(r, 6).Resize(1, d).Value = 0
  End If
  If x >= 1 Then
    ' Change number format
     Cells(r, 4).NumberFormat = "0_);(0)"
    ' Put x / d, rounded, into all days of the month
    Cells(r, 6).Resize(1, d).Value = CLng(x / d)
    ' Calculate the remaining amount to be distributed
    leftover = x - (CLng(x / d) * d)
    ' Distribute the leftover amount randomly
    Do Until leftover = 0
      'Find a random column
      c = Int(Rnd * d) + 6
      'If the number in this column hasn't been changed
      If Cells(r, c).Value = CLng(x / d) Then
        'Add 1 (or -1) to the value
        Cells(r, c).Value = Cells(r, c).Value +    (leftover / Abs(leftover))
        'Decrease leftover with 1 (or -1)
        leftover = leftover - (leftover / Abs(leftover))
      End If
    Loop
  End If
End Sub
 
 

0
 
LVL 8

Expert Comment

by:starl
ID: 6361068
well, for starters, having a ByVal means that you are going to pass a value in.. the only way of doing that (that I know of) is to call this macro from within another macro.
How were you calling the macro originally?
0
 

Author Comment

by:ksander
ID: 6361103
I don't think the macro is being called by another.  All I have to do to invoke it is to go (by selecting its tab) to one of the sheets that 'housed' the macro.

Then nothing happens until I made an entry into one of the "watched" cells.
0
 
LVL 5

Accepted Solution

by:
jklmn earned 100 total points
ID: 6361128
Hi ksander,

It looks very easy.....hopefully, I am right:-)

In each Sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
MyWorksheet_Change Target
End Sub

In a module:
Public Sub MyWorksheet_Change(ByVal Target As Range)
MsgBox "Yes, I know."
End Sub

You may need some midification though.
0
 
LVL 5

Expert Comment

by:jklmn
ID: 6361142
I mean put the original code to
Public Sub MyWorksheet_Change(ByVal Target As Range)
in a module, and call this sub from
Private Sub Worksheet_Change(ByVal Target As Range)
in each sheets.
0
 

Author Comment

by:ksander
ID: 6361163
Works great and very cool. Thanks.
0
 

Author Comment

by:ksander
ID: 6361215
jklmn - Please watch for another question coming in a few minutes that may require this or similar logic.
0
 
LVL 13

Expert Comment

by:cri
ID: 6361434
I stated "...especially...". This is not restricted to Subs, it also works for Functions. Never mind.
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 6361815
You do not need this MyWorksheet_Change macro, as it already exists in the ThisWorkbook module, where you have this event

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)

End Sub

This will apply to all worksheets.
0

Featured Post

Office 365 Advanced Training for Admins

Special Offer:  Buy 1 course, get 2nd free!  Buy the 'Managing Office 365 Identities & Requirements' course w/ Accelerated TestPrep, and automatically receive the 'Enabling Office 365 Services' course FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Custom Checklist creation 2 47
Auto save MS Word - shortest intervals 10 23
Shading Cells between cells that are populated 4 28
Excel Auto-Complete lines 4 32
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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…

737 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