PDA

View Full Version : Solved: Deleting a part from a parts list with a macro



Bern494
07-11-2007, 08:26 AM
Hi
I seem to having difficulty with my code. What I am trying to achieve is this:
My parts entry form works ok but I need a delete from time to time a part from the list. I have placed a delete button on the form, used for entering a new part. I would also like a warning message along the lines of 'Are you sure you want to delete this part' All my code from the form shown below.


Private Sub cmdCancel_Click()
Unload Me
Sheets("Imput").Select
Range("B2").Select
End Sub
Private Sub UserForm_Initialize()
txtpartn0.Value = ""
txtcost.Value = ""
txtnetcost.Value = ""
End Sub

Private Sub cmdpartentry_Click()
Application.Calculation = xlCalculationManual
Dim c As Range
With Sheets("Parts Issue").Columns("A:A")
Set c = .Find("ZZ", LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
c.Value = txtpartn0.Value
c.Offset(0, 1) = txtcost.Value
c.Offset(0, 2) = txtnetcost.Value
End If
End With
Application.Calculation = xlCalculationAutomatic
Unload frmpartenter
Call SortParts
End Sub

Private Sub cmdpartdelete_Click()
Application.Calculation = xlCalculationManual
Dim c As Range
With Sheets("Parts Issue").Columns("A:A")
Set c = .Find("ZZ", LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
If Value = c Then
cell.EntireRow.Delete
End If
End If
End With
Application.Calculation = xlCalculationAutomatic
Unload frmpartenter
Call SortParts
End Sub

'ZZ is a default name in the list for sorting purposes.

Many thanks
Bern (Using Excel 2003)

lucas
07-11-2007, 08:43 AM
Hi Bern,
I would request that you post your workbook with fake data so we don't have to reproduce it....

I don't understand how this works unless it is a variable derived from a control???

'ZZ is a default name in the list for sorting purposes.


Otherwise ZZ in column A is all you would find??

to attach your workbook: hit post reply at the lower left of the last post and after entering your message scroll down till you find manage attachments.

mdmackillop
07-11-2007, 10:22 AM
Hi Bern
Welcome to VBAX.
FYI, when you post code, select it and click the VBA button to format it as shown.
Regards
MD

mdmackillop
07-11-2007, 10:30 AM
Something like

Private Sub cmdpartdelete_Click()
Dim Response As Integer
Dim c As Range
Dim MyPart As String
MyPart = Me.combobox1 '<== amend to suit
With Sheets("Parts Issue").Columns("A:A")
Set c = .Find(MyPart, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Response = MsgBox("Are you sure you wish to delete?" & _
vbCr & MyPart, vbCritical + vbYesNo)
If Response = vbYes Then c.EntireRow.Delete
End If
End With
Unload frmpartenter
Call SortParts
End Sub

Bern494
07-11-2007, 10:41 AM
Hi
Thanks for the replies. I have attached a parts tester as instructed, as you see I work off 202 cells/rows but probably increase over time.
Thank you for the indenter. I have edited my original post.

Bern
6189

lucas
07-11-2007, 11:01 AM
Using Malcolm's code. Just put the part number in the textbox and hit delete.

Bern494
07-11-2007, 11:18 AM
Many thanks for all your help
Works lovely.
Bern

mdmackillop
07-11-2007, 11:30 AM
Hi Bern,
I had a little time to play around so here's an example using a combobox, for data entry/updating/deletion. It uses a dynamic range name "Parts" (see Insert/Name/Define Parts for the method)
BTW, there is no need to turn off/on the calculation for small amounts of data. I would leave this unless you experience real delays.

Bern494
07-11-2007, 02:21 PM
Hi
Thank you MD that certainly improves the coding.

But alas its opened a new can of worms and if I need to I will start a new thread. The deletion from the parts list works great, but this list of parts is referenced in about 14 other worksheets i.e. it uses the column of parts and prices directly by linking, so consequently when you run the macro the main parts list updates bit it leaves #REF in the row of other sheets.

My thoughts on a solution to this would be to extend the parts issue list, and instead of deleting, move it to bottom, or what I was thinking, to rename the part you wish to delete with a prefix of say 'zzz to push it to the bottom of the list after running the sort?

Any thoughts
Bern

mdmackillop
07-11-2007, 03:12 PM
Better to tidy up properly.
You can use SpecialCells (a very useful function) to find error cells. On the spreadsheet, press F5/Special to use this facility.

Sub DelErrors()
Dim sh As Worksheet
On Error Resume Next
For Each sh In Sheets
sh.Cells.SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
Next
End Sub