Don'r Run Macro if Conditions are met

[peakoverload]

I'm a total newbie to VBA and struggling to solve a problem I have. I'll try and explain the problem, apologies in advance if I'm not always using the right terminology.

I have a workbook which I've called Job Sheet Generator.xlsm which is used to create what we call Tracking Sheets.

When we run the Job Sheet Generator we click on a button that generates a job number based on the last job number used in another workbook. When it generates the job number it also resets all existing cells, drop down lists, checkboxes etc.

We then fill in information about the job into the Job Sheet Generator and then click on another button. This button then saves it as a new workbook, saving it to a folder based on the content of a cell in a predefined path and gives it a file name which again is based on the content of certain cells.

What also happens is that two other workbooks are also automatically updated with certain content from the relevant cells. These being current year.xlsx and commercial_work_log.xlsx.

Now I achieved all of this simply by searching online for existing VBA examples and then working out how to simply add the cells I need and point it at the various workbooks. I understand some of the actual VBA but other bits I don't and just know that it works even if I don't know how or why.

This is some of the macros I've 'written'

' This adds relevant information to the Current Year.xlsx workbook adding details of the job and it's job number so that the job number isn't then used again.
Sub PostToCommercialTracking()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("Tracking Sheet")
Workbooks.Open "M:\Commercial Clients\Current Year.xlsx"
Set WS2 = Worksheets("Current")

' Figure out which row is the next row
NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1

' Write the important values to the Commercial Tracker
WS2.Cells(NextRow, 1).Resize(1, 4).Value = Array(WS1.Range("D2"), WS1.Range("B1"), WS1.Range("D1"), WS1.Range("H1"))
Workbooks("Current Year.xlsx").Close SaveChanges:=True
End Sub

Sub NextJobNumber()
' This looks at the last row in the current year.xlsx workbook, gets it's job number and adds one to it which it then enters into the job number cell of the Job Sheet Generator. It also resets all other cells so that new data can be entered.
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

Dim WBPath As String
Dim CurrentWB As Workbook
Dim WB As Workbook
Dim X As Integer
Dim LR As Long
Dim MaxNum As String

Set CurrentWB = ActiveWorkbook
WBPath = "M:\Commercial Clients\Current Year.xlsx"

Workbooks.Open (WBPath)
Set WB = ActiveWorkbook

LR = WB.Sheets("Current").Cells(Rows.Count, 1).End(xlUp).Row

MaxNum = 0

For X = 1 To LR
    If Left(WB.Sheets("Current").Cells(X, 1), 1) = 5 Then
    If WB.Sheets("Current").Cells(X, 1) > MaxNum Then MaxNum = WB.Sheets("Current").Cells(X, 1)
    End If

Next X

ActiveWorkbook.Close

CurrentWB.Sheets("Tracking Sheet").Range("D2").Value = MaxNum + 1

With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With

Range("B1:B9,D1,D3:D4,E8,F1:F3,H2:H5,C18:C31,D18:D31,F18:F31,G18:G33,E38:E47,F38:F47,H38:H47,J38:J47,K38:K47,E52:E58,H52:H56,H59").ClearContents

ActiveSheet.CheckBoxes.Value = False
ActiveSheet.Shapes("Drop Down 5").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 6").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 7").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 8").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 9").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 10").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 11").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 12").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 13").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 14").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 15").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 16").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 17").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 18").ControlFormat.Value = 1
ActiveSheet.Shapes("Special_Requirements").TextFrame.Characters.Text = ""
ActiveSheet.Shapes("Masters").TextFrame.Characters.Text = ""
ActiveSheet.Shapes("Announcements").TextFrame.Characters.Text = ""
ActiveSheet.Shapes("Producer_Notes").TextFrame.Characters.Text = ""
With Range("H1")
.Value = Date
End With
End Sub

Sub SaveTrackingSheetWithNewName()
' This saves it as a new workbook and creates a folder in the appropriate structure and names the workbook after job number and title of job.
Dim fileFolder  As String
Dim NewFN       As Variant
Dim sJobNum     As String
Dim sClient     As String
Dim sTitle      As String
Dim sTitleFolder    As String
Dim sPath       As String


fileFolder = "G:\Commercial work\"

ActiveWorkbook.Save
PostToCommercialTracking
PostToCommercialWorkLog
' Copy Tracking Sheet to a new workbook
sJobNum = Range("D2").Value & " "
sClient = Range("B1").Value & ""
sTitle = Range("D1").Value & ".xlsm"
sTitleFolder = Range("D1").Value & ""
sPath = fileFolder & sClient & Application.PathSeparator & sJobNum & sTitleFolder

If Dir(fileFolder & sClient, vbDirectory) = "" Then
    MkDir fileFolder & sClient
Else
    MkDir fileFolder & sClient & Application.PathSeparator & sJobNum & sTitleFolder
    
End If


ActiveSheet.Unprotect "sadie"
ActiveWorkbook.SaveAs Filename:=sPath & Application.PathSeparator & sJobNum & sTitle, FileFormat:=52
ActiveSheet.Shapes("Rounded Rectangle 1").Delete
ActiveSheet.Shapes("Rounded Rectangle 2").Delete
ActiveSheet.Protect "sadie"
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

All of this code (and more) I've written in the Module 1 object of the VBA project simply because that's where I was told to write the first macro I wrote and just kept adding more to it.

The problem I'm having is that sometimes when we use the Job Sheet Generator we perhaps don't have all the information we require from the client and need to go back and add it later. Currently when we need to do this, we have to update the Tracking Sheet that the Job Sheet Generator created. That's fine but it doesn't then add that information to the Commercial_work_log.xlsx workbook which it would have done if we knew it when we used the Job Sheet Generator.

So, what I've been trying to do is to have a macro that whenever the Tracking Sheet is saved it automatically updates the Commercial_work_log.xlsx workbook.

In order to try and achieve this I added this code to the ThisWorkbook object

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("Tracking Sheet")
Workbooks.Open "G:\Commercial work\COMMERCIAL_WORK_LOG.xlsx"
Set WS2 = Worksheets("Log")
' Figure out which row has the job nr
findrow = Application.Match(WS1.Range("D2"), Range("A:A"), 0)
If Not IsError(findrow) Then
WS2.Cells(findrow, 2) = WS1.Range("B1")
WS2.Cells(findrow, 3) = WS1.Range("D1")
WS2.Cells(findrow, 4) = WS1.Range("B2")
WS2.Cells(findrow, 5) = WS1.Range("F3")
WS2.Cells(findrow, 6) = WS1.Range("F1")
WS2.Cells(findrow, 7) = WS1.Range("F2")
WS2.Cells(findrow, 8) = WS1.Range("D16")
WS2.Cells(findrow, 9) = WS1.Range("E16")
WS2.Cells(findrow, 10) = WS1.Range("H2")
    WS2.Cells(findrow, 11) = WS1.Range("D3")
WS2.Cells(findrow, 12) = WS1.Range("H58")
WS2.Cells(findrow, 13) = WS1.Range("H58" * 0.3)
WS2.Cells(findrow, 15) = WS1.Range("H59")

Else
    MsgBox "Job Nr not found"
End If
Workbooks("COMMERCIAL_WORK_LOG.xlsx").Close SaveChanges:=True
End Sub

The problem here is that when the Job Sheet Generator saves as a new Workbook, this macro also wants to run.

What I want is either for this macro to only be added to the Tracking Sheet when it's created by the Job Sheet Generator or that the macro can be ammended so that if it's being run from the Job Sheet Generator it skips that macro and so only runs on the Tracking Sheet itself.

I've tried adding a If ThisWorkBook.Name = "Job Sheet Generator.xlsm" Then
Exit Sub but this then throws up an error with the line Set WS2 = Worksheets("Log") saying it's not present.

I'm now way out of my depth. Is what I'm wanting possible, is there a way I can do it?

 

[cerfani]

You can use a global variable to flag if a macro was previously run...

Public macroACalled As Boolean

Private Sub MacroA()
    macroACalled = True
End Sub

Private Sub MacroB()
    If Not macroACalled Then Exit Sub

End Sub

 

[dmt32]

Hi,
In your Job Sheet Generator code try adding following shown in BOLD

Sub JobSheetGenerator()
Application.EnableEvents = False

‘rest of your code
 
Application.EnableEvents = True
 End Sub

And see if this resolves your problem

Hope Helpful

Dave