Results 1 to 6 of 6

Macro Code Will Not Stop Saving In My Module

Threaded View

  1. #1
    Forum Contributor
    Join Date
    01-09-2016
    Location
    Surrey B.C.
    MS-Off Ver
    2013
    Posts
    155

    Thumbs up Macro Code Will Not Stop Saving In My Module

    So I am Very New with Macros. Any Help would be appreciated. I have a code That I am running in my Module. It is designed to Stop Everything (ie Invoice Register, Todays Date, Saving) Unless All Specific Cells Are filled. I have managed to splice together a code that opens a message window and higlights the blank cells. It still However continues with the rest of my Macro Including Saving. This My Complete Module Code:

    Sub fullmacsro()
        FillCells
        TodaysDate
        PostToRegister
        SavePdf
        SaveXLMFile
        NextInvoice
       
    End Sub
    
    Sub FillCells()
    
        Dim Start As Boolean
        Dim Rng1 As Range, Rng3 As Range, Rng4 As Range
         
        Dim Prompt As String, RngStr As String
        Dim Cell As Range
         'set your ranges here
         'Rng1 is on sheet "Group Profile" and cells B5 through B14
         'Cell F1, A range of F5 through F7 etc.  you can change these to
         'suit your needs.
        Set Rng1 = Sheets("1 Invoice Notes").Range("B4,B8,B9,B10,B14,B15,B17,B29,B30,B31,B32,D8,D9,F30,F31,J37")
             'message is returned if there are blank cells
        Prompt = "Please check your data ensuring all required " & _
        "cells are complete." & vbCrLf & "you will not be able " & _
        "to close or save the workbook until the form has been filled " & _
        "out completely. " & vbCrLf & vbCrLf & _
        "The following cells are incomplete and have been highlighted yellow:" _
        & vbCrLf & vbCrLf
        Start = True
         'highlights the blank cells
        For Each Cell In Rng1
            If Cell.Value = vbNullString Then
                Cell.Interior.ColorIndex = 6 '** color yellow
                If Start Then RngStr = RngStr & Cell.Parent.Name & vbCrLf
                Start = False
                RngStr = RngStr & Cell.Address(False, False) & ", "
            Else
                Cell.Interior.ColorIndex = 0 '** no color
            End If
        Next
        If RngStr <> "" Then RngStr = Left$(RngStr, Len(RngStr) - 2)
        Start = True
        If RngStr <> "" Then RngStr = RngStr & vbCrLf & vbCrLf
       
        If RngStr <> "" Then RngStr = Left$(RngStr, Len(RngStr) - 2)
        Start = True
        If RngStr <> "" Then RngStr = RngStr & vbCrLf & vbCrLf
       
          If RngStr <> "" Then RngStr = Left$(RngStr, Len(RngStr) - 2)
        If RngStr <> "" Then
            MsgBox Prompt & RngStr, vbCritical, "Incomplete Data"
            Cancel = True
        Else
             'saves the changes before closing
            ThisWorkbook.Save
            Cancel = False
        End If
         
        Set Rng1 = Nothing
        Set Rng3 = Nothing
        Set Rng4 = Nothing
         
    End Sub
    
    Sub TodaysDate()
    Range("B6").Value = Format(Now(), "MM/DD/YYYY")
    End Sub
    
    Sub PostToRegister()
        Dim WS1 As Worksheet
        Dim WS4 As Worksheet
        Set WS1 = Worksheets("1 Invoice Notes")
        Set WS4 = Worksheets("4 Register")
        ' Figure out which Row Is next
        NextRow = WS4.Cells(Rows.Count, 1).End(xlUp).Row + 1
        ' Write the important Values To Register Last Number is How many items
        WS4.Cells(NextRow, 1).Resize(1, 7).Resize(1, 7).Value = Array(WS1.Range("B6"), WS1.Range("D6"), WS1.Range("B8"), WS1.Range("J37"), WS1.Range("J36"), WS1.Range("i33"), WS1.Range("J38"))
        
    End Sub
    
    Sub SavePdf()
    
    'This macro opens the SaveAs option with the defult file path "you have to set this file path below" coverts the whole sheet into .pdf file format
    'And opens the .pdf to view <-- you can disable the view after covert option with lower code: OpenAfterPublish:=False
    
        pdfName = Sheet2.Range("D6") & Sheet2.Range("B8") & Sheet2.Range("D8")
        ChDir "C:\Users\Terrance\Dropbox\Marks Custom Drywall And Finishing\3 INVOICING DEPOSITS\4 outstanding invoice"
        fileSaveName = Application.GetSaveAsFilename(pdfName, _
        fileFilter:="PDF Files (*.pdf), *.pdf")
        If fileSaveName <> False Then
        Sheet2.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            fileSaveName _
            , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True
        End If
        MsgBox "File Saved to" & " " & fileSaveName
        
        End Sub
        
        Sub SaveXLMFile()
        
        
        
        Dim NewFN As Variant
        
        ActiveSheet.Copy
        NewFN = "C:\Users\Terrance\Dropbox\Marks Custom Drywall And Finishing\3 INVOICING DEPOSITS\6 Invoice Copy\" & Range("D6").Value & Range("B8").Value & Range("D8").Value & ".xlsx"
        ActiveWorkbook.SaveAs NewFN, xlOpenXMLWorkbook
        ActiveWorkbook.Close
        
        
        
           End Sub
           
           Sub NextInvoice()
        
               
         
        Range("D6").Value = Range("D6").Value + 1
        Range("B8:B15").ClearContents
        Range("D8:J9").ClearContents
        Range("B17:B20").ClearContents
        Range("D12:E29").ClearContents
        Range("F12:G12").ClearContents
        Range("F15:G15").ClearContents
        Range("F18:G18").ClearContents
        Range("F21:G21").ClearContents
        Range("F24:G24").ClearContents
        Range("F27:G27").ClearContents
        Range("B4:H4").ClearContents
        Range("B29:B35").ClearContents
        Range("J37").ClearContents
        Range("F30").ClearContents
        Range("F31").ClearContents
        
    End Sub
    Thank you in advance. Any Help would be appreciated
    Last edited by Leith Ross; 01-12-2016 at 01:16 PM. Reason: Moved Code Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help in macro code to stop code running for certain time
    By ExcelFailure in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-05-2015, 02:35 AM
  2. [SOLVED] Code locks cells when inserted in sheet module but returns error in standard module
    By yoda66 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-07-2014, 07:39 AM
  3. [SOLVED] Convert code in a class module to a macro
    By Consty1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2013, 09:44 AM
  4. VBA Code to stop users Saving
    By Dbyrne75 in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 06-10-2010, 11:20 AM
  5. how to access Sheet module, normal module, Worbook module to type code
    By alibaba in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2009, 07:51 AM
  6. Replies: 1
    Last Post: 04-10-2005, 07:07 PM
  7. [SOLVED] Stop macro from saving changes
    By manoj.tare@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-01-2005, 09:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1