Solved Jumping to random? cell (1 Viewer)

[Gasman]

Hi all,

Would anyone know why this code would jump to what appears a random cell, before it calls the url code?

I have client CMS numbers in an Excel sheet and this code will take me into the CMS charity system with the correct ID.

Sometimes! on first use, it jumps to another cell. I added the activecell part to try and stop that, but it still does it?

I asked on the MrExcel forum a good while back, but received no response.

Sub CMSConnect()
Dim blnUrlOpen As Boolean
' Now use a function to bypass login page when already logged in
'ActiveWorkbook.FollowHyperlink cstrCMSUrl & ActiveCell.Value, "", False
' Sometimes cell A1 is selected, so check value is numeric
If IsNumeric(ActiveCell.Value) Then
    blnUrlOpen = fHandleFile(cstrCMSUrl & ActiveCell.Value, WIN_NORMAL)
Else
    MsgBox "Non numeric value " & ActiveCell.Value
End If
End Sub

'***************Usage Examples***********************
'Open a folder:     ?fHandleFile("C:\TEMP\",WIN_NORMAL)
'Call Email app:    ?fHandleFile("mailto:dash10@hotmail.com",WIN_NORMAL)
'Open URL:          ?fHandleFile("http://home.att.net/~dashish", WIN_NORMAL)
'Handle Unknown extensions (call Open With Dialog):
'                   ?fHandleFile("C:\TEMP\TestThis",Win_Normal)
'Start Access instance:
'                   ?fHandleFile("I:\mdbs\CodeNStuff.mdb", Win_NORMAL)
'****************************************************

Function fHandleFile(stFile As String, lShowHow As Long)
Dim lRet As Long, varTaskID As Variant
Dim stRet As String
    'First try ShellExecute
    lRet = apiShellExecute(hWndAccessApp, vbNullString, _
            stFile, vbNullString, vbNullString, lShowHow)
            
    If lRet > ERROR_SUCCESS Then
        stRet = vbNullString
        lRet = -1
    Else
        Select Case lRet
            Case ERROR_NO_ASSOC:
                'Try the OpenWith dialog
                varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
                        & stFile, WIN_NORMAL)
                lRet = (varTaskID <> 0)
            Case ERROR_OUT_OF_MEM:
                stRet = "Error: Out of Memory/Resources. Couldn't Execute!"
            Case ERROR_FILE_NOT_FOUND:
                stRet = "Error: File not found.  Couldn't Execute!"
            Case ERROR_PATH_NOT_FOUND:
                stRet = "Error: Path not found. Couldn't Execute!"
            Case ERROR_BAD_FORMAT:
                stRet = "Error:  Bad File Format. Couldn't Execute!"
            Case Else:
        End Select
    End If
    fHandleFile = lRet & _
                IIf(stRet = "", vbNullString, ", " & stRet)
End Function
'************ Code End **********

 

[cheekybuddha]

Do you have any other code in your workbook? Please post it if you have.

Nothing obvious jumps out from the code you have posted that would move your current cell, but also no code is shown that calls CMSConnect() either, so my guess is that the answer lies there.

 

[Gasman]

Hi cheekybuddha,
The CMS connect code is just called from a button on the toolbar.

I select the relevant client CMS number in the sheet and then click the button. That is why I cannot figure out why this is happening.?

 

[cheekybuddha]

Hmmm.... most curious! In that case, I'm afraid I have no idea.

If your workbook is not sensitive then please post and I or someone else here can have a look and try and replicate what's going on.

 

[Gasman]

I'll need to amend the text data and post.

I'll be back as Arne said, but not arnelgp said.

 

[Gasman]

Sorry, forgot all about this.

I have a stripped down workbook as attached.
The fault generally happens when the code runs the first time the workbook opens. After that, it behaves as it should.

Today it jumped to a blank cell before going to the url? and I have a test that the cell is numeric as it use to jump to cell A1 which had a heading.
The macro is CMSConnect in Module 1, and is activated by the far right macro icon, if they show up on the ribbon.?
The function fHandleFile is in module urlFunction.

TIA

 

[cheekybuddha]

I don't know about 'jumping' to an empty cell, but your CMSConnect() function will probably always try and open the CMS page whether the ActiveCell contains a value or not.

Change:

' ...
If IsNumeric(ActiveCell.Value) Then
' ...

to:

' ...
If IsNumeric(ActiveCell.Text) Then
' ...

You may wish to also add some checks that the ActiveCell is on an appropriate column before trying to open the CMS page.

Something like:

Sub CMSConnect()

  Dim blnUrlOpen As Boolean
' Now use a function to bypass login page when already logged in
' ActiveWorkbook.FollowHyperlink cstrCMSUrl & ActiveCell.Value, "", False
' Sometimes cell A1 is selected, so check value is numeric
 
  With ActiveCell
    Select Case .Parent.Name                      ' Check this is a sheet where you can connect from
    Case "2020", "2014", "MT Yearly Sheet"        ' Add applicable sheet names here
      If .Parent.Cells(1, .Column) = "CMS" Then   ' Check column header of ActiveCell is "CMS"
        If IsNumeric(.Text) Then                  ' Test for numeric
          blnUrlOpen = fHandleFile(cstrCMSUrl & .Text, WIN_NORMAL)
        Else
          MsgBox "Non numeric value " & .Text
        End If
      Else
        ' Not a CMS column
      End If
    Case Else
      ' Do nothing
    End Select
  End With

End Sub

hth,

d

 

[CJ_London]

not checked - but is it possible your random cell is the one that had the focus the last time that worksheet was closed or lost focus?

 

[cheekybuddha]

There is a Workbook_Open sub that sets the ActiveCell to the last row in column a of the first sheet. It seems to work as advertised.

I suspect that the user was setting the ActiveCell to an empty cell unwittingly and the original If test wasn't catching it.

 

[Gasman]

Hi all,

Yes normally I would open to the first sheet (whic is normally 'Promised') and goes to the last row for column A.
This is where I most likely need to start from. I had to gut the workbook of all historic data as well as any confidential data to upload it.

However that was added after all this was happening, so sorry for the red herring.

I just opened the workbook again and it jumped to the last used cell in column A (row 48) which holds Client Name.

I then select a CMS number in column B (around row 34) and click the macro icon. This then jumped to A52, and empty cell.

If I select B34 as before it all works, and will continue to work until the next time opened.

It is annoying really, but I was still curious as to why it was happening.

The only user is me , I just use it to get into our CMS system a lot quicker than using it's search method.

Thanks for looking. I was hoping I was not missing something glaringly obvious.

I've just closed it and reopened it and selected the same cell and it went to the same cell again. I tried a cell further up as another test and it still went to A52?

I even commented out that workbook.open code, selected last used cell in column A, saved, exited and re-entered the workbook andon open that last selected cell was selected, but it went to A52 again no matter wich CMS number I used.

Ah well.

 

[cheekybuddha]

How did you add the button to the toolbar?

 

[Gasman]

Normal way?
Customize QAT, then select Macros from the combo and then select the relevant macro.

 

[cheekybuddha]

OK, I tried that. It works as you might expect.

I've no idea what can cause the jump

 

[Gasman]

OK, I have no clue what is going on here.
I added a test for ISEMPTY() and changed the display to non numeric/empty, but only got the non numeric portion.?

Turns out that it is running the macro in the original workbook I saved test as.?

I saved one of the workbooks and then deleted sheets and mended data to leave what I uploaded.
The macro is normally run from each workbook (or at least I thought it was?)

However if I only open the test and run the macro from the button, I end up opening the original and runing that version.?
I tested this by amending the msgbox for each workbook. If I open the QAT customize it only shows macro name without any workbook prefix, BUT if I hover over the one in the QAT it shows as the macro from the original workbook.?

I removed the macro and re added from the test workbook and it works every time.?

I'm off to check the originals and will report back.

 

[Gasman]

Right I think I have cracked it.?

I needed to put the macro into my Personal workbook. I had planned on giving the second workbook to another treasurer, so had the macro in both workbooks.
My first mistake was not realising that only one can exist on the QAT, hence putting it in my Personal workbook.
However even after copying the function used, the code complained about hWndAccessApp when I did that. This is in the apiShell call in Dev's function.
I tried hWndExcelApp, but that was not recognised. , so off googling and found Application.hWnd and that works in Excel at least.

I had obtained this code to use my my Access DB without really looking at it, just used it as it was? Then reaised I could use in Excel and except for this errant jump on first use, worked well.?
Strangely enough the hWndAccessApp property has worked fine in an standard Excel workbook, just not when moved to Personal.?

Just tried to make the code the same in Access, but that does not have an Application.hWnd property, just the hWndAccessApp property, so the code will have to be different.

Well you learn something new everyday (if you are lucky )