That works perfectly many thanks.
That works perfectly many thanks.
Any code you can give me would be greatly appreciated
I'm stuck on how to code the checkboxes to check the box, if your code above matches the checkbox caption value?
I'm still fairly new to VBA.
I have a UserForm with 39 checkBoxes that relate to different assessments that employees may need.
I have a ListBox that imports the assessments currently held by the operative from the worksheet.
The assessments are in column 5 of the ListBox.
The name of the assessment matches the caption name of the CheckBoxes.
is there a way of writing code to automatically checking the CheckBoxes if the caption matches the assessment listed in column 5 of the ListBox?
Any help will be much appreciated.
I couldn't wait and had to try it tonight.
It works like a dream.
Many thanks for your help.
Many thanks will try it tomorrow.
I may be being dense but how does your code pick up the name in TextBox1?
I have a spreadsheet that has two worksheets "HERS Data" and "Archived Data".
HERS Data is the live data sheet containing training records of employee, there are multiple rows per employee due to number of assessments and certificates.
Column A contains an Op ID Number the same number is assigned to every row data for that employee
Column B is the employee name
Column K is contains a unique Record ID
I have a userform with TextBox1 employee Name and a command button cmdArchive
What I want to do is when I enter the employee's name in TextBox1 and click cmdArchive all rows of data relating to that employee are copied from HERS Data to Archived Data and then the source data in HERS Data is then deleted.
Can anyone help please been trying for days to find away to do this.
I am working on a user form using a ListBox called ListBox1 and TextBox called TextBox10 and a command button called cmdArchove
ListBox1 contains 11 columns of data which is sourced from sheet1 A2:K
The idea is to use TextBox10 to search ListBox1, then use multiselect to chose employee items to archive in sheet4 and deleting from source sheet1.
the data in column 11 is a unique reference number for the row of data.
Can anyone help with a code to enable this to happen?
Many thanks in advance.
You're a star that works perfectly.
The search now works perfectly thank you.
But I am now getting a run time error 70 permission denied on the line in red below when I select an item in listbox1
Private Sub ListBox1_Change() Dim j As Long, testString As String testString = "*" & TextBox10.text & "*" With Me.ListBox1 [COLOR=#FF0000] .List = Sheets("HERS Data").Range("A2:J" & Sheets("HERS Data").Cells(Rows.Count, 1).End(xlUp).Row).value[/COLOR] If .ListIndex = -1 And Len(TextBox10.text) Then For j = .ListCount - 1 To 0 Step -1 If (Not (LCase(.List(j, 0)) Like testString) And (Not (LCase(.List(j, 1)) Like testString))) _ And (Not (LCase(.List(j, 2)) Like testString) And (Not (LCase(.List(j, 3)) Like testString))) Then .RemoveItem j Next j End If End With End Sub
Do you have any ideas?
Thanks for the reply.
The typing in the ListBox doesn't work as it has 10 columns of data, which are all searchable from TextBox1 this all works fine and as I type it filters the ListBox results.
But if I hit backspace it clears the text in the TextBox but doesn't update the ListBox and I have to reset the form.
I really need a code where the backspace will continue updating the results in the ListBox.
I need some help with the code below, it searches the contents of the listbox as I type in the textbox and work fine. However if I miss type and use backspace nothing happens.
Can some one help me out so backspace works in the search?
private Sub TextBox10_Change() Dim j As Long Dim testString As String testString = LCase("*" & TextBox10.text & "*") With ListBox1 For j = .ListCount - 1 To 0 Step -1 If (Not (LCase(.List(j, 0)) Like testString) And (Not (LCase(.List(j, 1)) Like testString))) _ And (Not (LCase(.List(j, 2)) Like testString) And (Not (LCase(.List(j, 3)) Like testString))) Then .RemoveItem j End If Next j End With End Sub
Many thanks in Advance