Cannot enter text in textbox on form

[aabrazil1]

Hello All,

I cannot enter data in the text box circled below. The error message is near the end and is marked 'Error message here***. The Text box is named txtSearch.

Option Explicit
Sub Reset()
    Dim iRow As Long
    iRow = [Counta(Database!A:A)] 'identifying the last row'
    With frmform1
        .txtnumber.Value = ""
        .Txttitle.Value = ""
        .lbsystem.Clear  
          .lbsystem.AddItem "ACS"
        .lbsystem.AddItem "Archive Data"
         .lbsystem.AddItem "ATDS"
        .lbsystem.AddItem "Autocapture Testbed"
         .lbsystem.AddItem "AVN"
        .lbsystem.AddItem "C&DH"
         .lbsystem.AddItem "CBS"
        .lbsystem.AddItem "COMLIDAR"
         .lbsystem.AddItem "COMM"
        .lbsystem.AddItem "COMSEC"
         .lbsystem.AddItem "EGSE"
        .lbsystem.AddItem "EGSE (Flight I&T)"
         .lbsystem.AddItem "EPS"
        .lbsystem.AddItem "FlatSat"
         .lbsystem.AddItem "FSW"
        .lbsystem.AddItem "HFCS"
         .lbsystem.AddItem "L7 Mockups (Flight I&T)"
        .lbsystem.AddItem "Landsat 7"
         .lbsystem.AddItem "LIDAR"
        .lbsystem.AddItem "MECH"
         .lbsystem.AddItem "MGSE (Flight I&T)"
        .lbsystem.AddItem "PCC"
         .lbsystem.AddItem "PROP"
        .lbsystem.AddItem "PSU"
         .lbsystem.AddItem "PTS"
        .lbsystem.AddItem "RDT"
         .lbsystem.AddItem "REU"
        .lbsystem.AddItem "ROBOT"
         .lbsystem.AddItem "RPO"
        .lbsystem.AddItem "RPO Testbed"
         .lbsystem.AddItem "SC"
        .lbsystem.AddItem "SCTHRM"
         .lbsystem.AddItem "Servicing Payload (PYLD)"
        .lbsystem.AddItem "Serviving Testbed"
         .lbsystem.AddItem "Simulators"
        .lbsystem.AddItem "SP/SV/SC SPIDER GSE"
        .lbsystem.AddItem "Spave Vehicle Management"
        .lbsystem.AddItem "SPIDER"
         .lbsystem.AddItem "SPINT"
        .lbsystem.AddItem "STR"
         .lbsystem.AddItem "SVINT"
        .lbsystem.AddItem "Testbeds"
         .lbsystem.AddItem "THRM"
        .lbsystem.AddItem "TOOL"
        .lbsystem.AddItem "VDSU"
        .lbsystem.AddItem "VSS"
        .optyes.Value = False
        .optno.Value = False
        .lbdefectcode.Clear 
            .lbdefectcode.AddItem "10 - Solder Defect"
        .lbdefectcode.AddItem "20 - Contamination"
          .lbdefectcode.AddItem "30 - Shrink Tubing Missing"
        .lbdefectcode.AddItem "40 - Not Built to Specification/Drawing"
          .lbdefectcode.AddItem "50 - Dimensions Out of Tolerance"
        .lbdefectcode.AddItem "60 - Failed Test"
          .lbdefectcode.AddItem "70 - Accept"
        .lbdefectcode.AddItem "80 - Damaged"
          .lbdefectcode.AddItem "90 - Documentation Error"
        .lbexpected.Clear 
          .lbexpected.AddItem ".5"
        .lbexpected.AddItem "1"
        .lbexpected.AddItem "1.5"
        .lbexpected.AddItem "2"
           .lbexpected.AddItem "2.5"
        .lbexpected.AddItem "3"
        .lbexpected.AddItem "3.5"
        .lbexpected.AddItem "4"
           .lbexpected.AddItem "4.5"
        .lbexpected.AddItem "5"
        .lbexpected.AddItem "5.5"
        .lbexpected.AddItem "6"
           .lbexpected.AddItem "6.5"
        .lbexpected.AddItem "7"
        .lbexpected.AddItem "7.5"
        .lbexpected.AddItem "8"
        .lbexpected.AddItem "8.5"
        .lbexpected.AddItem "9"
        .lbexpected.AddItem "9.5"
        .lbexpected.AddItem "10"
        .lbexpected.AddItem "10.5"
        .lbexpected.AddItem "11"
        .lbexpected.AddItem "11.5"
        .lbexpected.AddItem "12"
        .lbactual.Clear     
         .lbactual.AddItem ".5"
        .lbactual.AddItem "1"
        .lbactual.AddItem "1.5"
        .lbactual.AddItem "2"
           .lbactual.AddItem "2.5"
        .lbactual.AddItem "3"
        .lbactual.AddItem "3.5"
        .lbactual.AddItem "4"
           .lbactual.AddItem "4.5"
        .lbactual.AddItem "5"
        .lbactual.AddItem "5.5"
        .lbactual.AddItem "6"
           .lbactual.AddItem "6.5"
        .lbactual.AddItem "7"
        .lbactual.AddItem "7.5"
        .lbactual.AddItem "8"
        .lbactual.AddItem "8.5"
        .lbactual.AddItem "9"
        .lbactual.AddItem "9.5"
        .lbactual.AddItem "10"
        .lbactual.AddItem "10.5"
        .lbactual.AddItem "11"
        .lbactual.AddItem "11.5"
        .lbactual.AddItem "12"
        .txtproblem.Value = ""
        .txtnotes.Value = ""
        .txtRowNumber.Value = ""
        'Below code are associated with Search Feature    
        Call Add_SearchColumn
        ThisWorkbook.Sheets("Database").AutoFilterMode = False
        ThisWorkbook.Sheets("SearchData").AutoFilterMode = False
        ThisWorkbook.Sheets("SearchData").Cells.Clear
        '-----------------------------------------------
        .lbdatabase.ColumnCount = 12
        .lbdatabase.ColumnHeads = True
        .lbdatabase.ColumnWidths = "40,70,55,55,20,20,40,40,40,40,40,40"
        If iRow > 1 Then
            .lbdatabase.RowSource = "Database!A2:L" & iRow
        Else    
            .lbdatabase.RowSource = "Database!A2:L2"
        End If
    End With
End Sub
Sub Submit()
    Dim Sh As Worksheet
    Dim iRow As Long
    Set Sh = ThisWorkbook.Sheets("Database")
    If frmform1.txtRowNumber.Value = " " Then
    iRow = [Counta(Database!A:A)] + 1
    Else
    iRow = frmform1.txtRowNumber.Value
    End If
    With Sh
        .Cells(iRow, 1) = iRow - 1
        .Cells(iRow, 2) = frmform1.txtnumber.Value
        .Cells(iRow, 3) = frmform1.Txttitle.Value
        .Cells(iRow, 4) = frmform1.lbsystem.Value
        .Cells(iRow, 5) = IIf(frmform1.optyes.Value = True, "Y", "N")
        .Cells(iRow, 6) = frmform1.lbdefectcode.Value
        .Cells(iRow, 7) = frmform1.lbexpected.Value
        .Cells(iRow, 8) = frmform1.lbactual.Value
        .Cells(iRow, 9) = frmform1.txtproblem.Value
        .Cells(iRow, 10) = frmform1.txtnotes.Value
        .Cells(iRow, 11) = Application.UserName
        .Cells(iRow, 12) = [Text(Now(), "MM-DD-YYYY HH:MM:SS")]
      End With
End Sub
Sub Show_Form()
    frmform1.Show
End Sub
Function Selected_List() As Long
    Dim i As Long
    Selected_List = 0
    For i = 0 To frmform1.lbdatabase.ListCount - 1
        If frmform1.lbdatabase.Selected(i) = True Then
            Selected_List = i + 1
            Exit For       
        End If       
    Next i
End Function
Sub Add_SearchColumn()
    frmform1.EnableEvents = False
    With frmform1.cmbSearchcolumn
        .Clear    
        .AddItem "All"
         .AddItem "WOA Num"
         .AddItem "WOA Title"
         .AddItem "System"
         .AddItem "PR"
         .AddItem "Defect"
         .AddItem "Expected"
         .AddItem "Actual"
         .AddItem "Prob Desc"
         .AddItem "Notes"
         .AddItem "QE"
         .AddItem "Date"
         .Value = "All"  
    End With  
        frmform1.EnableEvents = True
        frmform1.txtSearch.Value = ""
        frmform1.txtSearch.Enabled = False
        frmform1.cmdSearch = False
End Sub
Sub SearchData()
    Application.ScreenUpdating = False 
    Dim shDatabase As Worksheet 'Database sheet
    Dim shSearchData As Worksheet 'SearchData sheet 
    Dim iColumn As Integer 'To hold the selected column number in Database sheet
    Dim iDatabaseRow As Long 'To store the last non-blank row number available in Database
    Dim iSearchRow As Long 'To hold the last non-blank row number in SearchData Sheet
    Dim sColumn As String 'To store the column selection
    Dim sValue As String 'To hold the search text value 
    Set shDatabase = ThisWorkbook.Sheets("Database")
    Set shSearchData = ThisWorkbook.Sheets("SearchData")  
    iDatabaseRow = ThisWorkbook.Sheets("Database").Range("A" & Application.Rows.Count).End(xlUp).Row  
    sColumn = frmform1.cmbSearchcolumn.Value  
    sValue = frmform1.txtSearch.Value     'ERROR IS HERE************************************  
    iColumn = Application.WorksheetFunction.Match(sColumn, shDatabase.Range("A1:L1"), 0)
    'Remove filter from Database worksheet
    If shDatabase.AutoFilterMode = True Then  
       shDatabase.AutoFilterMode = False     
    End If  
    'Apply filter on Database worksheet modified  
    If sColumn = "Date" Then
        shDatabase.Range("A1:L" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:="=" & sValue
    Else
        shDatabase.Range("A1:L" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:="*" & sValue & "*"
    End If
    If Application.WorksheetFunction.Subtotal(3, shDatabase.Range("C:C")) >= 2 Then
        'Code to remove the previous data from SearchData Worksheet      
        shSearchData.Cells.Clear
        shDatabase.AutoFilter.Range.Copy shSearchData.Range("A1")
        Application.CutCopyMode = False
        iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row
        frmform1.lbdatabase.ColumnCount = 12
        frmform1.lbdatabase.ColumnWidths = "40,40,40,40,40,40,40,40,40,40,40,40"
     If iSearchRow > 1 Then
        frmform1.lbdatabase.RowSource = "SearchData!A2:L" & iSearchRow
        MsgBox "Records found."
     End If
    Else
        MsgBox "No record found."
    End If
        shDatabase.AutoFilterMode = False
        Application.ScreenUpdating = True 
End Sub

Thank you,
Tony

 

[Anthony47]

You mean that you are not allowed to type anything into that textbox?
Well, within the Sub Add_SearchColumn the textbox is disabled (frmform1.txtSearch.Enabled = False): is it enabled somewhere else before you need to use it?

 

[aabrazil1]

You mean that you are not allowed to type anything into that textbox?
Well, within the Sub Add_SearchColumn the textbox is disabled (frmform1.txtSearch.Enabled = False): is it enabled somewhere else before you need to use it?

Hello Anthony,

Yes, I can not click in the text box. 'txtSearch' is enabled in the form code. It is commented below 9th line down.

Option Explicit
Public EnableEvents As Boolean
Private Sub cmbSearchcolumn_Change()
    If Me.EnableEvents = False Then Exit Sub   
    If Me.cmbSearchcolumn.Value = "All" Then
        Call Reset
    Else
        Me.txtSearch.Value = ""
        Me.txtSearch.Enabled = True      'txtsearch is enabled here
        Me.cmdSearch.Enabled = True       
    End If
End Sub
Private Sub cmdDelete_Click()
  Dim iRow As Long
  If Selected_List = 0 Then
        MsgBox "No row is selected.", vbOKOnly + vbInformation, "Delete"
        Exit Sub
    End If
    Dim i As VbMsgBoxResult
    i = MsgBox("Do you want to delete the selected record?", vbYesNo + vbQuestion, "Confirmation")
    If i = vbNo Then Exit Sub
    iRow = Application.WorksheetFunction.Match(Me.lbdatabase.List(Me.lbdatabase.ListIndex, 0), _
    ThisWorkbook.Sheets("Database").Range("A:A"), 0)
    ThisWorkbook.Sheets("Database").Rows(iRow).Delete
    Call Reset
    MsgBox "Selected record has been deleted.", vbOKOnly + vbInformation, "Deleted"
End Sub
Private Sub cmdEdit_Click()
  If Selected_List = 0 Then
    MsgBox "No row is selected.", vbOKOnly + vbInformation, "Edit"
    Exit Sub
    End If
    'Code to update the value to respective controls
    Dim sPR As String
    Me.txtRowNumber.Value = Application.WorksheetFunction.Match(Me.lbdatabase.List(Me.lbdatabase.ListIndex, 0), _
    ThisWorkbook.Sheets("Database").Range("A:A"), 0)
    Me.txtnumber.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 1)
    Me.Txttitle.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 2)
    Me.lbsystem.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 3)    
    sPR = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 4)
    If sPR = "Y" Then
        Me.optyes.Value = True
    Else
        Me.optno.Value = True
    End If
    Me.lbdefectcode.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 5)
    Me.lbexpected.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 6)
    Me.lbactual.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 7)
    Me.txtproblem.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 8)
    Me.txtnotes.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 9)    
    MsgBox "Please make the required changes and click on 'Save' button to update.", vbOKOnly + vbInformation, "Edit"
End Sub
Private Sub cmdReset_Click()
    Dim msgValue As VbMsgBoxResult
   msgValue = MsgBox("Do you want to rerest the form?", vbYesNo + vbInformation, "Confirmation")
   If msgValue = vbNo Then Exit Sub
    Call Reset
End Sub
Private Sub cmdSave_Click()
   Dim msgValue As VbMsgBoxResult
   msgValue = MsgBox("Please check you entries and confirm you want to save the data", vbYesNo + vbInformation, "Confirmation")
   If msgValue = vbNo Then Exit Sub
    Call Submit
    Call Reset
End Sub
Private Sub cmdSearch_Click()
    If Me.txtSearch.Value = "" Then
        MsgBox "Please enter the search value.", vbOKOnly = vbInformation, "Search"
    End If
        Call SearchData     
End Sub
Private Sub Frame1_Click()
End Sub
Private Sub UserForm_Initialize()
    Call Reset
End Sub

Thank you,
Tony

 

[Anthony47]

I cannot check which is the Disable /Enable sequence, but if you are not allowed to type into the textbox it means it is disabled.
Try this:
-when you wish to type into the textbox and that is impossible open the vba, then open its "Immediate window" (type Contr-g; if the shortcut fails then Menu /Display /Immediate window)
-type the command ?frmform1.txtSearch.Enabled
-if the answer says "False" than you have to check why the enable instruction has been ignored

But I think it is unsafe declaring a Public variable within the userform code module (I mean Public EnableEvents As Boolean), as I believe its value is initialized when the userform starts.
So try declaring it on top of a standard vba module, then change the ways you refer to it (not frmform1.EnableEvents, nor Me.EnableEvents but simply EnableEvents. And, btw, I think it is unsafe naming a variable with the same name of a "property"; so call it myEnableEvent)

Try...