Glaswegian
02-05-2015, 05:40 AM
Hi again
I'm puzzled with this and hope someone can help.
I have a listbox on a multipage userform - the box is populated when the form is opened from data on a hidden worksheet. I take 5 columns of data but need to only show 3 to the user. Users then have the opportunity to delete a record from the worksheet if required - they simply click on the customer name and then click a command button.
Customer names may not be unique but the customer reference number (8 digit) in the 5th column is unique. Users will choose to delete a record based on the customer name and reference number combination.
However, when I try to return the reference number to find the correct customer, I do not receive the correct value - and I cannot work out why.
The listbox is NOT multiselect. There are 5 columns in the listbox, with 2 of them set to 0 width, leaving 3 visible. ColumnCount=5 and BoundColum=5 - everything else is at the default setting.
The code is just a simple Find on the hidden data worksheet:
Private Sub CommandButton8_Click()
Dim vaDetails
Dim myReply
Dim lItem As Long
Dim Found As Range, firstAddress As String
'check to ensure the user is aware of what will happen if they continue
myReply = MsgBox("This will remove the customer from the Master Record" & vbCr & vbCr & _
"and CANNOT BE UNDONE. Do you want to continue?", _
vbOKCancel + vbDefaultButton2, "Update Master Record")
If myReply = vbCancel Then Exit Sub
'use the selected item from the listbox
lItem = Me.lbCustView3.ListIndex
If lItem <> -1 Then
vaDetails = Me.lbCustView3.List(lItem)
End If
'start the find
Set Found = Sheets("Data").UsedRange.Offset(1, 0).Find(what:=vaDetails, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not Found Is Nothing Then
firstAddress = Found.Address
Found.EntireRow.Copy _
Destination:=Worksheets("Removed").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End If
Found.EntireRow.Delete shift:=xlUp
End Sub
The routine will simply not find the 8 digit reference number.
Probably missing something straightforward but cannot see it - any help greatly appreciated.
I'm puzzled with this and hope someone can help.
I have a listbox on a multipage userform - the box is populated when the form is opened from data on a hidden worksheet. I take 5 columns of data but need to only show 3 to the user. Users then have the opportunity to delete a record from the worksheet if required - they simply click on the customer name and then click a command button.
Customer names may not be unique but the customer reference number (8 digit) in the 5th column is unique. Users will choose to delete a record based on the customer name and reference number combination.
However, when I try to return the reference number to find the correct customer, I do not receive the correct value - and I cannot work out why.
The listbox is NOT multiselect. There are 5 columns in the listbox, with 2 of them set to 0 width, leaving 3 visible. ColumnCount=5 and BoundColum=5 - everything else is at the default setting.
The code is just a simple Find on the hidden data worksheet:
Private Sub CommandButton8_Click()
Dim vaDetails
Dim myReply
Dim lItem As Long
Dim Found As Range, firstAddress As String
'check to ensure the user is aware of what will happen if they continue
myReply = MsgBox("This will remove the customer from the Master Record" & vbCr & vbCr & _
"and CANNOT BE UNDONE. Do you want to continue?", _
vbOKCancel + vbDefaultButton2, "Update Master Record")
If myReply = vbCancel Then Exit Sub
'use the selected item from the listbox
lItem = Me.lbCustView3.ListIndex
If lItem <> -1 Then
vaDetails = Me.lbCustView3.List(lItem)
End If
'start the find
Set Found = Sheets("Data").UsedRange.Offset(1, 0).Find(what:=vaDetails, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not Found Is Nothing Then
firstAddress = Found.Address
Found.EntireRow.Copy _
Destination:=Worksheets("Removed").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End If
Found.EntireRow.Delete shift:=xlUp
End Sub
The routine will simply not find the 8 digit reference number.
Probably missing something straightforward but cannot see it - any help greatly appreciated.