listbox and textbox problem

I don't understand why this is happening. I have a form with a listbox and
four textboxex. when an value is selected in the listbox, the 4 textboxes
need to show data (vehicle information) from the same row as the selected
value.
it works.... sort of. if I have the vehicle information sheet selected,
everything works fine, but if any other sheet is selected, the values for the
text boxes come from there. here is the code i have in the userform:


Private Sub CommandButton1_Click()
TextBox1 = Clear
TextBox2 = Clear
TextBox3 = Clear
TextBox4 = Clear
AddTicket.Hide
End Sub

Private Sub CommandButton2_Click()
Call Add_Ticket
End Sub

Private Sub ListBox1_Change()
With Sheets("Sheet2").Range("A:A,E:E")
Dim lastrow As Long, a As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For a = lastrow To 2 Step -1
If Cells(a, 1).Text = ListBox1.Text Then
If Cells(a, 2).Text = "" Then
TextBox1.Text = "No Info Availiable"
Else: TextBox1.Text = Cells(a, 2).Text
End If
If Cells(a, 3).Text = "" Then
TextBox2.Text = "No Info Availiable"
Else: TextBox2.Text = Cells(a, 3).Text
End If
If Cells(a, 4).Text = "" Then
TextBox3.Text = "No Info Availiable"
Else: TextBox3.Text = Cells(a, 4).Text
End If
If Cells(a, 5).Text = "" Then
TextBox4.Text = "No Info Availiable"
Else: TextBox4.Text = Cells(a, 5).Text
End If
End If
Next
End With
End Sub

Private Sub UserForm_Initialize()
Dim ListCellreasons As Range
Dim listcellofficers As Range
Dim listcellowner As Range
With Sheets("sheet2").Range("A:A,C:C")
For Each listcellowner In Range("Owner")
If listcellowner.Value <> "" Then
ListBox1.AddItem listcellowner.Value
End If
Next
For Each listcellofficers In Range("officers")
If listcellofficers.Value <> "" Then
ListBox2.AddItem listcellofficers.Value
End If
Next
For Each ListCellreasons In Range("reasons")
If ListCellreasons.Value <> "" Then
ListBox3.AddItem ListCellreasons.Value
End If
Next
End With
End Sub

TIA

You with structure doesn't do anything unless you precede object to be
qualified with a period

Worksheets("Sheet2").Activate
With Worksheets("Sheet1")
msgbox cells(1,1).Value
End with

will display A1 from Sheet2 (since sheet2 is the active sheet) - your WITH
statement is doing nothing

Worksheets("sheet2").Activate
With Worksheets("Sheet1")
msgbox .Cells(1,1).Value
End With

now displays the value of A1 in Sheet1.

fix your code to utilize your WITH statement or otherwise qualify your cell
references with a specific sheet reference.

--
Regards,
Tom Ogilvy

"jeramie" wrote:

> I don't understand why this is happening. I have a form with a listbox and
> four textboxex. when an value is selected in the listbox, the 4 textboxes
> need to show data (vehicle information) from the same row as the selected
> value.
> it works.... sort of. if I have the vehicle information sheet selected,
> everything works fine, but if any other sheet is selected, the values for the
> text boxes come from there. here is the code i have in the userform:
>
>
> Private Sub CommandButton1_Click()
> TextBox1 = Clear
> TextBox2 = Clear
> TextBox3 = Clear
> TextBox4 = Clear
> AddTicket.Hide
> End Sub
>
> Private Sub CommandButton2_Click()
> Call Add_Ticket
> End Sub
>
> Private Sub ListBox1_Change()
> With Sheets("Sheet2").Range("A:A,E:E")
> Dim lastrow As Long, a As Long
> lastrow = Cells(Rows.Count, 1).End(xlUp).Row
> For a = lastrow To 2 Step -1
> If Cells(a, 1).Text = ListBox1.Text Then
> If Cells(a, 2).Text = "" Then
> TextBox1.Text = "No Info Availiable"
> Else: TextBox1.Text = Cells(a, 2).Text
> End If
> If Cells(a, 3).Text = "" Then
> TextBox2.Text = "No Info Availiable"
> Else: TextBox2.Text = Cells(a, 3).Text
> End If
> If Cells(a, 4).Text = "" Then
> TextBox3.Text = "No Info Availiable"
> Else: TextBox3.Text = Cells(a, 4).Text
> End If
> If Cells(a, 5).Text = "" Then
> TextBox4.Text = "No Info Availiable"
> Else: TextBox4.Text = Cells(a, 5).Text
> End If
> End If
> Next
> End With
> End Sub
>
> Private Sub UserForm_Initialize()
> Dim ListCellreasons As Range
> Dim listcellofficers As Range
> Dim listcellowner As Range
> With Sheets("sheet2").Range("A:A,C:C")
> For Each listcellowner In Range("Owner")
> If listcellowner.Value <> "" Then
> ListBox1.AddItem listcellowner.Value
> End If
> Next
> For Each listcellofficers In Range("officers")
> If listcellofficers.Value <> "" Then
> ListBox2.AddItem listcellofficers.Value
> End If
> Next
> For Each ListCellreasons In Range("reasons")
> If ListCellreasons.Value <> "" Then
> ListBox3.AddItem ListCellreasons.Value
> End If
> Next
> End With
> End Sub
>
> TIA

All of your Cells references are unqualified - that is, you don't specify
which sheet Cells belongs to and it assumes the active sheet. You have a
With block, but it doesn't look you're using anything related to
Range("A:A,E:E"). Try putting a period before each Cells(...) and see if
that does it for you.


--
**** Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

jeramie wrote:
> I don't understand why this is happening. I have a form with a
> listbox and four textboxex. when an value is selected in the listbox,
> the 4 textboxes need to show data (vehicle information) from the same
> row as the selected value.
> it works.... sort of. if I have the vehicle information sheet
> selected, everything works fine, but if any other sheet is selected,
> the values for the text boxes come from there. here is the code i
> have in the userform:
>
>
> Private Sub CommandButton1_Click()
> TextBox1 = Clear
> TextBox2 = Clear
> TextBox3 = Clear
> TextBox4 = Clear
> AddTicket.Hide
> End Sub
>
> Private Sub CommandButton2_Click()
> Call Add_Ticket
> End Sub
>
> Private Sub ListBox1_Change()
> With Sheets("Sheet2").Range("A:A,E:E")
> Dim lastrow As Long, a As Long
> lastrow = Cells(Rows.Count, 1).End(xlUp).Row
> For a = lastrow To 2 Step -1
> If Cells(a, 1).Text = ListBox1.Text Then
> If Cells(a, 2).Text = "" Then
> TextBox1.Text = "No Info Availiable"
> Else: TextBox1.Text = Cells(a, 2).Text
> End If
> If Cells(a, 3).Text = "" Then
> TextBox2.Text = "No Info Availiable"
> Else: TextBox2.Text = Cells(a, 3).Text
> End If
> If Cells(a, 4).Text = "" Then
> TextBox3.Text = "No Info Availiable"
> Else: TextBox3.Text = Cells(a, 4).Text
> End If
> If Cells(a, 5).Text = "" Then
> TextBox4.Text = "No Info Availiable"
> Else: TextBox4.Text = Cells(a, 5).Text
> End If
> End If
> Next
> End With
> End Sub
>
> Private Sub UserForm_Initialize()
> Dim ListCellreasons As Range
> Dim listcellofficers As Range
> Dim listcellowner As Range
> With Sheets("sheet2").Range("A:A,C:C")
> For Each listcellowner In Range("Owner")
> If listcellowner.Value <> "" Then
> ListBox1.AddItem listcellowner.Value
> End If
> Next
> For Each listcellofficers In Range("officers")
> If listcellofficers.Value <> "" Then
> ListBox2.AddItem listcellofficers.Value
> End If
> Next
> For Each ListCellreasons In Range("reasons")
> If ListCellreasons.Value <> "" Then
> ListBox3.AddItem ListCellreasons.Value
> End If
> Next
> End With
> End Sub
>
> TIA