Certificate Printing Macro

[Laurenwsjassoc]

Hello,

I need to figure out how to write a macro that will take info from a worksheet (a row that goes across many columns) and place that info in another worksheet that is a certificate template. This macro also needs to recognize when it hits the last filled in column in a row it should print the certificate, move to the next row replace the info in the cert with the date in the next row and then print and keep doing that until it gets to an empty row.

Is this sort of macro possible? If so how do I go about creating it. I know it seems confusing I am happy to answer any questions to clarify.

Thank you!

 

[MrKowz]

MrKowz, I'm sure you have this more than adequately covered but how about this for a possible solution...

The user creates his certificate template using the INDIRECT function to refer to the cells from the data sheet based on a row number somewhere in the template sheet - for example, he puts a row number in cell Z1 of the template and in cell A8 of the template he'd have =INDIRECT("DataSheet!A"&Z1), in cell B22 of the template he'd have =INDIRECT("DataSheet!W"&Z1), etc.

When he puts a 1 in Z1 (manually), the template will populate itself from row 1 of the data sheet; when he puts a 2 in Z1, the template populates itself from row 2 of the data table, etc.

Then the macro just needs to cycle through the data table, placing each row number in turn into Z1 and printing the template.

That way you don't need to shift any data using VBA - it's all done by worksheet formulae. The user doesn't have to tell you where the data comes from or where it has to go (because he does all of that) and he can play around with the template without having to dip into VBA.

Did I explain myself clearly?

What do you reckon?

That's a good idea, especially if the user needs to go back and reprint a specific client; it makes it very user friendly.

I'll build two macros:

1. Initializer - gets the file properly set up with all of the INDIRECT functions and sets up the cell that the INDIRECTs will link to.
2. Print All - Prints out all records

 

[MrKowz]

Laurenwsjassoc,


Try out these two macros. Be sure to run them in the following order:

• Initializer
• PrintAllCertificates

And be sure that Y1 and Z1 are empty in your "Data" worksheet prior to running the code.


To install/run these macros:

1. Press Alt+F11 to load the VBA editor
2. Insert>Module
3. Copy/Paste the code provided into the module
4. Go back to your worksheet, and press Alt+F8 to load the Run Macro window
5. Choose the macro you wish to run from the list, and click Run

Public Sub Initializer()
Dim ws1 As Worksheet, _
    ws2 As Worksheet
 
Set ws1 = Sheets("Data")
Set ws2 = Sheets("Certificate")
ws1.Range("Y1").Value = "Row # to Print:"
ws1.Range("Z1").Value = 12
ws2.Range("A8").Formula = "=INDIRECT(""Data!A"" & Data!Z1)"
ws2.Range("B18").Formula = "=INDIRECT(""Data!B"" & Data!Z1)"
ws2.Range("B22").Formula = "=INDIRECT(""Data!W"" & Data!Z1)"
ws2.Range("C18").Formula = "=INDIRECT(""Data!C"" & Data!Z1)"
ws2.Range("C22").Formula = "=INDIRECT(""Data!E"" & Data!Z1)"
ws2.Range("D18").Formula = "=INDIRECT(""Data!H"" & Data!Z1)"
ws2.Range("D22").Formula = "=INDIRECT(""Data!X"" & Data!Z1)"
ws2.Range("E18").Formula = "=INDIRECT(""Data!K"" & Data!Z1)"
ws2.Range("E22").Formula = "=INDIRECT(""Data!Z"" & Data!Z1)"
ws2.Range("F18").Formula = "=INDIRECT(""Data!I"" & Data!Z1)"
ws2.Range("F22").Formula = "=INDIRECT(""Data!Y"" & Data!Z1)"
ws2.Range("G22").Formula = "=INDIRECT(""Data!F"" & Data!Z1)"
End Sub
 
 
Public Sub PrintAllCertificates()
Dim i       As Long, _
    LR      As Long, _
    wsData  As Worksheet, _
    wsCert  As Worksheet, _
    trial   As VbMsgBoxResult
 
 
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With
Set wsData = Sheets("Data")
Set wsCert = Sheets("Certificate")
LR = wsData.Range("A" & Rows.Count).End(xlUp).Row
trial = MsgBox("Would you like to run a trial to" & vbLf & "ensure certificate prints properly?", vbYesNo)
If trial = vbNo Then
    For i = 12 To LR
        Application.StatusBar = "Currently printing row " & i & " of " & LR
        wsData.Range("Z1").Value = i
        Application.Calculate
        wsCert.PrintOut
    Next i
Else
    For i = 12 To 15
        Application.StatusBar = "Currently printing row " & i & " of 15 (TRIAL RUN)"
        wsData.Range("Z1").Value = i
        Application.Calculate
        wsCert.PrintOut
    Next i
End If
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .StatusBar = False
End With
End Sub

Note that the cells in your certificate will not contain a formula. You can change what record is populated in the certificate by changing cell Z1 in your Data worksheet. Also, I would recommend using the "trial run" of the PrintAllCertificates to ensure it prints to your specifications before sending the entire dataset through.

 

[Laurenwsjassoc]

WOW! Thank you. I am going to play around with this and see if I can get it to work. I really appreciate this. If I run into a problem I will post to this thread.

 

[Laurenwsjassoc]

Ok so I tried to run the macro but I think I ran into a few problems. I pasted the module into the VB. I left the VB open and I went back to the worksheet. I first ran the initializer then I ran print all certs. An error message popped up on the VB that said:
Compile Error:
Automatic type not supported in VB.

So question one: Do I close the VB and if so How do I make sure the macro saved

Ques 2: Isn't running the two macros separtely going to mess up the implementing of the data from one employee to the next into the cert?

I think I may be a little confused, however this is the farthest I have been able to get with this project so thank you.

 

[MrKowz]

Ok so I tried to run the macro but I think I ran into a few problems. I pasted the module into the VB. I left the VB open and I went back to the worksheet. I first ran the initializer then I ran print all certs. An error message popped up on the VB that said:
Compile Error:
Automatic type not supported in VB.

So question one: Do I close the VB and if so How do I make sure the macro saved

Ques 2: Isn't running the two macros separtely going to mess up the implementing of the data from one employee to the next into the cert?

I think I may be a little confused, however this is the farthest I have been able to get with this project so thank you.

To answer question 1: the VB is a part of the workbook, it will be saved when the workbook is saved. If you exit the editor, the code will still be there.

Question 2: It will not. The reason why is because the cells are now linked via a formula, and all you need to do is change Z1 on the Data worksheet, and it will populate the certificate sheet with the data you define.

However, I've never run across the "Automatic type not supported in VB" error. Let me look into that and possibly build some dummy data to actually test the macro.

 

[MrKowz]

I just created some dummy data and tested both macros - they ran perfectly fine.

What version of Excel are you using?

Is this a workbook that you would be able to send me so I can look deeper into the problem? If so, let me know and I will PM you my e-mail address.

 

[Laurenwsjassoc]

This info may help you: The print all certs was highlighted yellow and when I went to close the VB it said closing will stop the debugging process. I closed it anyway so I don't know if I should have but maybe those pieces will help you

 

[MrKowz]

This info may help you: The print all certs was highlighted yellow and when I went to close the VB it said closing will stop the debugging process. I closed it anyway so I don't know if I should have but maybe those pieces will help you

When you get a compile error, the procedure name gets highlighted, and the error box you described is normal. However, what is confusing me is the error message you received. I tested both macros on my end with some dummy-data and they ran smoothly.

 

[Laurenwsjassoc]

Its excel 2003. When I closed the workbook and then re-opened it this message popped up. Do you suggest changing security settings?

You may encounter this error for the following reasons:

1. 
   [*]Macro security is set to:
   
   • Very High and the application encounters a signed macro, but the macro was automatically disabled. Use the following procedure to enable the macro:
     1. 1. Select the Tools menu option and then select Macro and Security. In the resulting Security dialog, set the security level to High by clicking the High radio button.
        2. Close the file and any other instances of the application currently running on the computer (close all applications that also use the application you are currently running).
        3. Open the file again and examine the certificate of trust details and set the Always trust macros from this publisher box if you trust the certificate issued by the publisher.
        4. Click the Enable button to allow the macro to run.

• • High and the application encounters a signed macro, but you selected Disable when prompted by the macro warning dialog when opening the file. Use the following procedure to enable the macro:
    • • Close the file and any other instances of the application currently running on the computer (close all applications that also use the application you are currently running).
      • Open the file again and examine the certificate of trust details and set the Always trust macros from this publisher box if you trust the certificate issued by the publisher.
      • Click the Enable button to allow the macro to run.
  • High or Very High and the macro was not signed and was automatically disabled by the application. Use the following procedure to enable the macro, if you have verified you can trust the source of the unsigned macro:
    • • Select the Tools menu option and then select Macro and Security. In the resulting Security dialog, set the security level to Medium by clicking the Medium radio button.
      • Close the file and any other instances of the application currently running on the computer.
      • Open the file again and click the Enable button when prompted to allow for the unsigned macro to run.

• • 
    [*]When you have accepted the macro, return the macro security level to its previous setting.
    
  
  

1. 
   [*]Another instance of your application has a lock on the security settings and is disallowing any changes to security settings.
   
   • It is recommended to close all Office related applications or other instances of the same application when you attempt to make changes to your security settings.
   [*]Visual Basic for Applications (VBA) is not installed on your computer.
   
   • If VBA is not installed, it is not possible to accept certificates of trust attached to VBA macros. Therefore, any VBA macro attempting to run, even with an attached certificate of trust, cannot be run because the necessary macro interpreter is not installed on the system. Two scenarios can cause this error:
     • • Your administrator chose not to install VBA
       • The version of Office you are running does not install VBA by default
   • If you are running an instance of Office that does not provide VBA as an installable feature, you will need to upgrade your version of Office to run VBA macros.
   [*]The certificate of trust is invalid - (when this occurs, you cannot select the Enable button).
   
   • The certificate is no longer trusted, was revoked by the issuing authority, or is damaged. There are several other possibilities which you may need to explore as to why the certificate is no longer recognized as valid by your system. It is recommended that you not trust or run a macro that has an invalid certificate.
   A troubleshooter help topic is available online regarding this issue.
   

 

[MrKowz]

It's possible - try changing your security settings to a lower setting, and make sure that you enable macros.