Userform pops up unexpectedly

[PepperPots]

I have userform1 to search for a value in a list. If userform1 doesn't find amatch it calls userform2 to ask the user what they would like to do, Add, SearchAgain or Cancel. If the user selects "Add" (or CommandButton1) onuserform2, then the tab changes and the search value that was obtained inuserform1 is populated into B1 and then userform2 exits. If I try to use thearrow keys to change cells nothing happens and if I continue pressing the arrowkeys, userform2 will present itself again. If I use the mouse to click any cell, then everything is fine. It’s like it doesn’t actually exit the sub, eventhough I put that in my code. Any ideas?

 

[Norie]

What code are you using to show/hide/unload the userforms?

 

[PepperPots]

userform1.hide
userform2.show

 

[Norie]

Do you ever refer to userform1 or the controls on userform after you've hidden it?

 

[My Aswer Is This]

Show us all the code your using.

 

[PepperPots]

I do refer to the textbox value from the first userform.

Here is the code for AccessFormSearch (userform1)

Private Sub CommandButton1_Click()
AccessFormSearch.Hide

Dim sNewName As String
Dim lPosition As Long
Dim rEmpList As Range
Dim answer As String
Dim confirm As String
Dim i As Integer
Dim intValueToFind As String


Set rEmpList = Sheets("Index").Range("Name") 'adjust as required
Ask:
sNewName = TextBox1.Value 'InputBox("Enter the name of an employee")
If sNewName = "" Then Exit Sub 'GoTo Out
If sNewName <> "" Then GoTo Check
Check:
    intValueToFind = sNewName
    For i = 1 To 1500    ' Revise the 1500 to include all of your values
        If Cells(i, 1).Value = intValueToFind Then
            MsgBox ("This name already exists on row " & i)
            ActiveWindow.ScrollRow = i
            lPosition = Application.WorksheetFunction.Match(sNewName, rEmpList, 1)
            Range("A" & lPosition + 1).Select
            Unload Me
            Exit Sub
        End If
    Next i
    
PartialStringCheck:
    intValueToFind = sNewName
    Dim lngLastRow As Long
    Dim lngRow As Long
    Dim strValue As String
    Dim lngRowOutput As Long
    ' where does the data end
    lngLastRow = Sheets("Index").Range("Name").Rows.Count
    If lngLastRow = 1 Then Exit Sub ' no data

    lngRowOutput = 2
    For lngRow = 2 To lngLastRow
        strValue = Sheets("Index").Cells(lngRow, 1).Value
        If strValue = vbNullString Then
        GoTo Notfound
        Else
        If InStr(1, strValue, intValueToFind, vbTextCompare) > 0 Then
                        
            MsgBox ("This name may already exist on row " & lngRow)
            ActiveWindow.ScrollRow = lngRow
            Range("A" & lngRow).Select
            Unload Me
            Exit Sub
                
        End If
        End If
    Next lngRow
    
    Exit Sub
    
    
Notfound:
   
   AccessFormSearchNotFound.Show
End Sub
Private Sub CommandButton2_Click()
AccessFormSearch.Hide
Unload Me
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
CommandButton1_Click
End Sub
Private Sub UserForm_Activate()
AccessFormSearch.TextBox1.Value = ""
AccessFormSearch.TextBox1.SetFocus
End Sub
Private Sub UserForm_Initialize()
AccessFormSearch.TextBox1.SetFocus
With AccessFormSearch
  .StartUpPosition = 0
  .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
  .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)

End With
End Sub

Here is the code for AccessFormSearchNotFound (Userform2)

Private Sub CommandButton1_Click()
AccessFormSearchNotFound.Hide

Dim sNewName As String
Dim lPosition As Long
Dim rEmpList As Range
Dim answer As String
Dim confirm As String

Add:
Sheets("Access Form").Select
Range("b1").Value = AccessFormSearch.TextBox1.Value
Range("b2").Select

UnloadForms

End Sub
Private Sub CommandButton2_Click()
Unload AccessFormSearch
Unload AccessFormSearchNotFound
AccessFormSearch.Show


End Sub
Private Sub CommandButton3_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
With AccessFormSearchNotFound
  .StartUpPosition = 0
  .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
  .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
End With
End Sub

I've been trying some stuff since Norie brought up show/hide/unload so now I call a sub to unload the forms. It pops up twice telling me that the forms are unloaded

Sub UnloadForms()

    
        Dim i As Long, Str As String
    For i = VBA.UserForms.Count - 1 To 0 Step -1
        Str = Str & VBA.UserForms(i).Name & vbNewLine
        Unload VBA.UserForms(i)
        Next i
    MsgBox Str & "unloaded"
End Sub

 

[PepperPots]

I think I found the issue.

In AccessFormSearch I had the following code:

[COLOR=#333333]Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
[/COLOR]CommandButton1_Click 
[COLOR=#333333]End Sub
[/COLOR]

I removed it and replaced it with:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)       
             If KeyCode = vbKeyReturn Then
             CommandButton1_Click
             End If
End Sub

Everything seems to be working as I expected.

 

[My Aswer Is This]

I think I found the issue.

In AccessFormSearch I had the following code:

[COLOR=#333333]Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
[/COLOR]CommandButton1_Click 
[COLOR=#333333]End Sub
[/COLOR]

I removed it and replaced it with:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)       
             If KeyCode = vbKeyReturn Then
             CommandButton1_Click
             End If
End Sub

Everything seems to be working as I expected.

Glad to see you have worked it out.