Macro to delete record and validation problem

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

There are two things i wanna know and that is...
1)I have 3 worksheets(all in same book) one being User Deletion one
being User addition and the other being the User details. They all have
numbers...and what i want to do is make a macro that looks up the user
number inputted on the user deletion worksheet and delete the record
with that number on the User Details.... These images kidna explain
what i want....
[image: http://www.rabidpanda.co.uk/Dan/ex2.jpg]
And is to be looked up and deleted on....
[image: http://www.rabidpanda.co.uk/Dan/ex1.jpg]

Hopefully thats understood....
Right the second thing is....
2) I have a cell in the User Addition page which is User number... Now
i've tried formuleas to validate this cell so it does not work with any
numbers in the User details list. I allways seem to get it to either
i)Not allow me to use this forumlea ii)allows me to use the forumlea
but it wont let me input anything.....any help? thanks for your help.
 
Bob
1)I have 3 worksheets(all in same book) one being User Deletion one
being User addition and the other being the User details. They all have
numbers...and what i want to do is make a macro that looks up the user
number inputted on the user deletion worksheet and delete the record
with that number on the User Details....
Click to expand...

Here's a change event macro that will do that

Private Sub Worksheet_Change(ByVal Target As Range)

Dim shDet As Worksheet
Dim rngUN As Range
Dim FndRng As Range

If Target.Address = "$B$2" Then
Set shDet = Me.Parent.Sheets("user details")
Set rngUN = shDet.Range("a2", shDet.Range("A65536").End(xlUp))

Set FndRng = rngUN.Find(Target.Value, , , xlWhole)

If FndRng Is Nothing Then
MsgBox "User " & Target.Value & " not found"
Else
FndRng.EntireRow.Delete
End If
End If

End Sub

2) I have a cell in the User Addition page which is User number... Now
i've tried formuleas to validate this cell so it does not work with any
numbers in the User details list. I allways seem to get it to either
i)Not allow me to use this forumlea ii)allows me to use the forumlea
but it wont let me input anything.....any help? thanks for your help.
Click to expand...

What formula are you using? This one worked for me

=COUNTIF(UserNos,B3)=0

with UserNos being a named range.
 
On the first one i insert the code into the macro; then when i want to
go add the macro to a button the macro doesnt show up??? help???
 
Bob

That macro is an event macro that goes in the sheet's class module. It is
triggered when cell B2 is changed. If you would rather have it activated
from a button, then put the code in a standard module, change the name and
remove the Private keyword, e.g. from this

Private Sub Worksheet_Change(ByVal Target As Range)

to this

Sub User_Dele()

Then you need to replace all the occurences of the Me keyword and replace
them with a reference to the sheet. For example, this

Set shDet = Me.Parent.Sheets("user details")

needs to be changed to

Set shDet = ThisWorkbook.Sheets("user details")

Me refers to the sheet and Parent refers to the Workbook, so replace those
keywords with ThisWorkbook to get to the proper sheet.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

Bob said:
On the first one i insert the code into the macro; then when i want to
go add the macro to a button the macro doesnt show up??? help???


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
help me again i type in the code
Sub User_Delete()

Dim shDet As Worksheet
Dim rngUN As Range
Dim FndRng As Range

If Target.Address = "$B$2" Then
Set shDet = Book1.Parent.Sheets("user details")
Set rngUN = shDet.Range("a2", shDet.Range("A65536").End(xlUp))

Set FndRng = rngUN.Find(Target.Value, , , xlWhole)

If FndRng Is Nothing Then
MsgBox "User " & Target.Value & " not found"
Else
FndRng.EntireRow.Delete
End If
End If

End Sub
Click to expand...

Then it says there is an error with If target.Address= "$b$2" Then


HHHHHHHEEEEEEELLLPPPP MEEEEE I'M A NEWBIE
 
Bob

Sorry about that. Try this

Sub User_Delete()

Dim shDet As Worksheet
Dim rngUN As Range
Dim FndRng As Range
Dim UserNo As Range

Set UserNo = ThisWorkbook.Sheets("user deletion").Range("b2")

If Not IsEmpty(UserNo) Then
Set shDet = ThisWorkbook.Sheets("user details")
Set rngUN = shDet.Range("a2", shDet.Range("A65536").End(xlUp))
Set FndRng = rngUN.Find(UserNo.Value, , , xlWhole)

If FndRng Is Nothing Then
MsgBox "User " & UserNo.Value & " not found"
Else
FndRng.EntireRow.Delete
End If
End If

End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

Bob said:
help me again i type in the code

Then it says there is an error with If target.Address= "$b$2" Then


HHHHHHHEEEEEEELLLPPPP MEEEEE I'M A NEWBIE!


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Back
Top