Button to increase cell value and print document
I added a button from the Forms toolbar (not the control toolbox toolbar) to the
worksheet with the data. Then I assigned this macro to that button.
Option Explicit
Sub testme()
Dim InvCell As Range
Dim myAddr As Variant
Dim DestCell As Range
Dim iCtr As Long
Dim aCtr As Long
Set InvCell = ActiveSheet.Range("A1")
myAddr = Array("a9", "b12", "c3")
With Worksheets("Log")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
If IsNumeric(InvCell.Value) = False Then
MsgBox "Please fix: " & InvCell.Address(0, 0)
Exit Sub
End If
With InvCell
.Value = .Value + 1
End With
With DestCell
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = Now
.Offset(0, 1).Value = Application.UserName
.Offset(0, 2).Value = InvCell.Value
iCtr = 0
For aCtr = LBound(myAddr) To UBound(myAddr)
.Offset(0, iCtr + 3).Value = ActiveSheet.Range(myAddr(aCtr)).Value
iCtr = iCtr + 1
Next aCtr
End With
ActiveWorkbook.Save
ActiveSheet.PrintOut preview:=True 'save some paper while testing
End Sub
Fludium wrote:
This is probably very easy but i dont know to much about macros yet.
Example
I have a invoice ref # 001 (existing document)
I want to press a button labeled "print" (user action)
The macro adds +1 to existing invoice ref# cell. Copy the value of cell "X"
, "Y" and "Z" and invoice ref# cell to spread sheet #2 (creating a database)
Prints active worksheet.
Saves the document
Now I have a new invoice ref #002 and the 3 cell values are stored together
in the database on spread sheet #2.
This way, next time i'm invoicing, my database gets updated and i'll have a
refrence number to all my invoices for history puposes.
Thanks.
--
Dave Peterson
|