0

I have a VBA macro that allows the user to search for a value across numerous workbooks. The results of the search is then displayed in a listbox within the macro search form. When clicking on a row within this listbox, the row on the related excel workbook should activate and display on the related cell. However I am getting a 1004 error on the range.activate (rngCell.Activate) code that activates the cell within the workbook. I have tested this code through the debugger and it doesn't crash, however it crashes when using it otherwise. Is there any solution I could use for this? 

Private Sub lbxFinds_Change()
'Private Sub lbxFinds_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim wbName As String
    Dim wsName As String
    Dim cellAddress As String
    Dim cellContent As String
    Dim Wb As Workbook
    Dim Ws As Worksheet
    Dim rngCell As Range

    With Me.lbxFinds
        If .ListIndex < 0 Then Exit Sub
        If .List(.ListIndex, 0) = "Text not found." Then Exit Sub
        wbName = .List(.ListIndex, 0)
        wsName = .List(.ListIndex, 1)
        cellAddress = .List(.ListIndex, 2)
        cellContent = .List(.ListIndex, 3)
    End With

    Set Wb = Workbooks(wbName)
    Set Ws = Wb.Worksheets(wsName)
    Set rngCell = Ws.Range(cellAddress)

    Wb.Activate
    Ws.Activate
    rngCell.Activate
    Me.lbxFinds.SetFocus

    Set Wb = Nothing
    Set Ws = Nothing

End Sub

This is an image of the search Macro Search Form

braX
  • 11,506
  • 5
  • 20
  • 33
DavidKen
  • 1
  • 2
  • All that activating serves no purpose. https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – SJR Mar 18 '20 at 09:32
  • @SJR I think it does here. – Variatus Mar 18 '20 at 09:44
  • @Variatus - perhaps so in this case, but still better to use `application.goto` than all those activates. – SJR Mar 18 '20 at 09:51
  • `Activate` affects the `Selection`. For example, you may select a range of cells but only one of them will be the active one. So, when you activate `Rng.Cell`, how does that affect the current selection on that sheet? My wisdom comes from here: https://www.excelcampus.com/vba/select-vs-activate-method/. However, since the code works in debug mode I suspect that the cause of the crash might simply be that your system can't keep up with VBA's speed. Try a `DoEvents` loop to slow it down. And BTW, just in case it makes a difference, swap your 2 last lines of code. – Variatus Mar 18 '20 at 09:51
  • @Variatus, The particular cell that matches the value in the macro forms search field will be activated within the related worksheet when the listbox's row is selected. The program will often run for a few searches before I will get the 1004 error. I will try the other ideas you have suggested. – DavidKen Mar 18 '20 at 10:29
  • @Variatus, I had tried the DoEvents within the code but it still gives an error. I added 'On Error Resume Next' above the rngCell.Activate and (when debugging) noticed the variables within the code are holding the correct values of which row is clicked on within the listbox each time, however the correct workbook is not activating correctly on any other workbook than the first workbook that the macro was started on. Is there a way of getting access to the correct workbook upon each listbox click? – DavidKen Mar 20 '20 at 23:18
  • It's the combination of the Selection object, various Active properties, and a modeless form. The error type indicates that the error might not happen on the line of code the debugger highlights. My approach would be to micro-manage all contributors to the problem. You do not seem to have responded to my suggestion to examine the relationship between Selection and ActiveWindow or to look into Windows management. Add to that the need to analyse what `Me.lbxFinds.SetFocus` means to the `ActiveWindow` in which you have set an `ActiveCell` disregarding the `Selection` there. – Variatus Mar 21 '20 at 01:09
  • I put the line of code ThisWorkbook.Activate above the Wb.Activate line and the macro is now functioning as expected. I was able to remove Me.lbxFinds.SetFocus from the code as it was no longer needed. Many thanks for you help @Variatus. – DavidKen Mar 23 '20 at 13:06

0 Answers0