Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in VBA code
Hi all,
I've been using the code below to enter a set of numbers & alpha characters in the first part of set. Has been working near perfect until I noticed I can tab out of textbox after entering one character. You should not be able to exit until the first two characters in set have been entered. My question where do I place the code to check the first 2 characters in the set. Here the code Private Sub txtInvClaim_Change() Dim i As Long Dim s As String Dim ba() As Byte ' byte-array Static bExit As Boolean If Not bExit Then s = UCase(Me.txtInvClaim.Text) ba = s s = "" For i = 0 To UBound(ba) Step 2 If ba(i + 1) = 0 Then Select Case ba(i) Case 48 To 57 ' digits 0-9 s = s & Chr(ba(i)) Case 65 To 90 'letters only in 1st 2 characters If i <= 3 Then s = s & Chr(ba(i)) End If End Select End If Next If Len(s) 1 Then s = Left$(s, 2) & "/" & Right$("00000" & Mid$(s, 3, Len(s) - 2), 5) End If If txtInvClaim.Text < s Then bExit = True txtInvClaim.Text = s End If End If bExit = False End Sub Private Sub txtInvClaim_Exit(ByVal Cancel As MSForms.ReturnBoolean) If Not OkaytoExit Then If Me.txtInvClaim.Value = "" Then Cancel = True MsgboxResult = MsgBox("Please enter RiskCover Claim No", vbExclamation, "Shared Services") Me.txtInvClaim.SetFocus Exit Sub End If End If End Sub Private Sub txtInvClaim_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) OkaytoExit = False End Sub Private Sub txtInvClaim_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) With txtInvClaim LastPosition = .SelStart End With End Sub Private Sub txtInvClaim_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, _ ByVal x As Single, ByVal Y As Single) With txtInvClaim LastPosition = .SelStart End With End Sub Many thanks for the help in advance ViViC -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200910/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in VBA code
Hi ViVic
In the 'exit sub' you have to check if two characteres has been entered: Private Sub txtInvClaim_Exit(ByVal Cancel As MSForms.ReturnBoolean) If Not OkaytoExit Then If Me.txtInvClaim.Value = "" Or Len(Me.txtInvClaim) < 2 Then Cancel = True MsgboxResult = MsgBox("Please enter RiskCover Claim No", vbExclamation, "Shared Services") Me.txtInvClaim.SetFocus Exit Sub End If End If End Sub Regards, Per "ViViC via OfficeKB.com" <u39049@uwe skrev i meddelelsen news:9d90f2fa31e6f@uwe... Hi all, I've been using the code below to enter a set of numbers & alpha characters in the first part of set. Has been working near perfect until I noticed I can tab out of textbox after entering one character. You should not be able to exit until the first two characters in set have been entered. My question where do I place the code to check the first 2 characters in the set. Here the code Private Sub txtInvClaim_Change() Dim i As Long Dim s As String Dim ba() As Byte ' byte-array Static bExit As Boolean If Not bExit Then s = UCase(Me.txtInvClaim.Text) ba = s s = "" For i = 0 To UBound(ba) Step 2 If ba(i + 1) = 0 Then Select Case ba(i) Case 48 To 57 ' digits 0-9 s = s & Chr(ba(i)) Case 65 To 90 'letters only in 1st 2 characters If i <= 3 Then s = s & Chr(ba(i)) End If End Select End If Next If Len(s) 1 Then s = Left$(s, 2) & "/" & Right$("00000" & Mid$(s, 3, Len(s) - 2), 5) End If If txtInvClaim.Text < s Then bExit = True txtInvClaim.Text = s End If End If bExit = False End Sub Private Sub txtInvClaim_Exit(ByVal Cancel As MSForms.ReturnBoolean) If Not OkaytoExit Then If Me.txtInvClaim.Value = "" Then Cancel = True MsgboxResult = MsgBox("Please enter RiskCover Claim No", vbExclamation, "Shared Services") Me.txtInvClaim.SetFocus Exit Sub End If End If End Sub Private Sub txtInvClaim_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) OkaytoExit = False End Sub Private Sub txtInvClaim_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) With txtInvClaim LastPosition = .SelStart End With End Sub Private Sub txtInvClaim_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, _ ByVal x As Single, ByVal Y As Single) With txtInvClaim LastPosition = .SelStart End With End Sub Many thanks for the help in advance ViViC -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200910/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in VBA code
Thanks Per Jessen,
I missed the Len part of the statement I was using Me.txtInvClaim <2 but I could still tab when entering aplha char. It's been a hard day at the office Many thanks again for your help ViViC Per Jessen wrote: Hi ViVic In the 'exit sub' you have to check if two characteres has been entered: Private Sub txtInvClaim_Exit(ByVal Cancel As MSForms.ReturnBoolean) If Not OkaytoExit Then If Me.txtInvClaim.Value = "" Or Len(Me.txtInvClaim) < 2 Then Cancel = True MsgboxResult = MsgBox("Please enter RiskCover Claim No", vbExclamation, "Shared Services") Me.txtInvClaim.SetFocus Exit Sub End If End If End Sub Regards, Per Hi all, [quoted text clipped - 76 lines] ViViC -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with code (error code 9) | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Error in Excel VBA Code (Error 91) | Excel Programming | |||
How can I still go to the error-code after a On Error Goto? | Excel Programming | |||
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming |