Why wont my code populate Textbox ?

[ctm]

Private Sub ListBox3_AfterUpdate()
Application.ScreenUpdating = False
ListBox7.Clear
Dim LastCell As Long
Dim myrow As Long
On Error Resume Next
LastCell = Worksheets("InspectionData").Cells(Rows.Count,
"A").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 2 To LastCell
If .Cells(myrow, 1) <> "" Then
If .Cells(myrow, 1).Offset(-1, 2).Value = ListBox5.Value And
_
.Cells(myrow, 1).Offset(-1, 6).Value = ListBox6.Value
Then
For i = 2 To 22
If .Cells(myrow, 1).Offset(i, 2).Value =
ListBox3.Value Then
TextBox4.Value = .Cells(myrow, 1).Offset(i, 2).Value

End If
Next i
End If
End If
Next
End With
Sheets("Main").Activate
Application.ScreenUpdating = True
End Sub


I am trying to capture a value in Column A with the above code.

I have a form which uses a couple of listboxes and a textbox to narrow down
a selected value in a worksheet.

There is ALWAYS a value in column A, and between 2 - 22 rows below that
value in column A, i have numerical values in colum C.
Thus the (For i = 2-22 statement above).

Listbox5 selection is a customers name, that is ALWAYS offset 1 row above
AND Always in Column C(i,2) from the value in column A.
Listbox6 selection is a product. It is ALWAYS offset 1 row Above AND Always
in Column G(i,6) from the value in Column A.

Listbox3 is a numerical value that is ALWAYS in Column C and is between 2
AND 22 rows below a value in Column A.
As the possibility is that there could be MORE than one numerical value the
same in Column C, i have the IF statements with Listbox5 and Listbox6, to
ensure those listbox values are found in cells Offset to the value in Column
A.
*************************************************************************************************
I cannot seem to be able to obtain the Column A value that is OFFSET to the
value selected in Listbox3, and place it in Textbox4.
*************************************************************************************************


Can anyone see the problem in my code, and help me solve this headache of
mine?

Regards
Corey

 

[JLGWhiz]

This line:

TextBox4.Value = .Cells(myrow, 1).Offset(i, 2).Value

Should probably be something like:

TextBox4.AddItem .Cells(myrow, 1).Offset(i, 2).Value

 

[Joel]

Textboxes don't have additems, only listbox and combobox have additems.

Comment out the On Error statement to find where your error is. It is
impossible to give an answer because there are too many resons that the code
may not work. the On Error statement is hiding the cause of the problem.

 

[JLGWhiz]

I just woke up. That's my story and I'm sticking to it! <g>

 

[JLGWhiz]

Now that I am fully awake, the only reason that I can see that you would not
get a value in the TextBox is that the cell you are trying to get the value
from does not contain a value. You need to edit your code by stepping
through it and make sure that the relative references you are using are
actually for the cells you think they are for. You also need to make sure
that your if statments return a true at the point where you want to load the
TextBox. If the statement is not true, there is not value loaded.

 

[ctm]

I fa stepped through each If statement, and there should be a value as true
in each one, and a value in the Column A, but i get no textbox value still.

Corey..

 

[ctm]

Removing the 'On Error Resume Next' gives me No error messages, and also No
textbox value either.???

 

[JLGWhiz]

Here is what I read from your code to set the
condition for for the TextBox to load. First
iteration only.

On Sheets("InspectionData")
If A2 is not blank, and C1 = ListBox5 value and
G1 = Listbox6 value And C4 {thru C25} = Listbox3
value Then TextBox4 test should = C4 value

This would then walk down columns A, C and G
thru the last row evaluating the criteria in
C4 thru C25 on each iteration to find a value to
load into TextBox4. Is this what you expect it
to do?

 

[ctm]

Your description sounds like what i am trying to achieve, but textbox4
needs to be the value in column A(A2) if at top of sheet.

Corey...

 

[ryguy7272]

Try something like this:
me.textbox1.text = thisworkbook.worksheets("Sheet1").range("A1").value


Regards,
Ryan--