Userform F14_Risk1 has a command button that changes the format to view a different source of data. Everything works as should except when I try to reset the form to its original state. The Select Case event works using the CommandButton3 Caption as choice, but try as I might I cannot get form to revert. In the first case argument why isn't TextBox1_Change event but is if I try later in code.
Basically I want the form to revert to the first Initialized state but ListBox1.Clear is throwing an error
Any pointers appreciated
Private Sub CommandButton3_Click()
Select Case F14_Risk1.CommandButton3.Caption
Case Is = "View Risk Assessments"
With F14_Risk1
.ListBox1.Clear
.TextBox1.Value = "Risk Assessments"
.ListBox1.RowSource = "RA"
.CommandButton1.Visible = False
.CommandButton2.Visible = False
.CommandButton3.Caption = "Return to Search"
End With
Case Is = "Return to Search"
'Call UserForm_Initialize
Unload Me
'With F14_Risk1
'.CommandButton3.Caption = "Risk Assessments"
'.CommandButton1.Visible = True
'.CommandButton2.Visible = True
'TextBox1.Value = ""
'End With
'F14_Risk1.TextBox1.Value = ""
'.Value = "Search Records Here"
'.SetFocus
'.SelStart = 0
'.SelLength = Len(.Text)
'End With
'F14_Risk1.ListBox1.Clear ''''''''THIS IS HIGHLIGHTED ON ERROR
'With R14_Risk1
'.CommandButton1.Visible = True
'.CommandButton2.Visible = True
'.CommandButton3.Caption = "Risk Assessments"
'End With
End Select
End Sub
Private Sub UserForm_Initialize()
With F14_Risk1.TextBox1
.Value = "Search Records Here"
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
End Sub
Private Sub TextBox1_Change()
Dim a, i As Long, w(), n As Long, strExclude As String
F14_Risk1.ListBox1.Clear ''''''''THIS IS HIGHLIGHTED ON ERROR
a = ThisWorkbook.Sheets("RISKS").Cells(1).CurrentRegion.Value
For i = 2 To UBound(a, 1)
If (UCase$(a(i, 2)) Like "*" & UCase$(F14_Risk1.TextBox1.Value) & "*" Or _
UCase$(a(i, 3)) Like "*" & UCase$(F14_Risk1.TextBox1.Value) & "*" Or _
UCase$(a(i, 4)) Like "*" & UCase$(F14_Risk1.TextBox1.Value) & "*") Then
n = n + 1
ReDim Preserve w(1 To UBound(a, 2), 1 To n)
For ii = 1 To UBound(a, 2)
w(ii, n) = a(i, ii)
Next
End If
Next
If n > 0 Then F14_Risk1.ListBox1.Column = w
End Sub
Bookmarks