Posts by blxck

blxck · Jan 8th 2017

Re: search criteria using combobox and textbox

thank you for your kind help. now the form work properly
its only a month since i started using vba so i have many errors and ask many questions. guess i still have a lot more to learn. thank you again

blxck · Jan 7th 2017

Re: search criteria using combobox and textbox

i cant figure out any solid reason behind this issue.. as you said my filter is working fine but filtered data wont be display in the listbox. and theres something i notice, when i filtered "Commune", data from "Commune" move up the listbox but the rest of data from other commune still exist below the filtered data. example.. if i search Bavel for Commune, all data for bavel move up to listbox but other data such as bakan, chrey,etc (which is part of commune as well) still exist on the listbox as well. but this only happen for Commune.. other search criteria did not show any respond. they just hide my actual data in excel sheet and i have to open Data tab and click filter icon so it will display again.

im using "Province","District","Commune","Client" for search criteria which is in column D,E,F and H..
my combobox value is also based on this arrangement "Province","District","Commune" and "Client"

blxck · Jan 6th 2017

Re: search criteria using combobox and textbox

by taking your advice into consideration, i make some changes where i deleted my rowsource and use manually additem data into the listbox instead.i use userform initialization to load my data listbox. but now, another problem arise where it does not give out any error but it does not filtered any data either.

blxck · Jan 3rd 2017

Re: search criteria using combobox and textbox

hai, sure.. attached is the workbook that i've been working on;

blxck · Jan 2nd 2017

Re: search criteria using combobox and textbox

hai, i have try the =worksheets("database").cells(cell.row,"C") but it doesnt work. and when i use ..
Worksheets("database").activate
Stop

it would stop debug when i choose column "M" and "H" and give out permission denied error on line Me.ListBox1.AddItem when i choose column "F". do you have any idea??

blxck · Dec 29th 2016

Hai,

i have a situation where my sv want me to create a search engine using combobox and textbox based on data in excel. it goes on like this, this search engine enable users to select which column that they want from the combobox and enter any related keywords in the textbox. commandbutton is used to display data related to that column in combobox and display it in the listbox. the problem is my button does not working. when i click on the commandbutton, it does not return any values.

Here is my code :

Code

Private Sub CommandButton5_Click()


    Dim s As Long
    Dim deg1 As String, deg2 As String
    Dim colIndex As String
    Dim cell As Range


    With Me
        If .TextBox1.Value = "" Then
            MsgBox "Please enter a value", vbExclamation
            .TextBox1.SetFocus
            Exit Sub
        End If


        If .ComboBox1.Value = "" Or .ComboBox1.Value = "-" Then
            MsgBox "Choose a filter field", vbExclamation
            .ComboBox1.SetFocus
            Exit Sub
        End If


        deg2 = .TextBox1.Value
        Select Case ComboBox1.Value
            Case "Commune"
                colIndex = "F"
            Case "Client"
                colIndex = "H"
            Case "Year"
                colIndex = "M"
        End Select
    End With


    If colIndex <> "" Then
        With Worksheets("DataBase")
            With .Range(.Cells(1, colIndex), .Cells(.Rows.Count, colIndex).End(xlUp))
                .AutoFilter field:=1, Criteria1:=UCase(deg2) & "*"
                If Application.WorksheetFunction.Subtotal(103, .Cells) > 1 Then
                    For Each cell In .Resize(.Rows.Count - 1, 1).Offset(1).SpecialCells(xlCellTypeVisible)
                        Me.ListBox1.AddItem
                        Me.ListBox1.List(s, 0) = Cells(cell.Row, "C")
                        Me.ListBox1.List(s, 1) = Cells(cell.Row, "D")
                        Me.ListBox1.List(s, 2) = Cells(cell.Row, "E")
                        Me.ListBox1.List(s, 3) = Cells(cell.Row, "F")
                        Me.ListBox1.List(s, 4) = Cells(cell.Row, "G")
                        Me.ListBox1.List(s, 5) = Cells(cell.Row, "H")
                        Me.ListBox1.List(s, 6) = Cells(cell.Row, "I")
                        Me.ListBox1.List(s, 7) = Cells(cell.Row, "J")
                        Me.ListBox1.List(s, 8) = Cells(cell.Row, "K")
                        Me.ListBox1.List(s, 9) = Cells(cell.Row, "L")
                        Me.ListBox1.List(s, 10) = Cells(cell.Row, "M")
                        Me.ListBox1.List(s, 11) = Cells(cell.Row, "N")
                        Me.ListBox1.List(s, 12) = Cells(cell.Row, "O")
                        Me.ListBox1.List(s, 13) = Cells(cell.Row, "P")
                        Me.ListBox1.List(s, 14) = Cells(cell.Row, "Q")
                        Me.ListBox1.List(s, 15) = Cells(cell.Row, "R")
                        Me.ListBox1.List(s, 16) = Cells(cell.Row, "S")
                        Me.ListBox1.List(s, 17) = Cells(cell.Row, "T")
                        Me.ListBox1.List(s, 18) = Cells(cell.Row, "U")
                        Me.ListBox1.List(s, 19) = Cells(cell.Row, "V")
                        Me.ListBox1.List(s, 20) = Cells(cell.Row, "W")
                        Me.ListBox1.List(s, 21) = Cells(cell.Row, "X")
                        Me.ListBox1.List(s, 22) = Cells(cell.Row, "Y")
                        Me.ListBox1.List(s, 23) = Cells(cell.Row, "Z")
                        Me.ListBox1.List(s, 24) = Cells(cell.Row, "AA")
                        Me.ListBox1.List(s, 25) = Cells(cell.Row, "AB")
                        Me.ListBox1.List(s, 26) = Cells(cell.Row, "AC")
                        s = s + 1
                     Next cell
                End If
        End With
    End With
End If




End Sub

Display More

blxck · Dec 28th 2016

Re: could not set list property,invalid property value

thanks, now it works properly.. thank you for your help

blxck · Dec 28th 2016

Re: could not set list property,invalid property value

tried that before but it turned out to be, the search field will be from cell "C" not from cell "M"
cell c is the user ID
while cell M is the year

example:
when i enter "1968" it will not display any data as in the data not exist(i only have 200++ data) ..but when i enter "19", all ID that consist 19 will display on listbox (example: 19,190,191,,,,)

blxck · Dec 28th 2016

Re: could not set list property,invalid property value

hai, sorry for late reply.. i have been coping with work and study these days. i tried your code and it works. but i have a problem where it only display data from cells "M" to "AC" when my data actually start from cells "C". can you tell me why?? Thank you

blxck · Dec 26th 2016

hai,

i am having a trouble with my application. I have a textbox, commandbutton and 27 column count. i want to search for "YEAR" which is in cell M2:M on excel and display all data regarding that year in listbox on my userform. i keep on getting error message of could not set list property,invalid property value on line ListBox1.List(liste, 10) = isim.Offset(0, 10). can someone tell me what is wrong with the code??

Code

Private Sub CommandButton2_Click()


Application.ScreenUpdating = False
ListBox1.RowSource = Empty
ListBox1.Clear
ListBox1.ColumnCount = 27


For Each isim In Range("M2:M" & Range("C10000").End(xlUp).Row)
    If UCase(LCase(isim)) Like UCase(LCase(TextBox1)) & "*" Then
        liste = ListBox1.ListCount
            ListBox1.AddItem
            ListBox1.List(liste, 0) = isim
            ListBox1.List(liste, 1) = isim.Offset(0, 1)
            ListBox1.List(liste, 2) = isim.Offset(0, 2)
            ListBox1.List(liste, 3) = isim.Offset(0, 3)
            ListBox1.List(liste, 4) = isim.Offset(0, 4)
            ListBox1.List(liste, 5) = isim.Offset(0, 5)
            ListBox1.List(liste, 6) = isim.Offset(0, 6)
            ListBox1.List(liste, 7) = isim.Offset(0, 7)
            ListBox1.List(liste, 8) = isim.Offset(0, 8)
            ListBox1.List(liste, 9) = isim.Offset(0, 9)
            ListBox1.List(liste, 10) = isim.Offset(0, 10)
            ListBox1.List(liste, 11) = isim.Offset(0, 11)
            ListBox1.List(liste, 12) = isim.Offset(0, 12)
            ListBox1.List(liste, 13) = isim.Offset(0, 13)
            ListBox1.List(liste, 14) = isim.Offset(0, 14)
            ListBox1.List(liste, 15) = isim.Offset(0, 15)
            ListBox1.List(liste, 16) = isim.Offset(0, 16)
            ListBox1.List(liste, 17) = isim.Offset(0, 17)
            ListBox1.List(liste, 18) = isim.Offset(0, 18)
            ListBox1.List(liste, 19) = isim.Offset(0, 19)
            ListBox1.List(liste, 20) = isim.Offset(0, 20)
            ListBox1.List(liste, 21) = isim.Offset(0, 21)
            ListBox1.List(liste, 22) = isim.Offset(0, 22)
            ListBox1.List(liste, 23) = isim.Offset(0, 23)
            ListBox1.List(liste, 24) = isim.Offset(0, 24)
            ListBox1.List(liste, 25) = isim.Offset(0, 25)
            ListBox1.List(liste, 26) = isim.Offset(0, 26)
                       
     End If
Next
Application.ScreenUpdating = True


End Sub

Display More

blxck · Dec 12th 2016

hai, im trying to fill up textbox and combobox from selected index in listbox. but when i click the button , it display the Could not get list property. invalid argument error. here is my code. highlight error is at txtYOB.value

Code

If ListBox1.ListIndex <> -1 Then
        With ListBox1
        txtID.Value = .List(.ListIndex, 0)
        cmbProvince.Value = .List(.ListIndex, 1)
        cmbDistrict.Value = .List(.ListIndex, 2)
        cmbCommune.Value = .List(.ListIndex, 3)
        cmbVillage.Value = .List(.ListIndex, 4)
        txtClient.Value = .List(.ListIndex, 5)
        txtClientID.Value = .List(.ListIndex, 6)
        txtDate.Value = .List(.ListIndex, 7)
        cmbSex.Value = .List(.ListIndex, 8)
        txtDOB.Value = .List(.ListIndex, 9)
        txtYOB.Value = .List(.ListIndex, 10)
        cmbClientType.Value = .List(.ListIndex, 11)
        txtClientDS.Value = .List(.ListIndex, 12)
        cmbClientStatus.Value = .List(.ListIndex, 13)
        cmbBusinessType.Value = .List(.ListIndex, 14)
        cmbClientCategory.Value = .List(.ListIndex, 15)
        cmbGenderedHHType.Value = .List(.ListIndex, 16)
        txtAdultF.Value = .List(.ListIndex, 17)
        txtAdultM.Value = .List(.ListIndex, 18)
        txtChildF.Value = .List(.ListIndex, 19)
        txtChildM.Value = .List(.ListIndex, 20)
        txtHeadName.Value = .List(.ListIndex, 21)
        txtBeneficiary.Value = .List(.ListIndex, 22)
        txtLatitude.Value = .List(.ListIndex, 23)
        txtLongitude.Value = .List(.ListIndex, 24)
        txtAlt.Value = .List(.ListIndex, 25)
        txtPhone.Value = .List(.ListIndex, 26)
            
        End With
        Else
       MsgBox " Any listbox item isn't selected !", vbCritical, ""
    End If

Display More

blxck · Dec 8th 2016

Re: search using textbox and display on list box in userform

ahh another one, i have to do update button for my form. how can i choose data from list box and update it using textbox and combobox?

blxck · Dec 8th 2016

Re: search using textbox and display on list box in userform

thank you for your help. i've got the solution. you are right its because of my dynamic names range.. btw thank you for your help

blxck · Dec 6th 2016

Re: search using textbox and display on list box in userform

no, it start with column D with row 1 being the header row and data starting in cell D2.

and yes, i have changed my dynamic named range to ("myData")

Code

Private Sub txtSearch_Change()
Dim x, i As Long, ii As Long, iii As Integer
    x = [myData]
    
    With lstAnalysis
        If txtSearch = "" Then
            .RowSource = "myData"
        Else
            .RowSource = ""
            For i = 1 To UBound(x, 1)
                If LCase(x(i, 5)) Like LCase(txtSearch) & "*" Then
                    For ii = 1 To 5
                        .AddItem
                        .List(iii, ii - 1) = x(i, ii)
                    Next
                    iii = iii + 1
                End If
            Next
        End If
    End With
    
End Sub

Display More

blxck · Dec 5th 2016

Re: search using textbox and display on list box in userform

i have tried that sample code on my database but nothing come out on the userform. i cant search the name and full database is not loaded in the listbox.

blxck · Dec 5th 2016

Re: search using textbox and display on list box in userform

and 1 more thing, database that i worked on now consist of hundreds of data. will it display all the data once we open the userform.?

blxck · Dec 5th 2016

Re: search using textbox and display on list box in userform

yeah i worked perfectly like what i want. but can you explain to me why did you changed the dynamic range "DataList" again. im still new here and im still learning. thank you. anyway sorry for the late reply

blxck · Dec 1st 2016

Re: search using textbox and display on list box in userform

since i cant show you the database that im working on, this is the example exactly like what i have

blxck · Dec 1st 2016

I have a user form with a listbox, textbox, and command button. The intent is for a user to type into the textbox, click the button, and the code would search through the database for the text. database consist of 5 column like below;
1. client
2. province
3.district
4.commune
5. village

im trying to search for the client name example "sorn" or "sorn tye" than it will display all the related information regarding that name in a listbox. for now, the listbox will display all the info in the database not the one that i search in the textbox.

Below is some code I've been trying to change to work. Not sure if this is a step in the right direction.

Code

Private Sub cmdAnalysis_Click()
'dim the variables
Dim FindMe As Range
Dim DataSH As Worksheet
'error handler
On Error GoTo errHandler:
'set object variables
Set DataSH = DataBase
'hold in memory and stop screen flicker
Application.ScreenUpdating = False
'//////////////////////////////////////////
'find the value in the column
Columns("D:H").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "AG5:AG6"), CopyToRange:=Range("AI5:AM5"), Unique:=False
        
Set searchme = Range("AG6")
    Set FindMe = Range("D2:H1572").Find(What:=searchme, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        FindMe.Select
        


'/////////////////////////////////////////
'unprotect all sheets
'Unprotect_All
'filter the data
Columns("D:H").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("AG5:AG6"), CopyToRange:=Range("AI5:AM5"), _
Unique:=False
'add the dynamic data to the listbox
lstAnalysis.RowSource = DataSH.Range("DataList").Address(external:=True)
'protect all sheets
'Protect_All
'error handler
On Error GoTo 0
Exit Sub
errHandler:
'Protect all sheets
'Protect_All
'if error occurs then show me exactly where the error occurs
MsgBox "No match found for " & txtSearch.Text
'clear the listbox if no match is found
Me.lstAnalysis.RowSource = ""
Exit Sub
End Sub

Display More