Author: Debra Dalgleish

There is a sample file on my Contextures site, in which you can enter invoice details, then print all the new invoices by clicking a button.

I’ve updated the file, and you can now download the xlsm version, if you’re using Excel 2007 or a later version.

Store Invoice Details in Table

In the new version, the invoice data is stored in a named Excel table, and a named range – Database – is based on that table. The range is dynamic, because it is based on the named table, so it will include new rows as they are added.

Invoices that have been printed are marked with an X, and the new invoices do not have a mark in column A.

Count Unprinted Invoices

On the Invoice sheet, you can see how many invoices have not been printed. A formula calculates that number by counting invoice numbers and subtracting the count of X marks:

=COUNTA(Data!B2:B25)-COUNTA(Data!A2:A25)

Print the Invoices

When you click the Print Invoices button, a macro filters the list, to show only the records with a blank cell in column A. This code is quite different from the previous version, because it uses List AutoFilter VBA, which is only available in newer versions of Excel.

Then, for each of those records, the invoice is printed, and then the record is marked with an “X”.

Download the Sample File

To see how the invoice printing macro works, and to view the code, you can download the sample file. On the Sample Spreadsheets page, go to the Functions section, and look for FN0009 – Print Unmarked Invoices

The zipped file is in Excel 2007/2010 format (xlsm), and contains macros.

___________________

Yes, I know that it’s a bad idea to use the SendKeys method in Excel, because strange things can happen.

However, it’s handy in a few situations, and I use SendKeys in a few of my Comments macros.

SendKeys Example

For example, in this macro to insert a blank comment, without a user name, the comment opens for editing, at the end of the macro.

Sub CommentAddOrEdit()
  Dim cmt As Comment
  Set cmt = ActiveCell.Comment
  If cmt Is Nothing Then
    ActiveCell.AddComment text:=""
  End If
  SendKeys "+{F2}"
End Sub

Send Keyboard Shortcuts

In that example, the SendKeys line simulates using the keyboard shortcut – Shift + F2 – to edit the comment in the active cell.

SendKeys Doesn’t Run

While I was updating the Comments VBA page, I wanted to test a few of the macros, to make sure that they still worked in Excel 2010. To make it easier to run a macro, I added a keyboard shortcut for it – Ctrl + Shift + C.

Shortcut Problem

When I tested the macro, using that shortcut, it inserted the comment, but the comment didn’t open for editing. Hmmm…maybe that shortcut code doesn’t work in Excel 2010.

But, when I ran the macro from the Macro window, instead of the shortcut, it worked correctly. So, the problem wasn’t the SendKeys code.

There was something funny happening with the shortcut to run the macro.

Add a Wait Line

Some Googling led me to the Microsoft site, where this problem is in the MSKB: Error Using SendKeys in VB with Shortcut Key Assigned

The problem occurs because this is a very short macro, and I was still pressing the Ctrl + Shift keys when the macro runs the SendKeys statement. And that messes up the SendKeys keystrokes. See – I told you that SendKeys was risky!

Suggested Solution

The suggested solution is to add a Wait line in the macro, just before the SendKeys code. So, I altered the macro, and now it runs correctly when I use the keyboard shortcut.

Sub CommentAddOrEdit()
  Dim cmt As Comment
  Set cmt = ActiveCell.Comment
  If cmt Is Nothing Then
    ActiveCell.AddComment Text:=""
  End If
    Application.Wait (Now() + TimeValue("00:00:01"))
    SendKeys "+{F2}"
End Sub

_________________________