Kven


Hi,

I am very new to VBA Progamming. I have created a tool which checks for error in excel file. The below is my problem.

First i will select a file using Openfile method. Then the combobox will display all the sheets which are present in the selected excel file. Here i will select a excel sheet and run a code to check errors. The procedure will check the errors and shows how many errors are there. Like #NA, #Value.....etc

Now i want to get all the errors into listbox/Combobox, either the error name or text and cell reference.

Note: I am not adding any list to listbox using additem or using Range method.

Once the errors display in list box, If i click on any error the control should go that corresponding Excel cell where the error is present. That is i wanted to activate the cell which contains the error.

I have an idea that we have to use cell referrece here. But how to get a cell reference to a list box

If any one know this let me know...

Thanks in advance.




Re: How to activate a listbox selection using Excel VBA?

Andy Pope


Hi,

The simplest way is to use a multi column listbox and store the cells reference in one of the columns along with a description of the type of error.
You can then activate the cell using the click event of the list box.

If you need more help with the code it would be helpful if you could post what you already have.







Re: How to activate a listbox selection using Excel VBA?

Kven

Hi,

Yes i too wanted the same. But i am not getting the correct code for it. If possible plz provide me the code.

I have provided the code which i am using to check the errors in excel sheet. If u have any other way plz let me know with the code...

If ComboBox1.Value = "" Then
MsgBox "You have not selected any File!", vbOKOnly
ElseIf ListBox1.ListCount = 0 Then

Set sourceWB1 = Workbooks.Open(FileName, False, True)
Set ws1 = ActiveWorkbook.Worksheets(ComboBox1.Value)

On Error Resume Next
Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 16)
If rng Is Nothing Then Exit Sub
With WorksheetFunction
diffcount = 0
For Each cell In rng
fmla = Right(cell.Formula, Len(cell.Formula) - 1)
maxR = Rows.Address
ListBox1.AddItem cell.Text
diffcount = diffcount + 1
Next
End With
MsgBox diffcount & "Cells Contain Errors"
Else
MsgBox " Error checking done for the selected file"

End If

Thanks for ur time...






Re: How to activate a listbox selection using Excel VBA?

Andy Pope

Too keep this simple the code will just work on the active sheet.

Create a userform with a listbox and label control.
Add the following code.
In the listbox will be any cells that have an error. The displayed text and actual formula will be in the listbox. Along with the cells address. When you select an item in the listbox the cell on the sheet will be selected.
Also displayed is the cell address. This can be hidden by uncommenting the ColumnWidths property in the userform initialize event.

Private Sub ListBox1_Click()

Dim strAddress As String

strAddress = ListBox1.List(ListBox1.ListIndex, 2)
ActiveSheet.Range(strAddress).Activate

End Sub

Private Sub UserForm_Initialize()

Dim ws1 As Worksheet
Dim rng As Range
Dim diffcount
Dim Cell As Range

ListBox1.ColumnCount = 3
' uncomment once you understand how it works.
' ListBox1.ColumnWidths = ";;0"

Set ws1 = ActiveSheet

On Error Resume Next
Set rng = ws1.Cells.SpecialCells(xlCellTypeFormulas, 16)
If rng Is Nothing Then
MsgBox "No errors on " & ws1.Name
Exit Sub
Else
ListBox1.Clear
For Each Cell In rng
ListBox1.AddItem Cell.Text
ListBox1.List(ListBox1.ListCount - 1, 1) = Cell.Formula
ListBox1.List(ListBox1.ListCount - 1, 2) = Cell.Address
Next
Label1.Caption = rng.Cells.Count & " Cells Contain Errors"
End If

End Sub






Re: How to activate a listbox selection using Excel VBA?

Kven

Hi Pope,

First thanks a lot..

As you told, I have already used same form. Where i have wrote codes for a cmd button to select any excel file from the system. And then one combobox will show all the sheets present in that. Where we can select any sheet which will become an activesheet. Now i have another cmd button which is used to check for errors in that sheet..

Can i use same code for that button also...

Thanks,





Re: How to activate a listbox selection using Excel VBA?

Andy Pope

In the initialize event the activesheet is assigned to a object ws1

You can change that code to use the workbook/sheet referenced by your other controls.
If this is happening within the same form you will need to move the Initialize code into a routine so you can use it whilst the userform is loaded.





Re: How to activate a listbox selection using Excel VBA?

Kven

Hi,

Thanks a lot Pope. The procedure is working fine. Still i have one more doubt.

I am exporting the errors(Only text) into new excel sheet. So that user can check the errors and correct it. Now i wanted to export all the details like,

1. Error type

2. Full Formula

3. Cell Reference

Which are present in list box.

Is it possible to do it If yes let me know..

The below code is what i am using to export:

Private Sub cmdExport_Click()
Dim xlApp As Excel.Application
Dim xlSh As Excel.Worksheet
Dim i As Long
Set xlApp = New Excel.Application

xlApp.Visible = True
xlApp.Workbooks.Add

Set xlSh = xlApp.Workbooks(1).Worksheets(1)
For i = 1 To ListBox1.ListCount
xlSh.Cells(i, 1).Value = ListBox1.List(i - 1)

Next
Set xlSh = Nothing
Set xlApp = Nothing
End Sub

Thanks once again for your time..





Re: How to activate a listbox selection using Excel VBA?

Andy Pope

To output the contents of the other columns in a multi column listbox use

For i = 1 To ListBox1.ListCount
xlSh.Cells(i, 1).Value = ListBox1.List(i - 1, 0)
xlSh.Cells(i, 2).Value = ListBox1.List(i - 1, 1)
xlSh.Cells(i, 3).Value = ListBox1.List(i - 1, 2)
Next






Re: How to activate a listbox selection using Excel VBA?

Kven

Hi,

Thanks a lot...





Re: How to activate a listbox selection using Excel VBA?

Kven

Hi,

Is it possible to insert any headings to the new sheet Like Serial No, Error Type and Cell Ref....

Thanks





Re: How to activate a listbox selection using Excel VBA?

Kven

Hi,

Another quetion, Is it possible to add column headings to the listbox If we add heading while the errors popsup into listbox then it wil be good... and there is no need to insert any headings to the new sheet right

Can we do it like that

Thanks,





Re: How to activate a listbox selection using Excel VBA?

Andy Pope

A listbox can have column headers BUT you can only populate them if you use the RowSource property and specify a range.
So if your headers are in A1:C1 and your data in A2:C10 you would use.

RowSource: A2:C10

You can not manually or via code set the column header text.

So either write or of the listbox content to a range of cells and use the row immediately above the range to hold the text for headers. Or use Labels and place these above your listbox. You can control the width of the columns within a list box by using the ColumnWidths property.





Re: How to activate a listbox selection using Excel VBA?

Kven

Ok than thanks a lot for your time....

Have a nice time.





Re: How to activate a listbox selection using Excel VBA?

Kven

Hi Pope,

I have one more problem in my procedure.

1. Previously i am selecting a single sheet to check for errors. But that is not enough. So now i wanted to select all the sheets of a workbook and check for errors. Is it possible to give an option to user to select all the sheets and go ahead with error check tool. Like an option button or checkbox. You know the procedure which i have used(In my second thread). Please let me know with codse if its possible.

2. Second thing is i wanted to show the corresponding sheetnames which has the errors (like cell reference) in listbox. I am exporting all of them to new worksheet. So that user can check for the errors. If i am selecting a single sheet then there is no problem. If i select all the sheets then the problem starts.

If u have any idea about this two plz let me know..

Thanks again,





Re: How to activate a listbox selection using Excel VBA?

Andy Pope

You can include the sheet reference either in a separate column or as part of the existing cell column.

When going to the selected cell you will need to activate the sheet first before the cell.

So if the reference was Sheet3!A1 you would need to use code something like,

range("Sheet3!A2").Parent.activate
range("Sheet3!A2").activate