Posts by Andyw111

Hi Jonathan,

That works perfectly many thanks.

Andy

Any code you can give me would be greatly appreciated

Hi Jonathan,

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.

Andy

Hi,
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.

Andy

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.

Hi Jonathan

I may be being dense but how does your code pick up the name in TextBox1?

Andy

Hi,

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.

Andy

Hi All,

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.

Andy

Hi Pike

You're a star that works perfectly.

Many Thanks.

Andy

Hi Pike,

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?

Andy

Hi Pike

The list is populated like this:

Private Sub UserForm_Initialize()








ListBox1.ColumnCount = 10
ListBox1.List = Sheets("HERS Data").Range("A2:J" & Sheets("HERS Data").Cells(Rows.Count, 1).End(xlUp).Row).value






End Sub

Thanks in advance

Andy

Hi Pike,

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.

Many Thanks

Andy

Hi

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

AndyW