Posts by rich37

rich37 · Apr 8th 2017

Re: Code search when select Option button

[/QUOTE]
pike Thank for Very much.when i run this code Compile error:invalid or unqualified reference. line ".RowSource = "_Data"" i Remove the dots (.) Code working for me
[ATTACH=CONFIG]72120[/ATTACH]

rich37 · Apr 8th 2017

Re: Code search when select Option button

I try this code i don't know this correct or incorrect

Code

Private Sub TextBox6_Change() 
 Dim x, i As Long, ii As Long, iii As Integer
    x = [_Data]
    With ListBox1
        If TextBox6 = "" Then
            .RowSource = "_Data"
        Else
            .RowSource = ""
            For i = 1 To UBound(x, 1)
                If LCase(x(i, 1)) Like LCase(TextBox6) & "*" Then
                    For ii = 1 To 9
                        .AddItem
                        .List(iii, ii - 1) = x(i, ii)
                    Next
                    iii = iii + 1
                End If
            Next
        End If
    End With
End Sub
Private Sub OptionButton1_Click()
     ' Select OptionButton1
     'Private Sub TextBox6_Change() 'Code 1
    Dim x, i As Long, ii As Long, iii As Integer
    x = [_Data]
    With ListBox1
        If TextBox6 = "" Then
            .RowSource = "_Data"
        Else
            .RowSource = ""
            For i = 1 To UBound(x, 1)
                If LCase(x(i, 1)) Like LCase(TextBox6) & "*" Then
                    For ii = 1 To 9
                        .AddItem
                        .List(iii, ii - 1) = x(i, ii)
                    Next
                    iii = iii + 1
                End If
            Next
        End If
    End With
End Sub
Private Sub OptionButton2_Click()
     ' Select OptionButton2
     'Private Sub TextBox6_Change() ' Code2
    Dim x, i As Long, ii As Long, iii As Integer
    x = [_Data]
    With ListBox1
        If TextBox6 = "" Then
            .RowSource = "_Data"
        Else
            .RowSource = ""
            For i = 1 To UBound(x, 1)
                If LCase(x(i, 4)) Like LCase(TextBox6) & "*" Then
                    For ii = 1 To 9
                        .AddItem
                        .List(iii, ii - 1) = x(i, ii)
                    Next
                    iii = iii + 1
                End If
            Next
        End If
    End With
End Sub

Display More

[ATTACH=CONFIG]72117[/ATTACH]

rich37 · Apr 8th 2017

Re: Code search when select Option button

Quote from pike;790017
Hello you can change the control that initiates the code
Code
Private Sub OptionButton1_Click()
' Select OptionButton1
'Private Sub TextBox6_Change() 'Code 1
Code
Private Sub OptionButton2_Click()
' Select OptionButton1
'Private Sub TextBox6_Change() 'Code 2

Hello Pike Thank you I Try that it not work

rich37 · Apr 8th 2017

Re: Code search when select Option button

posted at http://www.ozgrid.com/forum/sh…age=2&p=789863#post789863

rich37 · Apr 8th 2017

Re: Code search when select Option button

[ATTACH=CONFIG]72112[/ATTACH]

rich37 · Apr 7th 2017

I'm having trouble with OptionButtons (the code was working) I don't know how to fix it. I want to search data from textbox with OptionButton. I want to select OptionButton1 for Code1 and OptionButton2 for Code2. Can someone Edit it Help Me Please.

Code

' Select OptionButton1
Private Sub TextBox6_Change() 'Code 1
   Dim x, i As Long, ii As Long, iii As Integer
    x = [_Data]
    With ListBox1
        If TextBox6 = "" Then
            .RowSource = "_Data"
        Else
            .RowSource = ""
            For i = 1 To UBound(x, 1)
                If LCase(x(i, 1)) Like LCase(TextBox6) & "*" Then
                    For ii = 1 To 9
                        .AddItem
                        .List(iii, ii - 1) = x(i, ii)
                    Next
                    iii = iii + 1
                End If
            Next
        End If
    End With
End Sub


' Select OptionButton2
Private Sub TextBox6_Change() ' Code2
   Dim x, i As Long, ii As Long, iii As Integer
    x = [_Data]
    With ListBox1
        If TextBox6 = "" Then
            .RowSource = "_Data"
        Else
            .RowSource = ""
            For i = 1 To UBound(x, 1)
                If LCase(x(i, 4)) Like LCase(TextBox6) & "*" Then
                    For ii = 1 To 9
                        .AddItem
                        .List(iii, ii - 1) = x(i, ii)
                    Next
                    iii = iii + 1
                End If
            Next
        End If
    End With
End Sub

Display More

rich37 · Apr 6th 2017

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

Quote from KjBox;789910

Do you mean option buttons on a user form or on a worksheet?

KjBoxthank you for reply me answer 's on userform.

[ATTACH=CONFIG]72090[/ATTACH]

rich37 · Apr 6th 2017

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

Quote from blxck;781883

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

Great it's work for me :thumbcoo: how to edit this code when i choose optionbutton 1 to select this code or optionbutton 2 to select another code

rich37 · Apr 1st 2017

[ATTACH=CONFIG]72026[/ATTACH]
Hi
i am having a trouble with my application.i keep on getting error message of could not set list property,invalid property Rowsource ListBox1.RowSource = "_ListMatch" can someone tell me what is wrong with the code??

Code

Private Sub TextBox1_Change()
    Sheets("Database").Range("K1").Value = TextBox1.Value
    ListBox1.RowSource = "_ListMatch"
End Sub




Private Sub TextBox7_AfterUpdate()
TextBox7.Value = Format(TextBox7.Value, "d mmmm yyyy")
End Sub
Private Sub UserForm_Activate()
   ActiveWindow.WindowState = xlMaximized
End Sub
Private Sub ComboBox1_Change()
    Select Case ComboBox1.ListIndex
        Case 0
        a = "Person!_Type"
        ComboBox1.RowSource = a
        Case 1
        b = "Person!_Person"
        ComboBox2.RowSource = b
        Case 2
        C = "Person!_Worktype"
        ComboBox3.RowSource = C
        Case 4
        D = "Person!_Niti"
        ComboBox4.RowSource = D
    End Select
End Sub




Private Sub CommandButton1_Click() 'search
On Error Resume Next
Dim ws As Worksheet
    Set ws = Worksheets("Database")
Dim myRange As Range
    Set myRange = Worksheets("Database").Range("_Data")
    If Trim(Me.TextBox1.Value) = "" Then
        Me.TextBox1.SetFocus
        MsgBox "¤Ø³ÂÑ§äÁèä´éãÊè¢éÍÁÙÅ", vbInformation, "ÃÐºº¤é¹ËÒÊÓ¹Ç¹"
        Exit Sub
    ElseIf Application.CountIf(ws.Range("_Irow"), Trim(Me.TextBox1.Value)) = 0 Then
        Me.TextBox1.SetFocus
        MsgBox "äÁè¾º¢éÍÁÙÅ¤´Õ " & Me.TextBox1.Value, vbInformation, "ÃÐºº¤é¹ËÒÊÓ¹Ç¹"
        Exit Sub
    Else
        TextBox2.Value = _
        Application.WorksheetFunction.VLookup(TextBox1.Value, myRange, 2, False)
        TextBox3.Value = _
        Application.WorksheetFunction.VLookup(TextBox1.Value, myRange, 3, False)
        TextBox4.Value = _
        Application.WorksheetFunction.VLookup(TextBox1.Value, myRange, 4, False)
        ComboBox3.Value = _
        Application.WorksheetFunction.VLookup(TextBox1.Value, myRange, 5, False)
        ComboBox4.Value = _
        Application.WorksheetFunction.VLookup(TextBox1.Value, myRange, 6, False)
        ComboBox1.Value = _
        Application.WorksheetFunction.VLookup(TextBox1.Value, myRange, 7, False)
        ComboBox2.Value = _
        Application.WorksheetFunction.VLookup(TextBox1.Value, myRange, 8, False)
        TextBox7.Value = Format(Application.WorksheetFunction.VLookup(TextBox1.Value, myRange, 9, False), "d mmmm yyyy")
    End If
    Me.ComboBox3.SetFocus
End Sub
Private Sub CommandButton2_Click() 'add
    Dim irow  As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Database")
    irow = ws.Cells(Rows.Count, 2) _
        .End(xlUp).Offset(1, 0).Row
    If Trim(Me.TextBox1.Value) = "" Then
        Me.TextBox1.SetFocus
        MsgBox "¤Ø³ÂÑ§äÁèä´éãÊè¢éÍÁÙÅ", vbInformation, "ÃÐºº¤é¹ËÒÊÓ¹Ç¹"
        Exit Sub
    Else
        ws.Cells(irow, 2).Value = Me.TextBox1.Value
        ws.Cells(irow, 3).Value = Me.TextBox2.Value
        ws.Cells(irow, 4).Value = Me.TextBox3.Value
        ws.Cells(irow, 5).Value = Me.TextBox4.Value
        ws.Cells(irow, 6).Value = Me.ComboBox3.Value
        ws.Cells(irow, 7).Value = Me.ComboBox4.Value
        ws.Cells(irow, 8).Value = Me.ComboBox1.Value
        ws.Cells(irow, 9).Value = Me.ComboBox2.Value
        ws.Cells(irow, 10).Value = Me.TextBox7.Value
        Me.TextBox1.Value = ""
        Me.TextBox2.Value = ""
        Me.TextBox3.Value = ""
        Me.TextBox4.Value = ""
        Me.ComboBox3.Value = ""
        Me.ComboBox4.Value = ""
        Me.ComboBox1.Value = ""
        Me.ComboBox2.Value = ""
        Me.TextBox7.Value = ""
        Me.TextBox1.SetFocus
        MsgBox "à¾ÔèÁ¢éÍÁÙÅàÃÕÂºÃéÍÂ", vbInformation, "ÃÐºº¤é¹ËÒÊÓ¹Ç¹"
    ActiveWorkbook.Save
    End If
    Call Runon
End Sub
Private Sub CommandButton3_Click() 'close
       ActiveWorkbook.Close SaveChanges:=True
        Application.Quit
End Sub
Private Sub CommandButton4_Click() ' edit
    Dim irow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Database")
    If Trim(Me.TextBox1.Value) = "" Then
        MsgBox "¤Ø³ÂÑ§äÁèä´éãÊè¢éÍÁÙÅ", vbInformation, "ÃÐºº¤é¹ËÒÊÓ¹Ç¹"
        Exit Sub
Else
        irow = _
        Application.WorksheetFunction.Match(TextBox1.Value, Sheets("Database").Range("B2:B1000000"), 0) + 1
    ws.Cells(irow, 2).Value = Me.TextBox1.Value
    ws.Cells(irow, 3).Value = Me.TextBox2.Value
    ws.Cells(irow, 4).Value = Me.TextBox3.Value
    ws.Cells(irow, 5).Value = Me.TextBox4.Value
    ws.Cells(irow, 6).Value = Me.ComboBox3.Value
    ws.Cells(irow, 7).Value = Me.ComboBox4.Value
    ws.Cells(irow, 8).Value = Me.ComboBox1.Value
    ws.Cells(irow, 9).Value = Me.ComboBox2.Value
    ws.Cells(irow, 10).Value = Me.TextBox7.Value
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
    Me.ComboBox3.Value = ""
    Me.ComboBox4.Value = ""
    Me.ComboBox1.Value = ""
    Me.ComboBox2.Value = ""
    Me.TextBox7.Value = ""
    Me.TextBox1.SetFocus
    MsgBox "á¡éä¢¢éÍÁÙÅàÃÕÂºÃéÍÂ", vbInformation, "ÃÐºº¤é¹ËÒÊÓ¹Ç¹"
    ActiveWorkbook.Save
     End If
End Sub




Private Sub CommandButton5_Click()
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
    Me.ComboBox3.Value = ""
    Me.ComboBox4.Value = ""
    Me.ComboBox1.Value = ""
    Me.ComboBox2.Value = ""
    Me.TextBox7.Value = ""
    Me.ListBox1.Value = ""
    Me.TextBox1.SetFocus
End Sub




Private Sub CommandButton6_Click()
UserForm2.Show
End Sub




Private Sub CommandButton7_Click()
UserForm3.Show
End Sub




Private Sub UserForm_Initialize()
        Me.StartUpPosition = 0
        Me.Top = 0
        Me.Left = 0
        a = "Person!_Type"
        ComboBox1.RowSource = a
        b = "Person!_Person"
        ComboBox2.RowSource = b
        C = "Person!_Worktype"
        ComboBox3.RowSource = C
        D = "Person!_Niti"
        ComboBox4.RowSource = D
        ListBox1.RowSource = "_ListMatch"
End Sub

Display More