Hi all,

Wonder if anyone could help me, I am struggling to find a way of
getting a macro to (upon change to a range) check the number that has
been put in across all of the tabs in the worksheet and remove that
number and its row (hopefully not leaving a blank row behind) from all
of its instances in all tabs.

It is a unique number and the key to the whole sheet (it is a barcode
number read in through a scanner) and I want (to keep my figures
correct) a way of determining that this record no longer wants to be
counted in with the figures.

or i could do it another way, I suppose i dont really need it to be
deleted, I could just do with it marking a cell on that row that is
used for the 'count' so its not counted but then that would involve
changing all of my code thus far to populate a 'count' cell as well.

so i have a seperate tab at the end which would be used to scan in all
the numbers that are 'finalised' and upon change to this tab i need it
to check the number put in and make the changes to stop it being
counted in.

Could anyone help?

Many thanks in advance

Duncan

See

http://xldynamic.com/source/xld.Deleting.html

--
Kind regards,

Niek Otten

"Duncan" wrote in message
oups.com...
Hi all,

Wonder if anyone could help me, I am struggling to find a way of
getting a macro to (upon change to a range) check the number that has
been put in across all of the tabs in the worksheet and remove that
number and its row (hopefully not leaving a blank row behind) from all
of its instances in all tabs.

It is a unique number and the key to the whole sheet (it is a barcode
number read in through a scanner) and I want (to keep my figures
correct) a way of determining that this record no longer wants to be
counted in with the figures.

or i could do it another way, I suppose i dont really need it to be
deleted, I could just do with it marking a cell on that row that is
used for the 'count' so its not counted but then that would involve
changing all of my code thus far to populate a 'count' cell as well.

so i have a seperate tab at the end which would be used to scan in all
the numbers that are 'finalised' and upon change to this tab i need it
to check the number put in and make the changes to stop it being
counted in.

Could anyone help?

Many thanks in advance

Duncan

Niek,

Many thanks for this but I am not sure that this would get what i want.
I have played around with the code but it appears to be a one time
usage simply to delete duplicate data.

Perhaps i rambled on a bit too much above when i tried to explain!

I need something that can look at a number i put in to the next cell on
a 'finalised' tab, compare it against the 5 other tabs in the worksheet
and then either delete the containing rows or mark one of the cells in
the row so that i can dis-count it when i tally up. it will be a list
of finished numbers on this tab which are actioned as they are put on
(through the barcode scanner)

I will paste where i am with it below, perhaps i have already started
to go wrong but i am borrowing code from a previous project and trying
to amend it, the main hurdle is getting it to look at the 5 other tabs.

see code below:

Private Sub submitint_Click()
Dim txt As String
Dim TitleText As String
'sets up error handler
On Error GoTo errorhandler
'disable screeen flickering
Application.ScreenUpdating = False

'ensuring all fields are filled in
If bcn.Value <= "" Then
GoTo problem
Else
'set up range to search
Range("A1:A65536").Select

'sets the find option to match the textbox to the range
Selection.Find(What:=Interiminput.regint.Value, LookIn:=xlValues,
SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate

'here is the problem so far, I need to put something to search all
tabs and then do something to the ones found

'these lines will put a yes next to the number to say that it has
been found and deleted/or marked done
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = ("Yes")
' clear the cell for next entry
bcn.Value = ""
MsgBox "Done", vbOKOnly, " Status "
Exit Sub

End If

'show message box if match not found
errorhandler:
MsgBox "Barcode not found! Please retry", vbOKOnly, "Status"
bcn.Value = ""
bcn.SetFocus
GoTo nd

problem:
MsgBox "Nothing to Submit, Re-Scan Barcode and try again",
vbRetryCancel



nd:
End Sub

This sounds very dangerous to me.

If you make a typing mistake, you may have just wiped out the wrong line on 5
different worksheets.

But if you want...

Select the Final worksheet, rightclick on its tab and choose view code.

Paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim wks As Worksheet
Dim FoundCell As Range

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then
Exit Sub
End If
If IsNumeric(Target.Value) = False Then Exit Sub
If Trim(Target.Value) = "" Then Exit Sub

For Each wks In Me.Parent.Worksheets
If wks.Name = Me.Name Then
'do nothing
Else
Do
With wks.Range("a:a")
Set FoundCell = .Find(what:=Target.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext)
End With

If FoundCell Is Nothing Then
'done with that worksheet
Exit Do
Else
FoundCell.EntireRow.Delete
End If
Loop
End If
Next wks

End Sub

I think I'd use the same kind of code, but instead of it firing when I made a
typing change, I'd assign that code to a button from the Forms toolbar (in Row 1
with row 1 frozen so that it's always visible) and use this:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FoundCell As Range
Dim myCell As Range
Dim ActSheet As Worksheet
Dim resp As Long

Set ActSheet = ActiveSheet
With ActSheet
Set myCell = .Cells(ActiveCell.Row, "A")
End With

If IsNumeric(myCell.Value) = False Then Exit Sub
If Trim(myCell.Value) = "" Then Exit Sub

resp = MsgBox(Prompt:="Are you sure you want to clean up: " _
& myCell.Value & "?", Buttons:=vbYesNo)

If resp = vbNo Then
Exit Sub
End If

For Each wks In ActSheet.Parent.Worksheets
If wks.Name = ActSheet.Name Then
'do nothing
Else
Do
With wks.Range("a:a")
Set FoundCell = .Find(what:=myCell.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext)
End With

If FoundCell Is Nothing Then
'done with that worksheet
Exit Do
Else
FoundCell.EntireRow.Delete
End If
Loop
End If
Next wks

End Sub




Duncan wrote:

Niek,

Many thanks for this but I am not sure that this would get what i want.
I have played around with the code but it appears to be a one time
usage simply to delete duplicate data.

Perhaps i rambled on a bit too much above when i tried to explain!

I need something that can look at a number i put in to the next cell on
a 'finalised' tab, compare it against the 5 other tabs in the worksheet
and then either delete the containing rows or mark one of the cells in
the row so that i can dis-count it when i tally up. it will be a list
of finished numbers on this tab which are actioned as they are put on
(through the barcode scanner)

I will paste where i am with it below, perhaps i have already started
to go wrong but i am borrowing code from a previous project and trying
to amend it, the main hurdle is getting it to look at the 5 other tabs.

see code below:

Private Sub submitint_Click()
Dim txt As String
Dim TitleText As String
'sets up error handler
On Error GoTo errorhandler
'disable screeen flickering
Application.ScreenUpdating = False

'ensuring all fields are filled in
If bcn.Value <= "" Then
GoTo problem
Else
'set up range to search
Range("A1:A65536").Select

'sets the find option to match the textbox to the range
Selection.Find(What:=Interiminput.regint.Value, LookIn:=xlValues,
SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate

'here is the problem so far, I need to put something to search all
tabs and then do something to the ones found

'these lines will put a yes next to the number to say that it has
been found and deleted/or marked done
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = ("Yes")
' clear the cell for next entry
bcn.Value = ""
MsgBox "Done", vbOKOnly, " Status "
Exit Sub

End If

'show message box if match not found
errorhandler:
MsgBox "Barcode not found! Please retry", vbOKOnly, "Status"
bcn.Value = ""
bcn.SetFocus
GoTo nd

problem:
MsgBox "Nothing to Submit, Re-Scan Barcode and try again",
vbRetryCancel

nd:
End Sub

--

Dave Peterson

Dave,

Many thanks for this, works a treat.

(just a few tweaks i am experimenting with now! I think you are right
and i should get something else to happen to discount it from the
figures instead of deleting the row)

many thanks again

Duncan