Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating module code
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating module code
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 "Phil Hageman" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating module code
"Phil Hageman" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating module code
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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating module code
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 "Phil Hageman" wrote in message ... 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating module code
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 "Phil Hageman" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating module code
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?grou...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 "Phil Hageman" wrote in message ... 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 "Phil Hageman" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code module? | Excel Worksheet Functions | |||
code won't run from a module | Excel Discussion (Misc queries) | |||
Unprotect Code Module in Code | Excel Discussion (Misc queries) | |||
code in module A to not execute a Worksheet_SelectionChange sub of another module | Excel Discussion (Misc queries) | |||
copying vba code to a standard code module | Excel Discussion (Misc queries) |