Checkbox and Macro to delete specific data in corresponding row

**doubleeinc** · 02-20-2012, 01:16 PM

I have an inventory spreadsheet. I am trying to make it as user friendly as possible. Currently, the inventory data begins in row 22 (D22;Q22). I would like to add checkboxes for every line of the inventory in column C. The user could then place a check beside all inventory items he/she wanted to delete. Then, I would like to create a macro to delete the corresponding data from that row (if checkmarks in C22 and C41, then delete data from D22;Q22 and D41;Q41) then uncheck all boxes, resort & save the inventory. I already have a macro (assigned to a button) to resort and save the inventory. Could someone help with creating the rest of the needed macro? I am lost.

**arlu1201** · 02-20-2012, 02:57 PM

Why dont you attach a sample file with some dummy data?

**doubleeinc** · 02-22-2012, 01:32 PM

Thank you.

I have attached a file for your consideration.

What I'd like to do:
1) Alter macro to create checkboxes in B22:B5000 linked to C22:C5000 respectively in which the checkboxes would have no caption or text and are possibly centered in the column. See references below.

2) Create a macro that would search Column C for "TRUE" and delete the information in all respective rows, columns D through Q. Then, my "uncheck" and "reset" macros (already created - "uncheck"- unchecks all boxes, "reset"- re-sorts the information in columns D22:Q22 through D5000:Q5000 and saves the spreadsheet).

If anyone can help me with any of these macros, I would greatly appreciate it. If you have suggestions for cleaning up my current macros (listed below), that would be helpful as well.

Reference information:
1) My current macro for item 1 is as follows:
Sub ADDCBOXES()
Dim c As Range
For Each c In Range("B41:B43")
c.RowHeight = 15
ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height) _
.LinkedCell = c(1, 2).Address 'c(1, 2) would offset 1 columns to right
Next
End Sub

2) There are a couple of macros I already have to go along with the code I need.

This macro will include a message box at the beginning.
It will most likely look like this:Dim nResult As Long
nResult = MsgBox( _
Prompt:="Are you sure you want to delete the selected inventory items?", _
Buttons:=vbYesNo)
If nResult = vbno Then
exit sub
End If
'rest of macro

My current re-sort/reset macro looks like this:
Sub SORT()

'
Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=7
Selection.AutoFilter Field:=8
Selection.AutoFilter Field:=9
Selection.AutoFilter Field:=10
Selection.AutoFilter Field:=11
Selection.AutoFilter Field:=12
Selection.AutoFilter Field:=13
Selection.AutoFilter Field:=14
Range("D22:Q5000").Select
Selection.SORT Key1:=Range("E22"), Order1:=xlAscending, Key2:=Range("F22" _
), Order2:=xlAscending, Key3:=Range("G22"), Order3:=xlAscending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers, DataOption3 _
:=xlSortNormal
Range("B5000").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
ActiveWorkbook.Save
End Sub

Here is my macro to uncheck all checkboxes
Sub UNCHECK()
ActiveSheet.CheckBoxes.Value = False
End Sub