Posts by XLacs20

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

Filter specific column in listbox

Hi Good People, Hoping someone could actually me on this problem. So basically, I have a textbox and a Listbox thats filter via textbox value. However, I…

www.mrexcel.com

Hoping someone could actually me on this problem. So basically, I have a textbox and a Listbox thats filter via textbox value. However, I wanted to show only a specific column of my listbox.



Below code filters my listbox.

My search starts in a2, if textbox value match the value in column A of my Sheet, then it shows all the items in listbox based on textbox value.



What I wanted to achieve is to show only lets say column B and column C only..



My textbox value will search in column A and display column B and C data to the listbox related to column A.



Is that possible? Thank you

Private Sub TextBox1_Change()
Dim a, i As Long, ii As Long, n As Long, temp As String
If Len(Me.TextBox1.Value) Then
    temp = UCase(Me.TextBox1.Value)
    With Sheets("sheet1")
        a = .Range("a2", .Range("a" & Rows.Count).End(xlUp)) _
        .Resize(, 2).Value
    End With
    a = Application.Transpose(a)
    For i = 1 To UBound(a, 2)
        If UCase(a(1, i)) Like temp & "*" Or _
            UCase(a(2, i)) Like temp & "*" Then
            n = n + 1
            For ii = 1 To UBound(a, 1)
                a(ii, n) = a(ii, i)
            Next
        End If
    Next
    If n > 0 Then
        ReDim Preserve a(1 To UBound(a, 1), 1 To n)
        Me.ListBox1.Column = a
    End If
End If
End Sub

Hi,

Please see attached file

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

Hello Everyone,

I am kinda new to this VBA programming so I am wondering if someone could help me with this simple problem.

So I have a time and motion study tracker with start, end and submit button button. I managed to worked on start and end.

But I am struggling to submit an specific data to my masterfile. In my workbook. I have a dropdown selection of status.. If the value of the dropdown is Pending, then those cases should be submitted in my Archive workbook when I hit the Submit button and the status in my last column will be updated to Submitted. So it will exclude those cases on my next submission and should not overlap the data in master file.

Below is my screenshot of source and masterfile and also my codes.

Thank you in Advance!

Sub Submit()

Dim STunsent As String
Dim OA As Object
Dim msg As Object
Dim sh As Worksheet


Dim Outapp As Object, Logfile As String

Dim r As Long, lr As Long, Archive As Workbook


sh = ThisWorkbook.Sheets("Prod")

Dim x As Integer
Dim lastrow As Integer

lastrow = Application.WorksheetFunction.CountBlank(sh.Range("D:D"))

For x = 2 To lastrow

If sh.Range("O" & i).Value <> "Submitted" And sh.Range("J" & i).Value = "Pending" Then

Set Archive = Workbooks.Open("C:\Users\ChrisLacs\Desktop\Test\Archive.xlsm")
r = 2

lr = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(r, 1), Cells(r, 3)).Copy
Active.Paste Destination:=Archive.Worksheets("Master").Rows(erow)

sh.Range("O" & i).Value = "Submitted"


End If

r = r + 1


Archive.Close

Next

End Sub