Excel 2007 WorkSheet_Change

Armand · October 15th, 2010, 04:29 PM

Hello,
I've got a sheet that needs code to accomplish a few things. Hope you can help.
1) I enter a part # into "A1" (part# can be numeric or alphanumeric)
after hitting enter the code searches down column "A" for a match, If found cell focus move to column "F" of that row so I can enter a quanity. If no match found display error "No Match Found", click "Ok" to acknowledge alert and cursor moves back to "A1" for next part entry.
That seems to be working w/ my crude coding.

2) After I enter a quanity in a cell in column "F" I would like the cell focus to move back to "A1" for next part entry.
Not sure how to make this second step happen.

This code resides in the sheet itself. thanks again.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, SearchRng As Range

'****************************************
' set to cell where search value is input.
Const SearchCell As String = "A1"
'_________________________________________

'****************************************
' set to range of cells being searched
' e.g. ActiveSheet.Cells or Columns("A:B") or Rows("5:50") etc
Set SearchRng = Columns("A")
'_________________________________________
If Target.Address <> Range(SearchCell).Address Or Range(SearchCell) = "" Then Exit Sub
Set rng = SearchRng.Find(What:=IIf(Right(Target.Value, 1) = "*", Left(Target.Value, Len(Target.Value) - 1), Target.Value), _
LookIn:=xlValues, _
LookAt:=IIf(Right(Range(SearchCell), 1) = "*", xlPart, xlWhole))
If rng Is Nothing Then GoTo NotFound
If rng.Address = Target.Address Then GoTo NotFound
rng.Activate
ActiveCell.Offset(0, 5).Select
Exit Sub
NotFound:
ActiveCell.Offset(0, -1).Select
MsgBox "Not found!", vbOKOnly, "Search for '" & Target.Value & "'"

End Sub

malcolmdixon · October 17th, 2010, 07:04 AM

Never done much in Excel but done plenty of VBA in Access, however I have just had a quick look at the Object Model for Excel and there is a SheetChange Event.
If you place code in there Range("A1").Select then whenever a cell changes value it will move focus back to A1. You will have to put a condition in to check that the entry was made in column F, should be easy as a range object is a parameter of the event which holds the cell that changed.

HTH (hope that helps).

Malc.

mtranchi · October 26th, 2010, 07:14 PM

sometimes it helps to see the actual code

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 Then 'it's the th column, in other words, "F"
    Application.EnableEvents = False
    Range("A1").Select
    Application.EnableEvents = True
End If
End sub