Verify Input

[WLMPilot]

Below is a macro that was written because of a question I had in trying to
control cursor movement. This is provided for info. This macro works.

I have three other questions at this time that may or may not need code
written into this specific macro, but needs a macro nonetheless. I have been
unable to find an answer on my own.

The question involves two sheets, Orders & Items. The macro below is sheet
specific to Orders. In the Orders sheet, a user will enter item numbers and
quantities (Range A15:B90). Currently, within the Orders sheet, I match the
Item number entered with the item number on the Item sheet. If there is no
match, then I have a column (D1590) that indicates INVALID and the
appropriate cells are conditional formatted to change to white font/red
background.

Q1) Since the spreadsheet itself cannot stop the progression of the cursor,
I wouild like to perform this check within the macro and if no match is
found, the cursor stays put until a correct item number is entered, according
to the list in column A on the Item sheet. How can this check be performed
in a macro?

Q2) Is there any type of error routine that needs to be set up?

Q3) I would like to have the cursor placed in cell B1 upon opening the
workbook. How is that accomplished?

CURRENT MACRO to control cursor movement in order form

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ans As Long
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub

'Entry in B4
If Target.Address(0, 0) = "B4" Then
Ans = MsgBox("Ask something.", 4, "MsgBox Title here")
Application.EnableEvents = False
If Ans = vbYes Then
Range("D2").Value = "Yes"
Range("E7").Select
Else
Range("D2").Value = "No"
Range("A15").Select
End If
Application.EnableEvents = True
Exit Sub
End If

'Entry in E9:G9
If Not Intersect(Target, Range("E9:G9")) Is Nothing Then
Target.Offset(-2, 1).Select
Exit Sub
End If

'Entry in H9
If Target.Address(0, 0) = "H9" Then
Range("A15").Select
Exit Sub
End If

'Entry in A15:B90
If Not Intersect(Target, Range("A15:B90")) Is Nothing Then
If Target.Column = 1 Then
Target.Offset(, 1).Select
Else
Target.Offset(1, -1).Select
End If
End If
End Sub


Thanks for your help,
Les

 

[JLGWhiz]

Based on your description of what you are trying to do, it seems that you
would want to use an If...Then...Else statement to check the range of items
for a match and if no match is found, exit the sub which would leave the
cursor in place. You could also use For...Each...Next in combination with an
If staement to check the range without moving the cursor. The If statement
would handle the condition of a match or no match. I can't help you with the
code because I am not sure exactly what you are trying to do, just guessing.

On Q3, You can use:

Sub Workbook_Open()
Worksheets(1).Range(B1).Select
End Sub

Put this in the ThisWorkbook code area instead of the general code module.

 

[WLMPilot]

I am unsure what the code you responded with below does. Basically this is
what is happening. Two sheets: Order & Items. Once the user gets into the
range of A14:B90, item numbers and quantity to reorder is entered. A14:A90
is the item number and B14:B90 is the quantity.

The macro that I included is there for information for you to see if needed.
The macro's purpose in that range is to move the cursor from A14 to B14,
then A15 to B15 (ie item number and quantity) instead of having a downward
movement.

Once an item number is entered, lets say in A14, I want to take that number
and match it against a list on the Item sheet in column A. If there is not
an exact match, ie item number does not exist, then I need a message box to
pop up and state this fact and the cursor to remain in A14 until a match is
made, ie correct item number is entered.

Hope that helps,
Les

 

[JLGWhiz]

This will correct the code I gave you for putting the cursor in cell B1 when
the workbook opens. To use it, you press Alt+F11 to open the VB Edito,
double click on ThisWorkbook in the Project window and paste this code in,
save, then close:

Sub Workbook_Open()
Worksheets(1).Range("$B$1").Select
End Sub

I'll have to work on the code for the other a little. I think you are right
about using the worksheet change, since you are trying to match an input to a
cell. However, you could use the InputBox and put the code in the standard
module rather than attach it to the worksheet. I'll take a look.

 

[JLGWhiz]

You can play around with where you want to put this, but it will go from your
orders sheet (sheet two in the code) to your items sheet (sheet 3 in the
code) find the first occurence of a match and pop up a message box that says
either it found a match or didn't. The cursor on the orders sheet does not
move while this happens. It's your toy now, so play with it as you will.

Sub findItem()
Dim srchRng As Range
Set srchRng = Worksheets(3).Range("A15:A90")
myItem = ActiveCell.Value
With srchRng
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
End If
fndItem = Range(firstAddress).Value
End With
If fndItem = myItem Then
MsgBox "Item Found"
Else
MsgBox "Not there"
End If
End Sub