Finding photos on a worksheet and deleting them

[csenor]

I have a pawn shop invoice that I want to be able to snap a photo of the jewelry and paste it to the bottom of each invoice. The macro I've been working on saves the record, while keeping the invoice open. It also clears out the added items, but I want it to be able to clear the photos attached to the worksheet. How do you search for photos? What's the proper syntax?

 

[northwolves]

Try:

Sub Macro1()
    ActiveSheet.DrawingObjects.Delete
End Sub

 

[pgc01]

Hi

Not sure I understand. You mean that you have a worksheet with pictures embedded and you want to delete them?

 

[csenor]

I want to insert a photo from my camera folder on my computer of the jewelry in that transaction. When I use the macro to save the invoice, I want the photos to save as well. The other part of the macro clears out the added data, but right now doesn't take into account the photo. I took notice to the code that northwolves postes and have only one question. On the worksheet, I added a button by adding a rectangle and assigning it the macro. By using drawobjects, would that delete the rectangle as well as the photos? If so, what's my alternative?

 

[pgc01]

On the worksheet, I added a button by adding a rectangle and assigning it the macro. By using drawobjects, would that delete the rectangle as well as the photos?

Hi

When you tested northwolves's code, did it delete the button?

 

[csenor]

Yes. It deleted photos and rectangle. If it makes a difference, the rectangle is in the top third of the page and the photos are at the bottom.

 

[pgc01]

Yes. It deleted photos and rectangle. If it makes a difference, the rectangle is in the top third of the page and the photos are at the bottom.

I see.

If they are picture objects, I'd use

ActiveSheet.Pictures.Delete

Please try.

 

[csenor]

Thanks PGC. I wondered if you could review the rest of my code and help me get it right? H6 is the Invoice #. H7 is a cell on the invoice that has a =now() formula so the time always coincides with the transaction. Another person on the message board suggested the paste special line of code to make the date stop calculating when the invoice is saved. The problem I am having is that it is replacing the =now() formula on the macro-enabled worksheet and replacing it with the pasted value. My goal is to have the option to never have to close the macro-enabled invoice and by clicking the button on the worksheet, save the transaction to a .xlsx file format worksheet in a folder on my computer with the time of the transaction. Another issue I'm having is the last line of code reads NextInvoice. I am under the impression that this should direct the next line of execution to go to the sub named NextInvoice. When I run the code line by line, it doesn't jump to that sub. How do I make this work? Both subs are saved in the same module.

Sub NextInvoice()
Range("h6").Value = Range("h6").Value + 1
Range("b6:b12,e6,e9,a20:g27").ClearContents
ActiveSheet.Pictures.Delete
End Sub

Sub SaveInvWithNewName1()
Dim NewFN As Variant
' Copy Invoice to a new workbook with date/time locked to that date on the saved copy.
Range("h7").Select
Selection.Copy
Range("h7").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets(Array(ActiveSheet.Name, Sheets("Data").Name)).Copy
NewFN = "C:\Users\Chris\Documents\Pawn Shop Invoices\Inv_" & Range("h6").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook, ReadOnlyRecommended:=True
ActiveWorkbook.Close
NextInvoice
End Sub

 

[pgc01]

Hi

If I understand correctly you can:

- copy the worksheets to the new workbook as they are
- in the new workbook freeze the datetime value

like:

Sub SaveInvWithNewName1()
Dim NewFN As Variant
Dim sWS As String

' store the name of the active worksheet
sWS = ActiveSheet.Name

' Copy Invoice to a new workbook and lock date/time to that date on the saved copy.
Sheets(Array(ActiveSheet.Name, Sheets("Data").Name)).Copy
With ActiveWorkbook.Worksheets(sWS).Range("h7")
    .Value = .Value
End With
 
...

 

[csenor]

Thanks PGC. You are really being a help. Now my next hurdle. I want to be able to create a a database with the information from the invoices. On a second sheet in the workbook called "Data", I want to list the Shop ID, Date, Time, Customer Name, Address, D/L#, etc. in front of every item in the transaction. The transaction area on the invoice sheet is between cells a20-f27. So what I'm thinking is: that in cells A1 to L1 of the "Data" worksheet, I referred to the corresponding cell on the Invoice sheet was. But I need to write the code to make it only list the data in A1 to L1

'List and Define the repeating information in row 1 as a variable - The shop info and customer info is 12 columns wide.

'Locate the transaction area of the invoice sheet - which is from A20 to F27

(Either a Loop or IF/Then Statement)
'If data exists in A20:F20, then copy info and paste to column M of "Data" sheet and place the Variable data in the cells before
Then look to A21:F21, and repeat
and so on until A27:F27
If no data is in the next row, stop.

I can't figure out how to post a picture of my invoice to this message board to help explain. If you want to discuss this through personal email, I'd be glad to talk to you.