Printing From An "Alert" Cell

[Ra]

Problem:

I have names and addresses in a file, for example in this case, in two Cell
A5 and B5, A6, B6, A7,B7, etc.

I obtain an red alert in, say, cell G5 or G6 or G7, etc. Once the red alert
is invoked, I want to click on that alerted cell in order to send a reminder
letter. For example:

Customer Name
Customer Address
Post code

Dear "name"

Suject: xxxxxx

Please note that you have to attend your appointment on yyyyy

Your Sincerely

Ra


I want to be able to send the reminder letter automatically to the printer
once I press the alerted cell.

Please can you help,

 

[Jarek Kujawa]

presume your names and addresses are in Sheet1
place the contents of the letter into Sheet2 with Customer Name in A1,
Address in A2 and "Dear customer" in A5
you did not mention where the Post code is located so it won't be
adjusted automatically

press ALT+F11 to get to VBA window
then in Project-VBA Project window double click on Thisworkbook and
select "Workbook" (slightly to the left where "(General)" should be
seen)
paste the following code there
pls note that the ColorIndex for red is 255 in Excel2007 and is
different in previous versions so that should be adjusted if the need
be

wouldn't you prefer the macro to print ALL letters refering to those
red alert cells in one go?

if so pls let me know, I'll adjust the code for you

pls click YES if this helped you

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal
Target As Range, Cancel As Boolean)
If ActiveCell.Column = 7 And ActiveCell.Interior.ColorIndex = 255 Then
Call Makro1
End If
End Sub

Sub Makro1()
Sheets("Sheet2").Range("A" & 1) = Sheets("Sheet1").Range("A" &
ActiveCell.Row)
Sheets("Sheet2").Range("A" & 2) = Sheets("Sheet1").Range("B" &
ActiveCell.Row)
Sheets("Sheet2").Range("A" & 5) = "Dear " & Sheets("Sheet1").Range
("A" & ActiveCell.Row)
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,1,,,TRUE,,FALSE)"
End Sub

 

[Jarek Kujawa]

presume your names and addresses are in Sheet1
place the contents of the letter into Sheet2 with Customer Name in A1,
Address in A2 and "Dear customer" in A5
you did not mention where the Post code is located so it won't be
adjusted automatically

press ALT+F11 to get to VBA window
then in Project-VBA Project window double click on Thisworkbook and
select "Workbook" (slightly to the left where "(General)" should be
seen)
paste the following code there
pls note that the ColorIndex for red is 255 in Excel2007 and is
different in previous versions so that should be adjusted if the need
be

wouldn't you prefer the macro to print ALL letters refering to those
red alert cells in one go?

if so pls let me know, I'll adjust the code for you

pls click YES if this helped you

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal
Target As Range, Cancel As Boolean)
If ActiveCell.Column = 7 And ActiveCell.Interior.ColorIndex = 255 Then
Call Makro1
End If
End Sub

Sub Makro1()
Sheets("Sheet2").Range("A" & 1) = Sheets("Sheet1").Range("A" &
ActiveCell.Row)
Sheets("Sheet2").Range("A" & 2) = Sheets("Sheet1").Range("B" &
ActiveCell.Row)
Sheets("Sheet2").Range("A" & 5) = "Dear " & Sheets("Sheet1").Range
("A" & ActiveCell.Row)
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,1,,,TRUE,,FALSE)"
End Sub

 

[Jarek Kujawa]

forgot to mention that you have to double-click a cell in G column for
those macros to work

 

[Jarek Kujawa]

forgot to mention that you have to double-click a cell in G column for
those macros to work

 

[Ra]

Hi Jarek

Many thanks for your reply and for your help.

I have placed the macro as per your instructions, however, it is not working
for some reason. In column G, I have formulas which are all protected. Does
this make a difference? Column A is not protected.

 

[Ra]

Hi Jarek

Many thanks for your reply and for your help.

I have placed the macro as per your instructions, however, it is not working
for some reason. In column G, I have formulas which are all protected. Does
this make a difference? Column A is not protected.

 

[Ra]

Forgot to add, when I run macro in 2007, I get:

"compile error
Expected identifier".

Hope this helps,

Thank you.

 

[Ra]

Forgot to add, when I run macro in 2007, I get:

"compile error
Expected identifier".

Hope this helps,

Thank you.