Help with macro

[Theslaz]

I have a workbook with 5 pages ( I apologize if those are not the right
terms )

1 for "Reference tables"
1 for "Invoice data Entry"
1 for "Job Pending & Completion List"
1 for "Print Invoice"
1 for "Summary Statistics"

In the Print "Invoice page" it shows all the necessary information that
I need for the invoice retrieved from the "Invoice Data Entry" page.

What I would like to have is a button on my "Print Invoice" page that
would print the invoice ( print to fax ) and at the same time enter the
date under a column for "Date Sent" in the "Job Pending & Completion
List" page.

It appears what I have do do in my macro is to retrieve the Invoice
number from the "Print Invoice" page and search for that number in the
"Job Pending & Completion List" and then insert the proper date under
the "Date Sent" column in the row for that invoice number.

I have been entering this date manually; but all to often I forget and
than I wind up resending the fax. Employer at other end has no sense of
humor and gets very annoyed! Trying to eliminate that.

 

[Simon Lloyd]

Theslaz, join our forums where you can attach a workbook where we woul
be glad to resolve this issue for you

It would be difficult to solve here as you need to let us know th
range the invoice number appears on, the range the date appears on, th
range the completed date should go and if you want the job status t
change after printing we also need to know the range that appears in no
to mention the sheets for all the above

--
Simon Lloy

Regards
Simon Lloy
'The Code Cage' (http://www.thecodecage.com

 

[theslaz]

I am now a member. Let me know what you require from me and I will send
it.

Thanks in advance

 

[Simon Lloyd]

Welcome to The Code CageOn your next pos
before submitting it scroll down a little further where you will see
Manage Attachment button click this and upload your workbook and we'l
take it from there

--
Simon Lloy

Regards
Simon Lloy
'The Code Cage' (http://www.thecodecage.com

 

[theslaz]

Here is the file you requested. Need more info; just ask

+-------------------------------------------------------------------
|Filename: Master Invoice 2008-09.xls
|Download: http://www.thecodecage.com/attachment.php?attachmentid=24
+-------------------------------------------------------------------

 

[Simon Lloyd]

Add a command button to your worksheet and add this code, i'm not to
sure on the fax as everyone's is different but the rest of the code wil
work fine

Code
-------------------

Dim fRange As Strin
fRange = Sheets("Job Pending & Completion List").Cells.Find(What:=Sheets("Print Invoice").Range("I2").Value,
After:=Sheets("Job Pending & Completion List").Range("A1"), LookIn:=xlValues, LookAt:=
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=
False, SearchFormat:=False).Addres
With Sheets("Job Pending & Completion List"
.Range(fRange).Offset(0, 7).Value = Dat
End Wit
Application.ActivePrinter = "microsoft fax
ActiveSheet.PrintOu


-------------------

--
Simon Lloy

Regards
Simon Lloy
'The Code Cage' (http://www.thecodecage.com

 

[theslaz]

I have added a Command button and inserted the code. You are right about
the Fax as I keep getting an error. What I have on my computer is a HP
OfficeJet J5700 series All-In-One printer. When I want to fax; I just
tell it to use the OfficeJet 5700 series Fax.

I am using XP Home Edition; Version 2002; Service Pack 3

 

[Simon Lloyd]

Like i said i'm not sure about your fax and i don't know the correct
name for that however if you set your default printer to be the fax you
can use this:


Code:
--------------------

Dim fRange As String
fRange = Sheets("Job Pending & Completion List").Cells.Find(What:=Sheets("Print Invoice").Range("I2").Value, _
After:=Sheets("Job Pending & Completion List").Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Address
With Sheets("Job Pending & Completion List")
.Range(fRange).Offset(0, 7).Value = Date
End With
ActiveSheet.PrintOut

--------------------
Did the rest of the code work for you?


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Theslaz]

Like i said i'm not sure about your fax and i don't know the correct
name for that however if you set your default printer to be the fax you
can use this:


Code:
--------------------

Dim fRange As String
fRange = Sheets("Job Pending & Completion List").Cells.Find(What:=Sheets("Print Invoice").Range("I2").Value, _
After:=Sheets("Job Pending & Completion List").Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Address
With Sheets("Job Pending & Completion List")
.Range(fRange).Offset(0, 7).Value = Date
End With
ActiveSheet.PrintOut

That worked. I will set the Fax printer as the "Default Printer"

I thank you.

Are you up for one more problem/item I would like to setup?

The HP printer that I use does not keep a copy of the actual fax that I
have sent. ( an older model Hp printer did and I used that feature a lot
) What I would like is to have a file generated showing only the
actual invoice and saved on my computer. I would like that file to be
named using the Invoice number and saved as a .CSV file. The file would
be saved to my "T" drive which is an external drive that I use for
backups. I could name the directory "Fax Backups"

 

[Theslaz]

That worked. I will set the Fax printer as the "Default Printer"

I thank you.

Are you up for one more problem/item I would like to setup?

The HP printer that I use does not keep a copy of the actual fax that I
have sent. ( an older model Hp printer did and I used that feature a lot
) What I would like is to have a file generated showing only the actual
invoice and saved on my computer. I would like that file to be named
using the Invoice number and saved as a .CSV file. The file would be
saved to my "T" drive which is an external drive that I use for backups.
I could name the directory "Fax Backups"

Problem. When I run the code; the Excel program hangs. It just sits at
the "Printing" dialog box and nothing happens. I tried to print the
invoice without using the code; and it printed no problem. I then tried
via the code and it hangs.

 

[Simon Lloyd]

Theslaz, please post in the forum as you can benefit from the interface
better.

The attached file worked fine for me, although i do not have a fax
printer, i simply print out to my default printer and a csv backup is
made in T:\Fax Backup.


+-------------------------------------------------------------------+
|Filename: Master Invoice 2008-09.xls |
|Download: http://www.thecodecage.com/attachment.php?attachmentid=25|
+-------------------------------------------------------------------+

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[theslaz]

I think that I have fixed the printing problem. I placed the code for
the printer at the beginning of the macro and it appears to be working.

ActiveSheet.PrintOut
Dim fRange As String
fRange = Sheets("Job Pending & Completion
List").Cells.Find(What:=Sheets("Print Invoice").Range("I2").Value, _
After:=Sheets("Job Pending & Completion List").Range("A1"),
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Address
With Sheets("Job Pending & Completion List")
..Range(fRange).Offset(0, 7).Value = Date
End With
End Sub

 

[Simon Lloyd]

Check out the latest attachment i made as it has the save code there!

Newsgroups don't cope with the functionality of forum boards very well
which is why i asked you to post here!


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[theslaz]

Sorry for the double post! Some reason or another the original post
didn't show.

Got your file and everything is working perfect. Couldn't ask fo
anything better.

Thanks for your trouble

 

[Simon Lloyd]

Glad we could be of help

If your query has been solved please take a moment to let us an
eveyone else know by going to Thread Tools>Mark Thread Solved,shown b
this icon2

+-------------------------------------------------------------------
|Filename: marksolved.gif
|Download: http://www.thecodecage.com/attachment.php?attachmentid=27
+-------------------------------------------------------------------

--
Simon Lloy

Regards
Simon Lloy
'The Code Cage' (http://www.thecodecage.com

 

[theslaz]

Glad we could be of help!

If your query has been solved please take a moment to let us and
eveyone else know by going to Thread Tools>Mark Thread Solved,shown by
this icon27

Went to "Thread Tools" and it only gave three choices: Show Printable
Version; Email This Page and Unsubscribe from this thread!

 

[Simon Lloyd]

Ah yes, my fault, its because you continued the thread from the one
imported from the newsgroups.

No worry i've done it!

Thanks for that.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)