DavidExcel
New Member
- Joined
- Feb 12, 2019
- Messages
- 10
I am a beginner with VBA and I mainly search the internet to do what I need.
I was able to do the code below which, by clicking a button, creates a copy of the file, opens the copy, copies values (prices) from the old file, and pastes values with increases in the new file.
All that with 2 Input Boxes to get some information.
I don't know if this is the most efficient way, but it works.
The only annoying thing was the fact that I was getting the 'Document Inspector' warning message. Therefore, I used my usual approach and searched the internet to find a solution.
I found the code below to be placed in the workbook module.
This seems to prevent the warning message to pop up, and I can save my file using the save button.
Now, here is the problem. When trying to close the copy without saving first, I get prompted to save, which is expected. The issue is that it keeps repeating the save prompt.
I guess there is some kind of loop somewhere, but I can't figure out where.
Anyone can help to fix my code or has a better solution to avoid the Document Inspector warning on this file?
Thanks in advance.
I was able to do the code below which, by clicking a button, creates a copy of the file, opens the copy, copies values (prices) from the old file, and pastes values with increases in the new file.
All that with 2 Input Boxes to get some information.
Code:
Private Sub IncreaseButton_Click()
Dim FolderPath As String
Dim FileName As String
Dim FileCopyName As Variant
FolderPath = Application.ActiveWorkbook.Path
FileName = Application.ActiveWorkbook.Name
FileCopyName = InputBox("Please enter new file name after incrase. New file will be saved in the same folder. We can also put more directions here.")
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs (FolderPath & "\" & FileCopyName & ".XLSM")
Dim StartFile As Excel.Workbook
Set StartFile = Workbooks.Open(FolderPath & "\" & FileName)
Dim EndFile As Excel.Workbook
Set EndFile = Workbooks.Open(FolderPath & "\" & FileCopyName & ".XLSM")
Dim StartPrice As Range
Set StartPrice = StartFile.Worksheets("DRF").Range("D10:D29")
Dim EndPrice As Range
Set EndPrice = EndFile.Worksheets("DRF").Range("D10:D29")
IncOrDec = InputBox("Enter the percentage of Increase or Decrease without the % sign")
EndPrice = Evaluate(StartPrice.Address & "* (1+" & IncOrDec & "/100)")
End Sub
I don't know if this is the most efficient way, but it works.
The only annoying thing was the fact that I was getting the 'Document Inspector' warning message. Therefore, I used my usual approach and searched the internet to find a solution.
I found the code below to be placed in the workbook module.
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Application
.DisplayAlerts = False
.EnableEvents = False
If SaveAsUI Then
Application.Dialogs(xlDialogSaveAs).Show
Else
ThisWorkbook.Save
End If
Cancel = True
.EnableEvents = True
.DisplayAlerts = True
End With
End Sub
This seems to prevent the warning message to pop up, and I can save my file using the save button.
Now, here is the problem. When trying to close the copy without saving first, I get prompted to save, which is expected. The issue is that it keeps repeating the save prompt.
I guess there is some kind of loop somewhere, but I can't figure out where.
Anyone can help to fix my code or has a better solution to avoid the Document Inspector warning on this file?
Thanks in advance.