[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

Need Excel sheets to share a macro

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
ksander
Asked:
ksander
  • 8
  • 3
  • 2
  • +2
1 Solution
 
starlCommented:
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
 
criCommented:
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
 
ksanderAuthor Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ksanderAuthor Commented:
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
 
ksanderAuthor Commented:
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
 
starlCommented:
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
 
ksanderAuthor Commented:
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
 
ksanderAuthor Commented:
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
 
starlCommented:
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
 
ksanderAuthor Commented:
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
 
jklmnCommented:
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
 
jklmnCommented:
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
 
ksanderAuthor Commented:
Works great and very cool. Thanks.
0
 
ksanderAuthor Commented:
jklmn - Please watch for another question coming in a few minutes that may require this or similar logic.
0
 
criCommented:
I stated "...especially...". This is not restricted to Subs, it also works for Functions. Never mind.
0
 
calacucciaCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 8
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now