PDA

View Full Version : Excel Userform Listbox VBA Help



andrewc85
06-02-2016, 11:26 PM
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
06-03-2016, 12:39 AM
Please use code tages !!

I don't see any reference to any listbox.

A simple sample file would be highly appreciated.

andrewc85
06-03-2016, 05:48 AM
Hello, Please find the code in correct format... I think. And I have uploaded a sample of the document.

Many thanks,

Andrew

16314



'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
06-03-2016, 06:06 AM
All you need:

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

mdmackillop
06-06-2016, 10:52 AM
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
06-06-2016, 12:02 PM
did you know ?


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

mdmackillop
06-06-2016, 12:25 PM
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
06-06-2016, 01:29 PM
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
06-06-2016, 02:13 PM
That one doesn't run :(

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

snb
06-07-2016, 12:20 AM
So if you adapt the textbox names ( - structuring precedes coding - ) to T_0 to T_3: :whistle:


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
06-07-2016, 10:12 AM
So if you adapt the textbox names ( - structuring precedes coding - ) to T_0 to T_3
Very good point:clap: