Condense VBA Code

  • Thread starter Thread starter FrankB
  • Start date Start date
F

FrankB

I have the following code replicated 20 times and works
just fine, however whenever the POWERS TO BE decide to
change the names (TTW) I have to change all references to
those names. There are 5 in every module. A total of 100
manual changes in this module along. Anyway to condense
this VBA module???

Thanks
Frank


Windows("TTW Cap Model.xls").Activate
Application.DisplayAlerts = False
ActiveSheet.Unprotect ("TTW")
ActiveSheet.Shapes("Button 1").Select
Selection.OnAction = "Switchboard.xls!QUICKBASETTW"
Range("A1").Select
ActiveWorkbook.ActiveSheet.PROTECT ("TTW")
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Project Managers\TTW Cap Model.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True
 
Frank

you could try something like this:

Const sWindow = "TTW Cap Model.xls"
Const sPassword = "TTW"
Const sOnAction = "Switchboard.xls!QUICKBASETTW"
Const sFileName = "G:\04Plan\Project Managers\TTW Cap Model.xls"

Windows(sWindow).Activate
Application.DisplayAlerts = False
ActiveSheet.Unprotect (sPassword)
ActiveSheet.Shapes("Button 1").Select
Selection.OnAction = sOnAction
Range("A1").Select
ActiveWorkbook.ActiveSheet.Protect (sPassword)
ActiveWorkbook.SaveAs Filename:=sFileName, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True

Then you only need to change the occurrence once at the beginning of the
(repetitive) code. Note the password gets used twice anyway. I guess the
password can be fixed regardless of what file name changes you make.

Regards

Trevor
 
-----Original Message-----
Frank

you could try something like this:

Const sWindow = "TTW Cap Model.xls"
Const sPassword = "TTW"
Const sOnAction = "Switchboard.xls!QUICKBASETTW"
Const sFileName = "G:\04Plan\Project Managers\TTW Cap Model.xls"

Windows(sWindow).Activate
Application.DisplayAlerts = False
ActiveSheet.Unprotect (sPassword)
ActiveSheet.Shapes("Button 1").Select
Selection.OnAction = sOnAction
Range("A1").Select
ActiveWorkbook.ActiveSheet.Protect (sPassword)
ActiveWorkbook.SaveAs Filename:=sFileName, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True

Then you only need to change the occurrence once at the beginning of the
(repetitive) code. Note the password gets used twice anyway. I guess the
password can be fixed regardless of what file name changes you make.

Regards

Trevor





.
Thanks Trevor, however I would still have to do this 20
Click to expand...
times, right??? I have 20 Workbooks

I was looking for a way to put something like a lookup
table (Case???) in the VBA statement and only have to run
it one time for all 20 Workbooks. Hope I am not confusing
this.

Thanks
Frank
 
Frank

sorry, I misinterpreted what you meant by repeated 20 times. I thought you
literally repeated the code 20 times in the same workbook, not had 20
workbooks with the same code.

In this case, I would put the code into a single workbook once and then run
it from there on each of the workbooks ... it could even mean that you don't
need any code in the original workbooks.

You could then save the "master" workbook in your. Excel Startup directory.

Another option is to make it an Add-in.

And, whichever way you choose to go, I would get the code to pick up the
values from a "control" sheet to make it easier to update rather than going
into the code each time.

Instead of using constants:

dim sWindow As String
sWindow = Range("A2").value
etc

Regards

Trevor
 
Back
Top