listbox and textbox problem

[Guest]

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

 

[Guest]

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.

 

[Dick Kusleika]

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.