Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel Exit on duplicate
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get userform Cancel button to invoke Exit Sub in calling macro? | New Users to Excel | |||
Workbook_BeforeClose(Cancel As Boolean) - Cancel won't work | Setting up and Configuration of Excel | |||
Cancel/Exit Sub | Excel Discussion (Misc queries) | |||
Exit Code Upon Cancel On Type 8 Input Box | Excel Discussion (Misc queries) | |||
InputBox / VBA question ok = print, cancel = exit | Excel Worksheet Functions |