ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,785
- Office Version
- 2007
- Platform
- Windows
I have the following code in use but when i click on a value in the Listbox i wish to then have that customer selected on my worksheet.
With regards the number 5 in the line of code shown, please advise how i know what number to use.
Im currently going through numbers one by one so it works as opposed to showing an error.
Reason being is i dont know where the 5 is referenced from on the list / sheet etc
With regards the number 5 in the line of code shown, please advise how i know what number to use.
Im currently going through numbers one by one so it works as opposed to showing an error.
Reason being is i dont know where the 5 is referenced from on the list / sheet etc
VBA Code:
Private Sub ListBox1_Click()
[COLOR=rgb(184, 49, 47)]Range("A" & ListBox1.List(ListBox1.ListIndex, 5)).Select[/COLOR]
Unload RoyalMailClaim
End Sub
Private Sub RunCode_Click()
Dim fndRng As Range
Dim firstAddress As String
With Me.ListBox1
.ColumnCount = 7
.ColumnWidths = "100;100;100;100;100;100;100"
End With
With Sheets("POSTAGE").Range("G:G")
Set fndRng = .Find(What:="RECEIVED NO DATE", LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not fndRng Is Nothing Then
firstAddress = fndRng.Address
Do
' check the date
If fndRng.Offset(, -6).Value > Date - 90 Then
' add to listbox
With Me.ListBox1
.AddItem fndRng.Offset(, -5).Value 'CUSTOMER
.List(.ListCount - 1, 1) = fndRng.Offset(, 2).Value 'USERNAME
.List(.ListCount - 1, 2) = fndRng.Offset(, -4).Value 'ITEM
.List(.ListCount - 1, 3) = fndRng.Offset(, -6).Value 'DATE
.List(.ListCount - 1, 4) = fndRng.Offset(, -2).Value 'TRACKING NUMBER
.List(.ListCount - 1, 5) = fndRng.Offset(, 5).Value 'CLAIM
.List(.ListCount - 1, 6) = fndRng.Value 'TBA
End With
End If
Set fndRng = .FindNext(fndRng)
Loop While Not fndRng Is Nothing And fndRng.Address <> firstAddress
End If
End With
End Sub