setFocus back to incorrect entry (1 Viewer)

Help please

I have created a form in Excel with text entry boxes, in the first box the text required is either A,B or C.
If an incorrect letter is entered I want to focus to go back to this cell. But I can t seem to manage this.

The code I have is...

Private Sub txt_ABC_Exit(ByVal Cancel As MSForms.ReturnBoolean)

txt_ABC.Text = UCase(txt_display.Text)

If txt_ABC.Text Like "[A,B,C]" <> False Then
txt_NEXT.SetFocus
Else
txt_ABC.SetFocus
lbl_error.Caption = "Error, incorrect entry"
'txt_ABC.SelStart = 0 ' tried this - didnt work!!
End If
End Sub

I can get focus on any other text box but the one I want!
Any ideas greatfully received.

The exit event is the wrong event to use for any edit code. You need to edit data BEFORE it is saved. If the edit is related to only a single field, the correct place is in that field's BeforeUpdate event. If the edit involves multiple fields or checks for null fields, the edit belongs in the form's BeforeUpdate event.


You can't change the data in the BeforeUpdate event so I added the UCase() to the AfterUpdate event.

I wouldn't do this at all. I would use a combo with the acceptable values set to LimitToList and let Access do my coding for me. If you don't let the user put anything invalid into the field, you don't need to worry about editing it. The valid values should be specified at the table level rather than the form level so that Jet actually does the editing. If the list is longer or you want to allow entries to be added by the user, it is best to use a table to hold the valid values. You can then create a relationship and use Referential Integrity to validate your data.

Thanks

I'll try that, unfortunately I can't use combo boxes as Im trying to emulate an existing screen which has been written in MSBASIC/DOS6 or something as ancient!!

But if it is "bad" why recreate it? Aren't you taking "emulate" too literally? I'm sure your form doesn't actually look like a DOS form so why shouldn't you use combos where appropriate?

solotoo said:

Help please

I have created a form in Excel ....

Click to expand...

Also in the wrong forum


.

Oldsoftboss said:

Also in the wrong forum
.

Click to expand...

I thought as this was a VBA question it should go in the VBA thread, not the specific Excel Thread.

I seem to have skipped right over that Excel business

solotoo,
Although many people here are multi-lingual, so to speak, the object model in Excel is sufficiently different that people familiar with Access VBA may not be able to help. This question might get a better response in the Excel forum. I'm not going to move it because someone may be able to help and I'm guessing that most members don't even read the Excel forum so if you make a "duplicate" post there, no one will care or comment

solotoo said:

Help please

I have created a form in Excel with text entry boxes, in the first box the text required is either A,B or C.
If an incorrect letter is entered I want to focus to go back to this cell. But I can t seem to manage this.

The code I have is...

Private Sub txt_ABC_Exit(ByVal Cancel As MSForms.ReturnBoolean)

txt_ABC.Text = UCase(txt_display.Text)

If txt_ABC.Text Like "[A,B,C]" <> False Then
txt_NEXT.SetFocus
Else
txt_ABC.SetFocus
lbl_error.Caption = "Error, incorrect entry"
'txt_ABC.SelStart = 0 ' tried this - didnt work!!
End If
End Sub

I can get focus on any other text box but the one I want!
Any ideas greatfully received.

Click to expand...

The proper event to use would be the Worksheet_Change() for that Excel worksheet. But I do not recommend that method.

The easiest way to restrict what the users enters would be to use "Validation" for the needed cells. Select the cells you want to validate and click the menu bar options in Excel; Data / Validation... and choose these settings... Allow: = List, Source: = A,B,C, Check the "In-Cell Dropdown" option in the Settins tab. Customize your message in the Input Message and Error Alert tabs. Play around with the settings until you get the desired resilts.