Remove Conditional Formatting via VBA in an existing macro, and protect new workbook created by macro

[louloujoy]

Hi all,

I have written 3 separate macros in a protected workbook which are running successfully, however I have 2 additional functions that I'm unable to get working. I have explained below what my macro does (in a nutshell) to give context to what I need to add in.

3 existing macros I am currently using:

Macro 1 - NextQuote(): Clears existing data and changes quote number to next sequential number (also unprotects and re-protects main workbook);
Macro 2 - SaveQuoteWithNewName(): Creates new macro enabled workbook saved with a specific name (also unprotects and re-protects main workbook);
Macro 3 - SaveFinalQuoteAsPDF(): Creates PDF copy from the new macro enabled workbook, saved in same name as the workbook it was created from.

2 additional fuctions I need to perform, but can't get working:

1. Protect the data in the new macro enabled workbook that is created by running macro #2;
2. Remove Conditional Formatting in the new macro enabled workbook that is created by running macro #2 (but don't remove from the original workbook).

Note - the new macro enabled workbook will have a different name each time it is saved as it incorporates a quote number and and Customer Name.

Here is my VBA coding that is currently performing my 3 macros. Advice on how I could incorporate my second 2 requirements into this coding would be hugely appreciated!

Sub NextQuote()    Worksheets("quoting tool").Unprotect Password:="chcp"
    Range("G4").Value = Range("G4").Value + 1
    Range("servicetype1:D36,B38:E42,C7:C8,K3:K6,C11:C11").ClearContents
    Worksheets("quoting tool").Protect Password:="chcp"
End Sub


Sub SaveQuoteWithNewName()
    Worksheets("quoting tool").Unprotect Password:="chcp"
    Dim NewFN As Variant
    ' Copy Quote to a new workbook
    Sheets(Array("quoting tool", "nurses", "client contribution", "services", "level 1 and 2", "level 3 and 4", "instructions for use", "pricing list")).Copy
    Worksheets("quoting tool").Range("I:Z").EntireColumn.Hidden = True
    NewFN = "M:\Always Used\CHCP Quoting Tool\CHCP Customer Quotes\CHCP Quote " & Range("G4").Value & " - " & Range("C3").Value & ".xlsm"
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    Application.DisplayAlerts = True
    ActiveWorkbook.Close
    Worksheets("quoting tool").Protect Password:="chcp"
    ThisWorkbook.Save
    ThisWorkbook.Close
End Sub


Sub SaveFinalQuoteAsPDF()
    Dim NewFN As Variant
    NewFN = "M:\Always Used\CHCP Quoting Tool\CHCP Customer Quotes\CHCP Quote " & Range("G4").Value & " - " & Range("C3").Value & ".pdf"
    ' Export the invoice sheet as PDF
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=NewFN, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
    ThisWorkbook.Save
    ThisWorkbook.Close
    ActiveWorkbook.Save
    ActiveWorkbook.Close

End Sub

Thanks in advance!

Lou

 

[NotoriousPopol]

Hi!

To get started, in your 2nd macro, you should very clearly manipulate the workbooks.

Dim WbMain As Workbook, WbNew As Workbook

Set WbMain = ThisWorkbook

.....

Set WbNew = ActiveWorkbook

Now you can do whatever you want with WbNew before closing it such as

WbNew.Sheets(1).Range("A:Z").FormatConditions.Delete
WbNew.Sheets(1).Protect Password:="chcp"

 

[louloujoy]

Thank you so much!

I was able to get the "protect" function working on my new workbook, although I'm seeing errors with the "delete conditional formatting" function. I suspect that this is because I'm trying to insert the function into the third macro, as it works perfectly when I insert in into the 2nd macro. However, including that line in the 2nd macro is removing the conditional formatting too early; I need the conditional formatting to remain until the document is 100% completed and ready to be saved as a PDF, only then should the conditional formatting be deleted. I hope this makes sense!

Any tips on where/how I should write this in? Updated coding below for my 3rd macro:

Sub SaveFinalQuoteAsPDF()    Dim WbMain As Workbook, WbNew As Workbook
    Set WbMain = ThisWorkbook
    Set WbNew = ActiveWorkbook
    Dim NewFN As Variant
    NewFN = "M:\Always Used\CHCP Quoting Tool\CHCP Customer Quotes\CHCP Quote " & Range("G4").Value & " - " & Range("C3").Value & ".pdf"
    ' Export the invoice sheet as PDF
    WbNew.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=NewFN, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
    WbMain.Save
    WbMain.Close
    WbNew.Save
    WbNew.Close
End Sub

Thanks again! I appreciate the help!

 

[NotoriousPopol]

Hi,

I understand that the 1st and 2nd macros are launched from the original file right? What about the 3rd one? If you correctly launch the 3rd macro from the new workbook, and write something like

ThisWorkbook.Sheets(1).Range("A:Z").FormatConditions.Delete

it should work.

What errors do you see?

 

[louloujoy]

Hi again!

Correct, I'm attempting to launch the third macro from the new workbook so that all formatting in the original workbook remains unchanged for future use.

I've tried your suggestion in different positions throughout the coding, and with amendments such as changing the "WbNew" name instead of "ThisWorkbook" but each time get the same error message that just says "400". I've basically tried every variation on this piece of code that I can find/think of for the past few days, always with the same "400" error message.

Here was my last attempt:

Sub SaveFinalQuoteAsPDF()    Dim WbMain As Workbook, WbNew As Workbook
    Set WbMain = ThisWorkbook
    Set WbNew = ActiveWorkbook
    WbNew.Sheets(1).Range("A:Z").FormatConditions.Delete
    Dim NewFN As Variant
    NewFN = "M:\Always Used\CHCP Quoting Tool\CHCP Customer Quotes\CHCP Quote " & Range("G4").Value & " - " & Range("C3").Value & ".pdf"
    ' Export the invoice sheet as PDF
    WbNew.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=NewFN, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
    WbMain.Save
    WbMain.Close
    WbNew.Save
    WbNew.Close

End Sub

Thanks again

 

[NotoriousPopol]

Of course, this is because your sheet is protected!!

Try to : unprotect, delete de conditions formats, protect again. And do it after you saved the file as PDF.