Input Box question

R

rastus

Board Regular
Joined
Jul 8, 2005
Messages
157
Hi

I am using this code to delete cells from a spreadsheet if the employee name entered in the InputBox matches a name in column D. Does anyone know how I can amend the code so that if the user enters a misspelled name for example, there will be a message box warning ("Invalid Name Entered!"), which the user can press OK and return to the Input Box and try again.

Sub deleter()

Sheets("Staff").Select

Dim Name
Name = Application.InputBox("Please enter employee name", "Staff Termination")
If Name = "" Then
MsgBox "No name entered"
If Name = False Then

End If

Exit Sub

End If

Dim SourceRange As Range, DestRange As Range
Dim DestSheet As Worksheet, Lr As Long
Dim lastrow As Long
Dim i As Long
lastrow = Sheets("Staff").[D65536].End(xlUp).Row
For i = lastrow To 2 Step -1

If Sheets("Staff").Cells(i, 4) = Name Then
Set SourceRange = Sheets("Staff").Rows(i).EntireRow.Range("A1:R1")
Set DestSheet = Sheets("Termination")
Lr = DestSheet.Cells(Rows.Count, "D").End(xlUp).Row
Set DestRange = DestSheet.Range("A" & Lr + 1)
SourceRange.Copy
DestRange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
Sheets("Staff").Rows(i).EntireRow.Range("A1:R1").Delete shift:=xlUp

End If

Next i

Any help would be appreciated.

Thanks

R
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
I would probably reccomend building a unique list of names on another sheet and linking that to a listbox on a form. Put a text box above that and as the user enters letters it refilters the listbox to show matches. That way you are guaranteed that the user picks something in the list.

Happy to knock up a quick example if you need.

Cheers

Dan
 
Upvote 0
Blade Hunters idea is definitely a good way to go for the end user, however, if you mean to simply test for a match of InputBox value to column D then something like the below perhaps - note I renamed your variable Name to strName.

Code: 
Sub Deleter()
Dim strName As String
Sheets("Staff").Select
InsertName:
strName = Application.InputBox("Please enter employee name", "Staff Termination")
If strName = "" Then
    MsgBox "Please Enter Name",vbcritical,"No Name Entered"
    GoTo InsertName
End If
Select Case IsError(Application.Match(strName,Columns(4),0))
    Case True
        MsgBox "Non-Existent Employee - Please Try Again",vbcritical,"Invalid"
        GoTo InsertName
End Select
'....rest of code
End Sub

You may want to think about having an exit route in case you really want to quit without deleting records (ie routine invoked by accident)
 
Upvote 0
If you do choose to go down this path, Here is an example for you.

http://www.sendspace.com/file/siioay

This is a very basic example, You can then add code to sort and distinct the list, I imagine distincting would not be a problem on a list of employees though as I would have thought them to be unique to start with.

Cheers

Dan
 
Last edited:
Upvote 0
Hi

Yes, Blade Hunter's method is better for an end user, but I wanted to stretch my vba skills and I hadn't used code which relies on data input by the user before.

DonkeyOte - you were right about an exit code. I found myself in a never-ending loop as it demanded I enter a value. So I have deleted the GoTo InsertCode lines. It's not perfect but it works.

Thanks for your help.

Cheers.
 
Upvote 0
Blade Hunter said:
If you do choose to go down this path, Here is an example for you.

http://www.sendspace.com/file/siioay

This is a very basic example, You can then add code to sort and distinct the list, I imagine distincting would not be a problem on a list of employees though as I would have thought them to be unique to start with.

Cheers

Dan
Click to expand...

Thanks Blade Hunter. I'm not allowed to download at work but I'll give that example a try later at home.
 
Upvote 0
You can leave the GoTo InsertName lines but after the InsertName: line and before the InputBox line add a MsgBox to give user an exit point

Code: 
InsertName:
If MsgBox("Are You Sure You Wish to Continue - Press No to Cancel",vbYesNo,"Continue?") = vbNo Then Exit Sub
'...rest of code
 
Upvote 0
No Need, I can tell you how to do it.

Create 2 sheets in a workbook called Names and FilteredNames

Create a form and call it FilterNames

On the form put a listbox and call it FilteredList

Above that put a textbox and call it UserFilter

Enter some names on the Names sheet and paste this code into the form's code area.

Code: 
Private Sub UserFilter_Change()
Sheets("FilteredNames").Cells.ClearContents
For X = 1 To Sheets("Names").Range("A" & Rows.Count).End(xlUp).Row
    If UCase(Left(Sheets("Names").Range("A" & X).Value, Len(UserFilter))) = UCase(UserFilter) Then
        If Sheets("FilteredNames").Range("A1") = "" Then
            Sheets("FilteredNames").Range("A1").Formula = Sheets("Names").Range("A" & X).Text
        Else
            Sheets("FilteredNames").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Formula = Sheets("Names").Range("A" & X).Text
        End If
    End If
Next
FilteredList.RowSource = "FilteredNames!A1:A" & Sheets("FilteredNames").Range("A" & Rows.Count).End(xlUp).Row
End Sub

Now just run the form.

It's as simple as that :)
 
Upvote 0
OK, I got there in the end...thx again DonkeyOte


Dim strName As String
Sheets("Staff").Select
InsertName:
strName = Application.InputBox("Please enter employee name", "Staff Termination")
If strName = "" Then
MsgBox "Please Enter Name", vbCritical, "No Name Entered"
If MsgBox("Are You Sure You Wish to Continue - Press No to Cancel", vbYesNo, "Continue?") = vbNo Then Exit Sub
GoTo InsertName

End If
Select Case IsError(Application.Match(strName, Columns(4), 0))
Case True
MsgBox "Non-Existent Employee - Please Try Again", vbCritical, "Invalid"
If MsgBox("Are You Sure You Wish to Continue - Press No to Cancel", vbYesNo, "Continue?") = vbNo Then Exit Sub
GoTo InsertName
End Select
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Move entire row based on expiration date
2
Replies
17
Views
377
rollis13
rollis13
G
  • Solved
Change Macro to import files from hyperlinks
2
Replies
12
Views
523
GarnesGambit
G
E
  • Solved
ActiveCell vs Active range search
Replies
3
Views
254
Joe4
Joe4
K
  • Question
vba copying unique values of a range to another workbook.
Replies
1
Views
124
k_bs
K
I
  • Solved
Set value for range being copied over with VBA
2
Replies
14
Views
320
ItalianPlatinum
I

Forum statistics

Threads
1,216,116
Messages
6,128,926
Members
449,479
Latest member
nana abanyin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top