Fill a cell once the file is printed

[Lux Aeterna]

There's an excel form that we fill out and print several times every day. What I need is to have a cell filled every time the sheet is printed.

By the way, we only print as PDF using a macro and an assigned button.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="C:\Users\pc50\Desktop\Νέα Τεστ Παπ\" & Range("AH1").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

So, if for example I put ID number 1 (ID number goes to $U$2 - Sheet Test results), I'd like cell B5 (Sheet Demographics) to get filled with the info from $AA$9 (Sheet Test results) as soon as the PDF is printed.
If I put ID number 2, I'd like cell B6 to get the info from $AA$9 and so on. List goes down to cell B10033 (Sheet Demographics).

It would be a great help if the PDF is allowed to be printed only when $AA$9 is filled.

Thank you in advance!

 

[Lux Aeterna]

Place the macro in a regular module, not the worksheet code module, and run it manually when you are ready to put the name from AA9 into the appropriate row in column B of Demographics and print to PDF.

It works like a charm!

No need to keep my old print as PDF macro, right?

Is it possible to add a pop up message ('You need to fill supervisor's name') when AA9 is empty? If it's too hard, don't worry about it.

Moreover, if ID (U2) is empty it gives me a run-time error. I'm not sure they'll know how to deal with it, i.e. press end Is it possible to ignore it and just exit sub without printing?

 

[mumps]

Do you want to force the user to enter an ID in U2 and a supervisor's name in AA9? You don't need your old macro.

 

[Lux Aeterna]

Do you want to force the user to enter an ID in U2 and a supervisor's name in AA9? You don't need your old macro.

Not sure if force has a special meaning in excel. I just need to avoid that run-time error window if they forget ID number and them to be reminded if they forget AA9.

 

[mumps]

By "force" I mean that the user will not be able to proceed to print to PDF unless they enter an ID and supervisor name.

 

[mumps]

Give this a try to see what I mean.

Sub PrintPDF()
    Application.ScreenUpdating = False
    Dim ID As Range, sup As String, sID As String
    If Sheets("Results").Range("AA9") = "" Then
        Do
            sup = InputBox("Please enter a supervisor name.")
            If sup <> "" Then
                Sheets("Results").Range("AA9") = sup
                Exit Do
            ElseIf sup = "" Then
                MsgBox "You must enter a supervisor name."
            End If
        Loop
    End If
    If Sheets("Results").Range("U2") = "" Then
        Do
            sID = InputBox("Please enter an ID.")
            If sID <> "" Then
                Sheets("Results").Range("U2") = sID
                Exit Do
            ElseIf sID = "" Then
                MsgBox "You must enter an ID."
            End If
        Loop
    End If
    Set ID = Sheets("Demographics").Range("A:A").Find(Sheets("Results").Range("U2").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not ID Is Nothing Then
        ID.Offset(, 1) = Sheets("Results").Range("AA9")
    End If
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="C:\Users\pc50\Desktop\??a ?est ?ap\" & Range("AH1").Value _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    Application.ScreenUpdating = True
End Sub

 

[Lux Aeterna]

Ok, now I get it. No, not force them. Just a pop up window with a reminder that they will need to close and then enter ID and their name in the appropriate cells in order to have the file printed.

 

[mumps]

Try:

Sub PrintPDF()
    Application.ScreenUpdating = False
    Dim ID As Range, sup As String, sID As String
    If Sheets("Results").Range("AA9") = "" Then
        MsgBox ("Please enter a supervisor name in cell AA9.")
        Sheets("Results").Range("AA9").Select
        Exit Sub
    End If
    If Sheets("Results").Range("U2") = "" Then
        MsgBox ("Please enter an ID in cell U2.")
        Sheets("Results").Range("U2").Select
        Exit Sub
    End If
    Set ID = Sheets("Demographics").Range("A:A").Find(Sheets("Results").Range("U2").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not ID Is Nothing Then
        ID.Offset(, 1) = Sheets("Results").Range("AA9")
    End If
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="C:\Users\pc50\Desktop\??a ?est ?ap\" & Range("AH1").Value _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    Application.ScreenUpdating = True
End Sub

 

[Lux Aeterna]

That's exactly what I was asking for! Thank you so much!

I've got a similar task on the same sheet. I've already opened a thread for that. May I tag you there, please?

 

[mumps]

You are very welcome.

May I tag you there, please?

No problem.

 

[Lux Aeterna]

Hey @mumps, I was wondering if you could add an extra feature to your macro.

When copying cell data from one sheet to another that is already filled in, pop up a warning message. Ideally, the pop up message would include the data of that cell. For example "This sample has already be tested by "cell data". Are you sure you want to overwrite it?" And a "Yes - No" option. Yes would just overwrite the cell data in the Demographics sheet and print the Results sheet. No would select cell U2 in Results sheet and delete contents.

Hope it's clear enough!

Thank you in advance!