Apologies in advance if this is not very well explained but I'm pretty much a total novice when it comes to VB and have only managed to get this far by searching Google for people with similar problems and modifying their solutions as well as some superb help on here.

Anyway. I have what I think is a complex workbook with lots of macros in it performing all manner of functions. The purpose of the workbook is to generate job sheets (hence it's called Job Sheet Generator) so I launch this workbook, I click a button that resets all the cells and generates a new job number, I enter the relevant job information and then click another button and it saves it as a new workbook in the relevant clients folder with the appropriate file name. This new workbook is then the actual Job Sheet and more information is added as the job progresses and other macros come into play.

Everything was working fine but then I decided to complicate matters. Because the workbook has so many macros in it I wanted to 'force' users to enable macros both in the Job Sheet Generator and in the Job Sheets it creates. So I created a new worksheet in the Job Sheet Generator with a message saying that macros had to be enabled and instructions on how to do this and then added another bit of VB that hid this message if macros are enabled or hid all the other worksheets if macros are not enabled.

If I just click on the button that saves the workbook as a new Job sheet everything is fine and If I open up the resulting job sheet and don't have macros enabled it shows me the message. Equally if I do have macros enabled, it doesn't show me the message. This is exactly how I want it.

However if, as I actually need to, I click on the button that resets all the cells in the Job Sheet Generator and generates a new job number, then enter the new information and click on the button that then saves it as a new Job sheet the job sheet doesn't appear to work in that whether I have macros enabled or not I never see the message about having to enable macros.

So presumably there is something in the VB of the reset macro that is killing the other macro.

This is the code for the reset and generate job number:

Sub NextJobNumber()
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 24").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 25").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 27").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 28").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 29").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 30").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 31").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 32").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 33").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 34").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 35").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 36").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 37").ControlFormat.Value = 1
ActiveSheet.Shapes("Drop Down 38").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
This is the code for the macro that saves it as a new job sheet:

Sub SaveTrackingSheetWithNewName()
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 these macros and others are in Modules 1 in the VBA project (I've no idea what that means or why it's in there but think from memory I just followed a guide that said to do that)

This is the code that hides or reveals the enable macros message:


    Private Sub Workbook_BeforeClose(Cancel As Boolean)
     
    'Step 1: Declare your variables
    Dim ws As Worksheet
     
    'Step 2: Unhide the Starting Sheet
    Sheets("START").Visible = xlSheetVisible
     
    'Step 3: Start looping through all worksheets
    For Each ws In ThisWorkbook.Worksheets
     
    'Step 4: Check each worksheet name
    If ws.Name <> "START" Then
     
    'Step 5: Hide the sheet
    ws.Visible = xlVeryHidden
    End If
     
    'Step 6: Loop to next worksheet
    Next ws
     
    'Step 7: Save the workbook
    ActiveWorkbook.Save
     
    End Sub



    Private Sub Workbook_Open()
     
    'Step 1: Declare your variables
    Dim ws As Worksheet
     
    'Step 2: Start looping through all worksheets
    For Each ws In ThisWorkbook.Worksheets
     
    'Step 3: Unhide All Worksheets
    ws.Visible = xlSheetVisible
     
    'Step 4: Loop to next worksheet
    Next ws
     
    'Step 5: Hide the Start Sheet
    Sheets("START").Visible = xlVeryHidden
     
    End Sub
This code is in ThisWorkbook within the VBA Project (Again I just followed instructions on another website so don't know if this is the correct place or not)

Can anyone see what is causing the job sheet that is generated to NOT have a working enable macro message if I've first clicked on the reset button in the generator?

</outofmydepth>