Consolidating module code

[Phil Hageman]

I administer 40 Excel workbooks, all alike, on a network.
The only difference among the workbooks is the data in
them. There are seven worksheets in each workbook, of
which four worksheets are exactly alike. In those four
worksheets (tab named Customer, Finance, Learning,
Processes) I have to impose the effect of this code:

If [M15] <= [M16] Then
MsgBox "In the first strategy: Target cannot be
greater than, or equal to Chart Max"

ElseIf [M47] <= [M48] Then
MsgBox "In the second strategy: Target cannot be
greater than, or equal to Chart Max"

ElseIf [M79] <= [M80] Then
MsgBox "In the third strategy: Target cannot be
greater than, or equal to Chart Max"

........etc.

By itself, this code does what I want it to do.

Here is what I need:
1.) I want to place the code in each workbook's module
(there is only one module), so that it operates on the
four specified worksheets.
2.) The code should require correction of incorrect data
entry before the user is allowed to leave the worksheet.
3.) If you see additional improvements in this code,
please suggest - I am not a programmer

Thanks, Phil

 

[Bob Kilmer]

How do you plan to call the code? In other words, what will cause the code
to run. When do you want it to run? When the workbook opens? When the sheet
activates? When the user clicks a button?

 

[Keith Willshaw]

I administer 40 Excel workbooks, all alike, on a network.
The only difference among the workbooks is the data in
them. There are seven worksheets in each workbook, of
which four worksheets are exactly alike. In those four
worksheets (tab named Customer, Finance, Learning,
Processes) I have to impose the effect of this code:

If [M15] <= [M16] Then
MsgBox "In the first strategy: Target cannot be
greater than, or equal to Chart Max"

ElseIf [M47] <= [M48] Then
MsgBox "In the second strategy: Target cannot be
greater than, or equal to Chart Max"

ElseIf [M79] <= [M80] Then
MsgBox "In the third strategy: Target cannot be
greater than, or equal to Chart Max"

.......etc.

By itself, this code does what I want it to do.

Here is what I need:
1.) I want to place the code in each workbook's module
(there is only one module), so that it operates on the
four specified worksheets.
2.) The code should require correction of incorrect data
entry before the user is allowed to leave the worksheet.
3.) If you see additional improvements in this code,
please suggest - I am not a programmer

Thanks, Phil

Transfer the code to an add-in

Keith

 

[keepitcool]

you dont need code..

use DataValidation to ensure valid data.
set it up in 1 of the books.





then in THAT book write a few lines of code.
you'll need it once only.

it quick and dirty but should serve it's purpose
it will work if the books are indeed EXACTLY the same
same no of rows etc etc

please test!
and copy the directory with the files before processing.
so you'll have backup!!!!


sub CopyValidation()
dim wb as workbook
dim ws as worksheet

for each wb in workbooks
if wb.name <> thisworkbook.name then
for each ws in thisworkbook.worksheets
ws.usedrange.copy
wb.sheets(ws.name).cells.pastespecial xlPasteValidation
next
wb.close true
endif
next

end sub

NOW open ALL the workbooks (or do it in sets of 5 or 10)
and run the macro.

optionally remove the module from the masterbook.



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Phil Hageman]

The user has opened the workbook and worksheet - the code
will run when user inputs incorrect data in the "M" cells
in any of the four worksheets. If they violate the "<="
requirements, the message box comes up - whereupon the
entry must be corrected, or the msg box keeps coming up.

In this thread, "Keepitcool" suggests using data
validation. This approach would work; however,
implementing validation would take forever to enter, and,
to maintain. The reason I want to use the workbook module
approach is to copy/paste the code one time in each of the
40 workbooks. Much easier. As to Keith's idea, what is
involved with the addin approach?

Thanks, Phil



Thanks, Phil

-----Original Message-----
How do you plan to call the code? In other words, what will cause the code
to run. When do you want it to run? When the workbook opens? When the sheet
activates? When the user clicks a button?

--
Bob Kilmer

I administer 40 Excel workbooks, all alike, on a network.
The only difference among the workbooks is the data in
them. There are seven worksheets in each workbook, of
which four worksheets are exactly alike. In those four
worksheets (tab named Customer, Finance, Learning,
Processes) I have to impose the effect of this code:

If [M15] <= [M16] Then
MsgBox "In the first strategy: Target cannot be
greater than, or equal to Chart Max"

ElseIf [M47] <= [M48] Then
MsgBox "In the second strategy: Target cannot be
greater than, or equal to Chart Max"

ElseIf [M79] <= [M80] Then
MsgBox "In the third strategy: Target cannot be
greater than, or equal to Chart Max"

.......etc.

By itself, this code does what I want it to do.

Here is what I need:
1.) I want to place the code in each workbook's module
(there is only one module), so that it operates on the
four specified worksheets.
2.) The code should require correction of incorrect data
entry before the user is allowed to leave the worksheet.
3.) If you see additional improvements in this code,
please suggest - I am not a programmer

Thanks, Phil

.

 

[Bob Kilmer]

Serving suggestion:

'Can put this in an add-in distributed to users.
'Can put this in a standard module in each workbook.
'Which one depends on which you see as easier to maintain.

Public Function ValuesAreOK(wks As Worksheet, strMsg As String) As Boolean
'Returns a boolean indicating if values are ok.
'Also returns message string if they are not.
Dim blnPassedTest As Boolean
With wks
If .[M15].Value <= .[M16].Value Then
strMsg = "In the first strategy: Target cannot " & _
"be greater than, or equal to Chart Max"
blnPassedTest = False
ElseIf .[M47].Value <= .[M48].Value Then
strMsg = "In the second strategy: Target cannot " & _
"be greater than, or equal to Chart Max"
blnPassedTest = False
ElseIf .[M79].Value <= .[M80].Value Then
strMsg = "In the third strategy: Target cannot be " & _
"greater than, or equal to Chart Max"
blnPassedTest = False
End If
End With
ValuesAreOK = blnPassedTest
End Function

'Example of call ----------------

'Call ValuesAreOK from any project.
'This example assumes ValuesAreOK is in
'an add-in named AddInTest.xla.

Sub Main()
'This code could be placed in or called from a
'workbook or worksheet event, or a
'command button or toolbar button, etc.

'Have to call ValuesAreOK for each worksheet
'as required.

Dim strMsg As String

If Application.Run("AddInTest.xla!ValuesAreOK", _
ThisWorkbook.Worksheets("Customer"), strMsg) Then
'execute code if values are ok here
Else
MsgBox strMsg & vbNewLine & _
"Please correct values before continuing."

'If more code follows this If block that needs to be
'skipped, you could add an Exit Sub here.
'Exit Sub.

End If

End Sub

'Or --------

'Call ValuesAreOK from any project.
'This example assumes ValuesAreOK is in
'the same workbook as the caller.
'Have to call ValuesAreOK for each worksheet
'as required.

Sub Main2()
'This code could be placed in or called from a
'workbook or worksheet event, or a
'command button or toolbar button, etc.

Dim strMsg As String

If ValuesAreOK( _
ThisWorkbook.Worksheets("Customer"), strMsg) Then
'execute code if values are ok here.
Else
MsgBox strMsg & vbNewLine & _
"Please correct values before continuing."

'If more code follows this If block that needs to be
'skipped, you could add an Exit Sub here.
'Exit Sub.

End If

End Sub


HTH
--
Bob Kilmer

I administer 40 Excel workbooks, all alike, on a network.
The only difference among the workbooks is the data in
them. There are seven worksheets in each workbook, of
which four worksheets are exactly alike. In those four
worksheets (tab named Customer, Finance, Learning,
Processes) I have to impose the effect of this code:

If [M15] <= [M16] Then
MsgBox "In the first strategy: Target cannot be
greater than, or equal to Chart Max"

ElseIf [M47] <= [M48] Then
MsgBox "In the second strategy: Target cannot be
greater than, or equal to Chart Max"

ElseIf [M79] <= [M80] Then
MsgBox "In the third strategy: Target cannot be
greater than, or equal to Chart Max"

.......etc.

By itself, this code does what I want it to do.

Here is what I need:
1.) I want to place the code in each workbook's module
(there is only one module), so that it operates on the
four specified worksheets.
2.) The code should require correction of incorrect data
entry before the user is allowed to leave the worksheet.
3.) If you see additional improvements in this code,
please suggest - I am not a programmer

Thanks, Phil

 

[Bob Kilmer]

In this thread, "Keepitcool" suggests using data

validation. This approach would work; however,

I don't have much experience with Excels data validation, but what
keepitcool is suggesting is to set it up in one workbook, then copy it
programmatically to the other workbooks. You say it will take "forever" to
implement. I will have to take your word for that. If it is less severe than
that, copying it to the otherworkbooks with a little code is not that
difficult. As for maintenance, you'd only have to change a master copy, then
run the code to update the rest of the workbooks. The same thing could be
done with the macro code.

what is involved with the addin approach?

From your point of view, an addin is mostly just another workbook that can
store code. It can be made to load in the background (See Tools > Add-Ins)
when a user opens Excel. If loaded, the code it contains would be available
to Excel. You would write most of your code in a workbook, save the workbook
as an addin, adding a password perhaps to hide the code from all but the
most determined users and against accidental modification. You would make
this addin available to your users, presumably from a network location. You
might see Add-In Loader Version 2: AddloaderV2.ZIP at www.DecisionModels.com
if only for the discussion of the issues it addresses, or look up add-ins at
http://groups.google.com/groups?group=microsoft.public.excel.* . Your
workbooks would still have to call the code stored in the addin.

I have used addins successfully and have also maintained code in multiple
workbooks on a network. Personally, I think the addin route just complicates
matters for you without contributing much. I'd tend toward copying the code
programmatically from a master workbook to the user workbooks initially and
in case maintenance changes are needed.

--
Bob Kilmer

The user has opened the workbook and worksheet - the code
will run when user inputs incorrect data in the "M" cells
in any of the four worksheets. If they violate the "<="
requirements, the message box comes up - whereupon the
entry must be corrected, or the msg box keeps coming up.

In this thread, "Keepitcool" suggests using data
validation. This approach would work; however,
implementing validation would take forever to enter, and,
to maintain. The reason I want to use the workbook module
approach is to copy/paste the code one time in each of the
40 workbooks. Much easier. As to Keith's idea, what is
involved with the addin approach?

Thanks, Phil



Thanks, Phil

-----Original Message-----
How do you plan to call the code? In other words, what will cause the code
to run. When do you want it to run? When the workbook opens? When the sheet
activates? When the user clicks a button?

--
Bob Kilmer

I administer 40 Excel workbooks, all alike, on a network.
The only difference among the workbooks is the data in
them. There are seven worksheets in each workbook, of
which four worksheets are exactly alike. In those four
worksheets (tab named Customer, Finance, Learning,
Processes) I have to impose the effect of this code:

If [M15] <= [M16] Then
MsgBox "In the first strategy: Target cannot be
greater than, or equal to Chart Max"

ElseIf [M47] <= [M48] Then
MsgBox "In the second strategy: Target cannot be
greater than, or equal to Chart Max"

ElseIf [M79] <= [M80] Then
MsgBox "In the third strategy: Target cannot be
greater than, or equal to Chart Max"

.......etc.

By itself, this code does what I want it to do.

Here is what I need:
1.) I want to place the code in each workbook's module
(there is only one module), so that it operates on the
four specified worksheets.
2.) The code should require correction of incorrect data
entry before the user is allowed to leave the worksheet.
3.) If you see additional improvements in this code,
please suggest - I am not a programmer

Thanks, Phil

.