Unlock Cells and Clear Contents

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,872
Office Version
2013
Platform
Windows
Post the first macro, but I can't see why it can't be done !!
 

ashani

Board Regular
Joined
Mar 14, 2020
Messages
112
Office Version
365
Platform
Windows
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.

VBA Code:
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.

Code:
Sub Newday()

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,872
Office Version
2013
Platform
Windows
Maybe this way.
The cells will only be cleared if the PDF has been completed
VBA Code:
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

Board Regular
Joined
Mar 14, 2020
Messages
112
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,872
Office Version
2013
Platform
Windows
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 !
VBA Code:
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

Board Regular
Joined
Mar 14, 2020
Messages
112
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,872
Office Version
2013
Platform
Windows
I can't test ...I don't have Excel, but try
THIS at the sstart of your create macro...before the SUB line
VBA Code:
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
VBA Code:
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

Board Regular
Joined
Mar 14, 2020
Messages
112
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,872
Office Version
2013
Platform
Windows
Are the macros in the same module ?
Is this line before the create Sub ?
VBA Code:
Public filesavename As String
 

Watch MrExcel Video

Forum statistics

Threads
1,099,005
Messages
5,465,959
Members
406,456
Latest member
jmishra91

This Week's Hot Topics

Top