Sabotage
Board Regular
- Joined
- Sep 19, 2013
- Messages
- 58
Hi All,
I have a VBA module where I need some help with regards to adding new lines into another VBA module. First of, is this possible at all??? Below is my code to add a new store into the existing store patch we currently have. The user using the spreadsheet does not know anything about VBA, therefore will not be able to edit all relevant modules, when it comes to new entities.
The first code shown below is allowing the user to add a new store, the second is saving the spreadsheet weekly. When the new store is added, I need to figure out how to add new lines into the module saving the workbook every week from a master.
So when the "Add_NewStore" function is being executed I need it to open and edit the "SaveAsNew" function to insert new lines of code where it needs to be. It would serve the purpose of saving the new file every week into the designated folder. I would like to add the new piece of code where it states below as the line and column will be constant no matter how many new stores will be added in the future.
I hope this would be a good enough explonation of what I'm looking for. Your help is much appreciated and thank you in advance.
I have a VBA module where I need some help with regards to adding new lines into another VBA module. First of, is this possible at all??? Below is my code to add a new store into the existing store patch we currently have. The user using the spreadsheet does not know anything about VBA, therefore will not be able to edit all relevant modules, when it comes to new entities.
The first code shown below is allowing the user to add a new store, the second is saving the spreadsheet weekly. When the new store is added, I need to figure out how to add new lines into the module saving the workbook every week from a master.
So when the "Add_NewStore" function is being executed I need it to open and edit the "SaveAsNew" function to insert new lines of code where it needs to be. It would serve the purpose of saving the new file every week into the designated folder. I would like to add the new piece of code where it states below as the line and column will be constant no matter how many new stores will be added in the future.
I hope this would be a good enough explonation of what I'm looking for. Your help is much appreciated and thank you in advance.
Rich (BB code):
Sub Add_NewStore()
Application.ScreenUpdating = False
'Declare Variables
Dim WS As Worksheet
Set WS = Sheets("Stock_Control")
Dim UG As Worksheet
Set UG = Sheets("User_Guide")
'Ask User to Input New Store Details
NewStoreCountry:
Country = InputBox(" Please Enter Country of the New Store!", "Country of the New Store!")
If StrPtr(Country) = 0 Then
MsgBox " Cancelled by User! Exiting Procedure!", , "Warning!"
Application.ScreenUpdating = True
Exit Sub
ElseIf Country = "" Then
MsgBox "Nothing Entered! Please Try Again or click Cancel to Exit!", vbOKOnly, "Warning!"
GoTo NewStoreCountry
End If
NewStoreName:
StoreName = InputBox("Please Enter the Name of the New Store!", "Name of the New Store!")
If StrPtr(StoreName) = 0 Then
MsgBox "Cancelled by User! Exiting Procedure!", , "Warning!"
Application.ScreenUpdating = True
Exit Sub
ElseIf StoreName = "" Then
MsgBox "Nothing Entered! Please Try Again or click Cancel to Exit!", vbOKOnly, "Warning!"
GoTo NewStoreName
End If
'Insert New Line into Store Patch Table (Validation for Cell)
WS.Select
Range("Z15:AA15").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'Use up the Data Entered Above
Range("Z15").Select
Selection = Country
ActiveCell.Offset(0, 1).Activate
Selection = StoreName
'Sort Store Patch Table by Store Names Ascending
Range("Z14:AA50").Select
ActiveWorkbook.Worksheets("Stock_Control").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Stock_Control").Sort.SortFields.Add Key:=Range( _
"AA14:AA50"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Stock_Control").Sort
.SetRange Range("Z14:AA50")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Create New Folder for New Store
MkDir Path:="G:\Stores\" & StoreName
' This is where I need help to add code opening and editing the module below!
' say: open module "SaveAsNew"
' go to Line x / Column x
' add required code lines
' save workbook
' back to this module and finish off!
Application.ScreenUpdating = True
End Sub
Rich (BB code):
Sub SaveAsNew()
Application.ScreenUpdating = False
'Declare Variables
Dim newFile As String, fName As String, DeliveryStore As String
Dim WS As Worksheet
Set WS = ActiveSheet
'Check if Required Fields are Reporting Empty
DeliveryStore = Cells(10, 1).Value
StartDate = Cells(3, 3).Value
If DeliveryStore = "" Or StartDate = "" Then
MsgBox "Something is Missing! Please Check Your Date Input, Store Name etc.!", vbOKOnly
Exit Sub
End If
'Ask the User to Confirm Action!
If MsgBox(" Are You Sure that You Wish to Save as New File?" & Chr(10) & Chr(10) _
& (" ") & Range("D10").Value, vbYesNo, _
"Please Confirm!") = vbYes Then
'Save File to "G:\" Drive Folder
Range("A1").Select
newFile = ActiveCell.Value
'Select Case for Delivery Store
If DeliveryStore = "Wonderland" Then
fName = "G:\Stores\Wonderland" & "\" + newFile + ".xlsm"
ActiveWorkbook.SaveAs Filename:= _
fName, FileFormat:=52
'New store's saving method would go here if possible!!! (first ElseIf)
ElseIf DeliveryStore = "Sunrise" Then
fName = "G:\Stores\Sunrise" & "\" + newFile + ".xlsm"
ActiveWorkbook.SaveAs Filename:= _
fName, FileFormat:=52
ElseIf DeliveryStore = "Piccadilly" Then
fName = "G:\Stores\Piccadilly" & "\" + newFile + ".xlsm"
ActiveWorkbook.SaveAs Filename:= _
fName, FileFormat:=52
End If
Else
Exit Sub
End If
ActiveSheet.Shapes("Generate").Select
Selection.Delete
Range("A1").Select
Selection.ClearContents
Application.ScreenUpdating = True
MsgBox "This is Your New File Now!" & vbNewLine & vbNewLine & "Please Sense Check Your Fresh File!"
ActiveWorkbook.Save
End Sub
