Unlock Cells and Clear Contents

[ashani]

Hi
I'm trying to set the macro up to unlock multiple ranges and clear contents on those ranges from Sheet1,Sheet2 & Sheet3 and also to set the message box to say "xyz" - please can someone guide me.

Many thanks,

 

[ashani]

is this at all possible ?

 

[Michael M]

Post the first macro, but I can't see why it can't be done !!

 

[ashani]

Thanks @Michael M

Here is the Create PDF Macro - So what I want is when I click on Clear Contents Macro - before clearing, it should check if the PDF macro has been run fully and if not then message box appears to say - Create PDF, if it has then continue as normal.

Sub CreatePDF_Click()

pdfName = Sheets("Sheet1").Range("C1").Value
ChDir "C:\User"
fileSaveName = Application.GetSaveAsFilename(pdfName, _
fileFilter:="PDF Files (*.pdf), *.pdf")
If fileSaveName <> False Then
Sheets(Array("Sheet1", "2", "3")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fileSaveName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

Sheets("Sheet1").Select
Exit Sub
End If
End Sub

This is the second Macro - so I want this Macro to check before clearing whether the first one has run fully.

Sub Newday()

Sheets("Sheet1").Select
With ActiveSheet
.Unprotect
     With Range("A1:O40 ")
        .Locked = False
        .ClearContents
End With
.Protect
End With

 

[Michael M]

Maybe this way.
The cells will only be cleared if the PDF has been completed

Sub CreatePDF_Click()
pdfName = Sheets("Sheet1").Range("C1").Value
ChDir "C:\User"
fileSaveName = Application.GetSaveAsFilename(pdfName, _
fileFilter:="PDF Files (*.pdf), *.pdf")
If fileSaveName <> False Then
Sheets(Array("Sheet1", "2", "3")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fileSaveName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
Call NewDay 'this will do the clearing after the PDF has bee created
Exit Sub
End If
End Sub

 

[ashani]

Thanks @Michael M

but I have 2 command buttons and want to keep it seperate button, because if pdf is created and Clear contents, and later data needs adding amending than it won’t b possible hence seperate buttons.

are there any other alternative to communicate macro 2 to macro 1.

thanks once again

 

[Michael M]

Ok, this will give you a message box if the file HAS been created....UNTESTED
Is that what you need ?
Then you can press the Clearing button !

Sub CreatePDF_Click()
pdfName = Sheets("Sheet1").Range("C1").Value
ChDir "C:\User"
FileSaveName = Application.GetSaveAsFilename(pdfName, _
fileFilter:="PDF Files (*.pdf), *.pdf")
If FileSaveName <> False Then
Sheets(Array("Sheet1", "2", "3")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FileSaveName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
If Dir(FileSaveName) <> "" Then MsgBox "File exists, Now Press the Clearing Button."
Sheets("Sheet1").Select
Exit Sub
End If
End Sub

 

[ashani]

Thank you @Michael M

can I have that message / alert box in the second macro if the file hasn’t been created Before clearing contents and abort the process?

 

[Michael M]

I can't test ...I don't have Excel, but try
THIS at the sstart of your create macro...before the SUB line

Public filesavename As String
Sub CreatePDF_Click()
pdfName = Sheets("Sheet1").Range("C1").Value
ChDir "C:\User"
FileSaveName = Application.GetSaveAsFilename(pdfName, _
fileFilter:="PDF Files (*.pdf), *.pdf")
If FileSaveName <> False Then
Sheets(Array("Sheet1", "2", "3")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FileSaveName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
If Dir(FileSaveName) <> "" Then MsgBox "File exists, Now Press the Clearing Button."
Sheets("Sheet1").Select
Exit Sub
End If
End Sub

THEN

Sub Newday()
If Dir(filesavename) = "" Then
MsgBox "File has not been created !!!"
Exit Sub
End If
Sheets("Sheet1").Select
With ActiveSheet
.Unprotect
     With Range("A1:O40 ")
        .Locked = False
        .ClearContents
End With
.Protect
End With
End Sub

 

[ashani]

Thanks @Michael M

Unfortunately it's not working - this line is highlighted "If filesavename <> False Then" with error message runtime error 13 - type mismatch.

 

[Michael M]

Are the macros in the same module ?
Is this line before the create Sub ?

Public filesavename As String