Neil said:
Hello,
If you go into the Visual Basic section (alt+F11) and goto
Tools->References. Add Microsoft Excel 10.0 Object Library (or the highest
version available). You will be able to use properties and methods available
in Excel. How are you validating your worksheet? When you press find all,
the following values are used to populate the listbox.
'Book' is the .Name property of the Workbook object (When in Excel VB can be
accessed by ActiveWorkBook.Name)
'Sheet' is the .Name property of the Worksheet object (When in Excel VB can
be accessed by ActiveSheet.Name)
'Cell' is the .Address property of the Range object (When in Excel VB can be
accessed by ActiveCell.Address)
'Value' is the .Value property of the Range object (When in Excel VB can be
accessed by ActiveCell.Value)
Obviously, when using Access it is a bit more complicated. If you show me
the code that you use to validate the spreadsheet I may be able to help you
further. What I would do is log all this information into a table (when
checking) and display the table as the rowsource for the listbox.
Neil.
Assumptions
Searching used range in one sheet
Corrupt cells contain "xxx"
The second column showing the cell address is not hidden
Userform contains 1 listbox and one command button
loads 2 columns showing corrupt cells and address
Private Sub UserForm_Initialize()
Set d = ActiveSheet.UsedRange
ListBox1.ColumnCount = 2
For Each cl In d
If cl = "xxx" Then
ListBox1.AddItem cl
ListBox1.Column(1, ListBox1.ListCount - 1) = cl.Address
End If
Next
End Sub
Jumps to selected error cell
Private Sub CommandButton1_Click()
If ListBox1.ListIndex = -1 Then
MsgBox "Select an item in list"
Exit Sub
Else
Range(ListBox1.List(ListBox1.ListIndex, 1)).Select
Unload Me
End If
End Sub
