Excel Userform Listbox VBA Help

[andrewc85]

Hello,

I have the below code that I am stuck on, and would be appreciative for any help. It works with a listbox on a userform.

For the text that is in red (the If Statement), the script executes the first "then" action (Sheet1.Cells(x, "B").Value = Me.TextBox1.Text but does not execute the following 2 actions. Sheet1.Cells(x, "C").Value = Me.TextBox2.Text and Sheet1.Cells(x, "D").Value = Me.TextBox3.Text

Any help would be very much appreicated.

Andrew

Code:

Private Sub CommandButton1_Click()
Dim erowa As Integer
Dim x As Integer
Dim y As Integer

If Me.TextBox1.Text = "" Or Me.TextBox2.Text = "" Or Me.TextBox3.Text = "" Then

MsgBox "Please Select a Row"

Else

erowa = Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))

For x = 2 To erowa
If Sheet1.Cells(x, "A").Value = Me.TextBox4.Text Then
Sheet1.Cells(x, "B").Value = Me.TextBox1.Text
Sheet1.Cells(x, "C").Value = Me.TextBox2.Text
Sheet1.Cells(x, "D").Value = Me.TextBox3.Text

End If

Next
Me.TextBox1.Text = ""
Me.TextBox2.Text = ""
Me.TextBox3.Text = ""

End If

End Sub

[snb]

Please use code tages !!

I don't see any reference to any listbox.

A simple sample file would be highly appreciated.

[andrewc85]

Hello, Please find the code in correct format... I think. And I have uploaded a sample of the document.

Many thanks,

Andrew

Testing Macro.xlsm

 'update existing recordPrivate Sub CommandButton1_Click()
Dim erowa As Integer
Dim x As Integer
Dim y As Integer

If Me.TextBox1.Text = "" Or Me.TextBox2.Text = "" Or Me.TextBox3.Text = "" Then

MsgBox "Please Select a Row"

Else

erowa = Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))

For x = 2 To erowa
If Sheet1.Cells(x, "A").Value = Me.TextBox4.Text Then
    Sheet1.Cells(x, "D").Value = Me.TextBox3.Text
    Sheet1.Cells(x, "C").Value = Me.TextBox2.Text
    Sheet1.Cells(x, "B").Value = Me.TextBox1.Text

End If

Next
Me.TextBox1.Text = ""
Me.TextBox2.Text = ""
Me.TextBox3.Text = ""

End If

End Sub

[snb]

All you need:

http://www.snb-vba.eu/VBA_Userform_in_database_en.html

[mdmackillop]

Your changes trigger Listbox.Click events, as the sheet data is the RowSource for the list. This workaround will disable these.

Dim Disable as Boolean
'update existing record
Private Sub CommandButton1_Click()
    Dim erowa As Integer
    Dim x As Integer
    Dim y As Integer
    If Me.TextBox1.Text = "" Or Me.TextBox2.Text = "" Or Me.TextBox3.Text = "" Then
        MsgBox "Please Select a Row"
    Else
        erowa = Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
        Disable = True
        For x = 2 To erowa
            If Sheet1.Cells(x, "A").Value = Me.TextBox4.Text Then
                Sheet1.Cells(x, "D").Value = Me.TextBox3.Text
                Sheet1.Cells(x, "C").Value = Me.TextBox2.Text
                Sheet1.Cells(x, "B").Value = Me.TextBox1.Text
                Exit For
            End If
        Next
        Disable = False
        Me.TextBox1.Text = ""
        Me.TextBox2.Text = ""
        Me.TextBox3.Text = ""
    End If
End Sub

and

Private Sub ListBox1_Click()
    If Not Disable Then
        TextBox4.Text = Me.ListBox1.List(ListBox1.ListIndex, 0)
        TextBox1.Text = Me.ListBox1.List(ListBox1.ListIndex, 1)
        TextBox2.Text = Me.ListBox1.List(ListBox1.ListIndex, 2)
        TextBox3.Text = Me.ListBox1.List(ListBox1.ListIndex, 3)
    End If
End Sub

[snb]

did you know ?

If Sheet1.Cells(x, "A").Value = TextBox4 Then sheet1.Cells(x, 2).resize(,3)=array(textbox1,textbox2,textbox3)

[mdmackillop]

and

Private Sub ListBox1_Click()
    If Not Disable Then
        For i = 1 To 4
        Me.Controls("Textbox" & i) = Me.ListBox1.List(ListBox1.ListIndex, i Mod 4)
        Next
    End If
End Sub

[snb]

or

Private Sub ListBox1_Change()
 if listbox1.listindex>-1 then 
   For j = 1 To 4 
     Me("Textbox" & j) = ListBox1.column(j) 
   Next
 end if
End Sub

[mdmackillop]

That one doesn't run

TextBox4.Text = Me.ListBox1.List(ListBox1.ListIndex, 0)

[snb]

So if you adapt the textbox names ( - structuring precedes coding - ) to T_0 to T_3:

Private Sub ListBox1_Change() 
    If listbox1.listindex>-1 Then 
        For j = 0 To 3 
            Me("T_" & j) = ListBox1.column(j) 
        Next 
    End If 
End Sub 

[mdmackillop]

So if you adapt the textbox names ( - structuring precedes coding - ) to T_0 to T_3

Very good point