Cell won't activate after executing Find in other workbook

[NotAGuru]

Greetings!

The worksheets in Workbook A allows the user to enter either a number or part of a name in column C. If the entry is a number, a formula in column D does a VLookup in Workbook B to find the name. That works fine.

If the user enters part of a name into column C, a VBA macro executes a Find command and returns the number associated with it. That works fine also. However, in the second scenario, I cannot get the worksheet activated to accept additional information unless I physically click on the worksheet with the mouse.

I have tried using Windows(Window A).activate, Workbooks(A).activate, Worksheets(1).activate and range(the current cell).activate. None of these works.

I'm using Windows 7 and Excel 2007.

Thanks for any insight that you can provide. It's driving me crazy!

 

[jim may]

Have you tried the Range("A1").Select or the like...?

 

[Peter_SSs]

Could we see the code?

 

[NotAGuru]

Here is the entire code for the Workbook_SheetChange event

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim strCurrCellAddr As String       'the cell address of the info that is used for the lookup
    Dim strSearchInfo As String         'the information used for the lookup
    Dim strFoundInfo As Long            'the found info from the lookup
    Dim wbOpen As Workbook              'the complete path to the 'Logic - GL Accounts List'
    Dim WBGLJTrxName As String          'the name of the workbook - 'General Journal Transactions.xlsm'
    Dim WSGLJTrxName As String          'the name of the worksheet currently being used
    Dim wndwGLJTRX As String            'the name of the window for the 'General Journal Transactions.xlsm' workbook

    WBGLJTrxName = Name                 'assign the name of the workbook
    WSGLJTrxName = ActiveSheet.Name     'assign the name of the active worksheet
    Const strGLListPath = "S:\API_Lists\Logic Lists\"       'the path to the 'Logic - GL Accounts List' workbook
    Const strGLListName = "Logic - GL Accounts List.xls"    'the filename of the 'Logic - GL Accounts List' workbook

    wndwGLJTRX = ActiveWindow.Caption   'assign the name of the window - using to attemt to activate it later

    Application.ScreenUpdating = False

    'check if the changed cell is in G/L Account Number range
    If Not Intersect(Target, Range("C7:C276")) Is Nothing Then
        strCurrCellAddr = Target.Cells.Address  'assign the target cell address for later use
        strSearchInfo = Target.Cells.Text       'assign the number or name that is entered (use as lookup value)

        If strSearchInfo = "" Then              'if there was no entry, or the entry was deleted
            Range(Cells(Range(strCurrCellAddr).Row, Range(strCurrCellAddr).Column).Address).Select
        ElseIf Left(strSearchInfo, 1) Like ("[A-Z]") Or _
            Left(strSearchInfo, 1) Like ("[a-z]") Then              'if the entry is alphabetic
            Set wbOpen = Workbooks.Open(Filename:=strGLListPath & strGLListName, _
                    ReadOnly:=True, Password:="rls$rjs!")   'open the 'Logic - GL Accounts List' workbook
            
            With wbOpen

                'if the entry was alphabetic, find the first row containing the text
                Cells.Find(What:=strSearchInfo, after:=ActiveCell, LookIn:=xlValues, LookAt:= _
                    xlPart, SearchOrder:=xlByRows, searchdirection:=xlNext, MatchCase:=False) _
                    .Activate
                strFoundInfo = ActiveCell.Offset(0, -1).Value           'the matching G/L Account Number found

                Workbooks(WBGLJTrxName).Worksheets(WSGLJTrxName).Activate
                Range(strCurrCellAddr).Select

                Range(strCurrCellAddr).Value = strFoundInfo             'copy the found info
                Workbooks("Logic - GL Accounts List.xls").Activate
                Workbooks("General Journal Transactions.xlsm").Activate
                Range(Cells(Range(strCurrCellAddr).Row, Range(strCurrCellAddr).Column + 2).Address).Select
            End With

            Range(Cells(Range(strCurrCellAddr).Row, Range(strCurrCellAddr).Column + 2).Address).Select

            If wbOpen Is Nothing Then 'Logic - GL Accounts List' is not open
                Set wbOpen = Nothing
                On Error GoTo 0
            Else 'Logic - GL Accounts List' is open, so close it
                Workbooks(strGLListName).Close SaveChanges:=False
                Set wbOpen = Nothing
                On Error GoTo 0
            End If

            Exit Sub

        ElseIf (Range(strCurrCellAddr).Value) > 0 Then      'if the entry is numeric
            Range(Cells(Range(strCurrCellAddr).Row, Range(strCurrCellAddr).Column + 2).Address).Select

        End If

    Application.ScreenUpdating = True
    Workbooks(WBGLJTrxName).Worksheets(WSGLJTrxName).Activate
    Workbooks(WBGLJTrxName).Worksheets(WSGLJTrxName).Activate

    End If

    'If the user types "d" or "c", this will capitalize them
    If Not Intersect(Target, Range("E7:E276")) Is Nothing Then
        Select Case Target.Value
            Case "d"
                Target.Value = "D"
            Case "c"
                Target.Value = "C"
        End Select
    End If

End Sub

Many thanks for your interest.

 

[Peter_SSs]

I'm afraid that looks a bit too complex to me to try to set up a replica of your situation to test.

 

[NotAGuru]

Peter,

Would it help if I sent the two workbooks? There's nothing particularly confidential. In fact, if I can get this little problem fixed, I was going to upload it to Microsoft as a template for others to use.

Thanks,
Rick

 

[shg]

Maybe like this:

    Dim cell        As Range
 
    Set cell = Cells.Find(What:=strSearchInfo, _
                          After:=ActiveCell, _
                          LookIn:=xlValues, _
                          LookAt:=xlPart, _
                          SearchOrder:=xlByRows, _
                          Searchdirection:=xlNext, _
                          MatchCase:=False)

    If cell Is Nothing Then
        MsgBox "Not found"
        ' quit or continue ...
    Else
        Application.Goto cell
    End If

You have a With wbOpen statement and then don't use the reference it establishes. You should either use the reference or remove the statement.