G
Garry Jones
I have 3 text boxes on a userform, all prefilled by suggested data from
cells.
Only the current textbox is enabled, after each entry the user tabs to
the next textbox, the one they were in becomes Enabled=False and the one
they tab to becomes Enabled=True. (This is done with keypress). I also
use color defaults for each state of the textbox. (enabled=true or
false, different colours).
The user has to enter a new value.
Example for textbox1, if user enters a value that already exists in
textbox2 or textbox3 it...
....identifies that a duplicate value has been found YES
....resets Textbox1 to the old existing value YES
...but it moves the cursor to Textbox2.
This is what I want to stop happening. I need code that will cancel the
exit when the user enters an existing value.
The full version has 18 textboxes, here is a version with three with the
same problem, any help appreciated, you will need a userform with 3
textboxes and one command button.
Option Explicit
Const c1 = &HC0E0FF 'creamish
Const c2 = &HC0& 'reddish
Const c3 = &H0& 'black
Const c4 = &HFF00& 'green
Dim cancelclose As Boolean
Dim bDisableEvents As Boolean
Private CloseMode As Integer
Public Function Chknow(tb As MSForms.TextBox) As Boolean
Dim boxnumhere As Integer
Dim boxnumfrm As Variant
Dim fubar As String
CloseMode = 0
boxnumhere = 1 'boxnumber start is 1
boxnumfrm = tb.Tag 'tb.tag is number of box
'this compares new input to existing input
'if input already exists it cancels the input
'and resets it to what it was and issues msgbox warning
Do While boxnumhere < 4
If boxnumfrm <> boxnumhere Then
fubar = EnterNames("TextBox" & boxnumhere).Text
If StrComp(tb.Value, fubar, vbTextCompare) = 0 Then
MsgBox "Name Duplicate"
cancelclose = False 'somewhere here
Chknow = True 'or here
CloseMode = 0 'or here there should be a way of trapping
the user in current textbox
Exit Function
Else
CloseMode = 1
End If
Else
End If
boxnumhere = boxnumhere + 1
Loop
Chknow = False
End Function
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = Chknow(TextBox1)
End Sub
Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = Chknow(TextBox2)
End Sub
Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = Chknow(TextBox3)
End Sub
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If bDisableEvents Then Exit Sub
Cancel = Chknow(TextBox1)
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If bDisableEvents Then Exit Sub
Cancel = Chknow(TextBox2)
End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If bDisableEvents Then Exit Sub
Cancel = Chknow(TextBox3)
End Sub
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 9: KeyAscii = 0: ntl 1
End Select
End Sub
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 9: KeyAscii = 0: ntl 2
End Select
End Sub
Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 9: KeyAscii = 0: ntl 3
End Select
End Sub
Private Sub UserForm_Initialize()
Dim i As Long
Me.CommandButton1.TabStop = True
With Me.TextBox1
.SetFocus
.Enabled = True
.TabStop = True
.TabKeyBehavior = True
.ForeColor = c3
.BackColor = c4
End With
For i = 2 To 3
With Me.Controls("textbox" & i)
.Enabled = False
.TabStop = False
.ForeColor = c1
.BackColor = c2
.TabKeyBehavior = True
End With
Next i
Me.TextBox1.SetFocus
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Beep
Cancel = True
End If
End Sub
Private Sub ntl(bnum As Integer)
With Me.Controls("textbox" & bnum)
If Trim(.Value) = "" Then
.SetFocus
Beep
Else
bDisableEvents = True 'needed to stop Exit triggering
.Enabled = False
bDisableEvents = False
.ForeColor = c1
.BackColor = c2
If bnum < 3 Then
With Me.Controls("textbox" & bnum + 1)
.Enabled = True
.ForeColor = c3
.BackColor = c4
.SetFocus
End With
Else
Me.CommandButton1.SetFocus
End If
End If
End With
End Sub
Garry Jones
Sweden
cells.
Only the current textbox is enabled, after each entry the user tabs to
the next textbox, the one they were in becomes Enabled=False and the one
they tab to becomes Enabled=True. (This is done with keypress). I also
use color defaults for each state of the textbox. (enabled=true or
false, different colours).
The user has to enter a new value.
Example for textbox1, if user enters a value that already exists in
textbox2 or textbox3 it...
....identifies that a duplicate value has been found YES
....resets Textbox1 to the old existing value YES
...but it moves the cursor to Textbox2.
This is what I want to stop happening. I need code that will cancel the
exit when the user enters an existing value.
The full version has 18 textboxes, here is a version with three with the
same problem, any help appreciated, you will need a userform with 3
textboxes and one command button.
Option Explicit
Const c1 = &HC0E0FF 'creamish
Const c2 = &HC0& 'reddish
Const c3 = &H0& 'black
Const c4 = &HFF00& 'green
Dim cancelclose As Boolean
Dim bDisableEvents As Boolean
Private CloseMode As Integer
Public Function Chknow(tb As MSForms.TextBox) As Boolean
Dim boxnumhere As Integer
Dim boxnumfrm As Variant
Dim fubar As String
CloseMode = 0
boxnumhere = 1 'boxnumber start is 1
boxnumfrm = tb.Tag 'tb.tag is number of box
'this compares new input to existing input
'if input already exists it cancels the input
'and resets it to what it was and issues msgbox warning
Do While boxnumhere < 4
If boxnumfrm <> boxnumhere Then
fubar = EnterNames("TextBox" & boxnumhere).Text
If StrComp(tb.Value, fubar, vbTextCompare) = 0 Then
MsgBox "Name Duplicate"
cancelclose = False 'somewhere here
Chknow = True 'or here
CloseMode = 0 'or here there should be a way of trapping
the user in current textbox
Exit Function
Else
CloseMode = 1
End If
Else
End If
boxnumhere = boxnumhere + 1
Loop
Chknow = False
End Function
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = Chknow(TextBox1)
End Sub
Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = Chknow(TextBox2)
End Sub
Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = Chknow(TextBox3)
End Sub
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If bDisableEvents Then Exit Sub
Cancel = Chknow(TextBox1)
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If bDisableEvents Then Exit Sub
Cancel = Chknow(TextBox2)
End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If bDisableEvents Then Exit Sub
Cancel = Chknow(TextBox3)
End Sub
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 9: KeyAscii = 0: ntl 1
End Select
End Sub
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 9: KeyAscii = 0: ntl 2
End Select
End Sub
Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 9: KeyAscii = 0: ntl 3
End Select
End Sub
Private Sub UserForm_Initialize()
Dim i As Long
Me.CommandButton1.TabStop = True
With Me.TextBox1
.SetFocus
.Enabled = True
.TabStop = True
.TabKeyBehavior = True
.ForeColor = c3
.BackColor = c4
End With
For i = 2 To 3
With Me.Controls("textbox" & i)
.Enabled = False
.TabStop = False
.ForeColor = c1
.BackColor = c2
.TabKeyBehavior = True
End With
Next i
Me.TextBox1.SetFocus
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Beep
Cancel = True
End If
End Sub
Private Sub ntl(bnum As Integer)
With Me.Controls("textbox" & bnum)
If Trim(.Value) = "" Then
.SetFocus
Beep
Else
bDisableEvents = True 'needed to stop Exit triggering
.Enabled = False
bDisableEvents = False
.ForeColor = c1
.BackColor = c2
If bnum < 3 Then
With Me.Controls("textbox" & bnum + 1)
.Enabled = True
.ForeColor = c3
.BackColor = c4
.SetFocus
End With
Else
Me.CommandButton1.SetFocus
End If
End If
End With
End Sub
Garry Jones
Sweden