One possible issue I have noticed is that if the user types in "ch" rather
than picking it from a drop down list in the cell (didn't mention that
earlier) and hits enter, the focus moves one cell away. My code 'Charge( )
looks to see if the active cell has "ch" and if it does, copies the data from
the same row to another sheet. How can I ensure the focus remains on the cell
which has "ch" in it when <enter was hit?
--
Traa Dy Liooar

Jock


"Chip Pearson" wrote:

You can use the worksheet's Change event procedure to do this. Open
the Sheet's code module (right-click the worksheet tab and choose View
Code) and paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Res As VbMsgBoxResult
Const TEST_RANGE = "M1:M100" '<<< CHANGE TO CELLS TO TEST
If Application.Intersect(Target, Me.Range(TEST_RANGE)) Is Nothing
Then
Exit Sub
End If
Application.EnableEvents = False
If StrComp(Target.Text, "ch", vbTextCompare) = 0 Then
' user entered 'ch'
Res = MsgBox("Really?")
If Res = vbNo Then
Target.Value = vbNullString
Else
Call Charge
End If
Else
' user didn't enter 'ch'
End If
Application.EnableEvents = True
End Sub


Change the value of TEST_RANGE to the range of cells you wish to test.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Tue, 6 Jan 2009 05:04:01 -0800, Jock
wrote:

When "CH" is entered in a cell in column 'M', I'd like a text box to appear
with "Really?" and a yes no option.
If No is selected, to clear the cell in 'M' but to remain with focus.
If Yes is selected, to run code called Sub Charge( ) which is located in
Module 1.

Any ideas?

Thanks