Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 15th, 2010, 04:29 PM
Registered User
 
Join Date: Oct 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Excel 2007 WorkSheet_Change

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
 
Old October 17th, 2010, 07:04 AM
Authorized User
 
Join Date: Oct 2010
Posts: 64
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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.
 
Old October 26th, 2010, 07:14 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
#NAME? error running Excel 2003 VBA in Excel 2007 steveburn Excel VBA 0 October 24th, 2009 08:47 AM
How to specify relative path to Access 2007 in ADO connection string? [Excel 2007] sektor Excel VBA 4 September 3rd, 2009 11:59 PM
Help! Conversion Excel 2003 VBA codes to Excel 2007 sunny76 BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2 0 August 13th, 2009 05:38 AM
Excel Worksheet_Change option mtowle Excel VBA 1 October 21st, 2005 09:27 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.