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.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

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

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....

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.


What formula are you using? This one worked for me

=COUNTIF(UserNos,B3)=0

with UserNos being a named range.

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

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???


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

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

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" wrote in message
...

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???


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
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


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


HHHHHHHEEEEEEELLLPPPP MEEEEE I'M A NEWBIE

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

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

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" wrote in message
...

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


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


HHHHHHHEEEEEEELLLPPPP MEEEEE I'M A NEWBIE!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

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