Delete Row from User Form in Excel

[hdevadiga]

Hi,

Myself Harindra, i have a problem while deleting the row in excel sheet from User Form.

1) I created a user form and in that i given a search function & delete function, when i search a data using search text box, it displayed in List Box1, but a problem arise when i try to delete the entire row it not getting deleted.

Please Help me in this.

Waiting for reply,

Harindra Devadiga

[AndySuk]

Hi Harindra

If you put a msggbox after C=activecell in your delete button command you'll see it returns " " (nothing)

The problem is this line in your search button code;

Set r = .Range("DataRange").Find(What:=res, LookAt:=xlWhole, LookIn:=xlValues, MatchCase:=False)

Although the code is correctly finding the text it's not actually activating the cell.

In your search button on_click event I added these two lines (the top one is yours)

If Not r Is Nothing Then
founditem = r.Address
Range(founditem).Activate

This then activates the cell found in your search button routine and then the DELETE button routine works ok as it has an activecell to work with.

BE AWARE that if you have multiple search results in your list box then this will only return the LAST item found as your activecell so, if this is likely to happen, it may be worth re-finding the item selected form the list box as part of the DELETE button routine.

Hope all this makes sense!

A

[royUK]

As Andy says your code won't delete the row because it is not active. However, it is not necessary to activate the cell. Change your code to be more efficient by
1. Do not use R to refer to the found cell, because you use R in the code I provided yesterday as a Long.
2.Place at the very top of your code (where you should actually have Option Explicit)

Please Login or Register  to view this content.

Then use rCl where you use r as range.

Please Login or Register  to view this content.

You really should also read the topic I suggested yesterday & also take a look at the DatabaseForm example there.