Unable to Control Cursor at End of Script

[Joliet_Tech]

I have a script for keeping passwords. The script works with a few anomalies that I would like to address. The first issue is that the cursor does not return to the "user data entry cell" that I have denoted in the script. At the end of the script, it moves down one cell as if I had used the <enter> key. I have tried a few things (e.g. Selecting the cell [c3] that I want the cursor to go to and offsetting the cursor as the last step of the script) that have not worked. The cursor always goes to [c4]. Here is the code:


Sub FindPSWD()
' This sub finds the password associated with the code entered by the user
On Error GoTo MyErrorHandler:
Dim PSWD_Code As String
' Identify the cell that the Password Code is to be entered
PSWD_Code = "c3"
If Len(PSWD_Code) > 0 Then
pswd = Application.WorksheetFunction.VLookup(PSWD_Code, Sheet1.Range("F2:G10000"), 2, False)
' Data is stored in columns F and GElse
MsgBox ("Enter the password code")
End If
Range("d3").Select

ActiveCell.Offset(-1, 0).Select
ActiveCell.Value = pswd
' Range("c3").Select
End Sub

I would appreciate any suggestions. I am using Excel 2016 with Windows 10 Pro

[Paul_Hossler]

1. I added CODE tags to your macro - you can use the [#] to insert CODE ... /CODE tags

2. Putting On Error at the front of the macro hides things; I only bracket statement(s) that could throw an error (e.g. VLookup)

3. Not 100% sure what you're trying to do, but try this



Option Explicit

Sub FindPSWD()

' This sub finds the password associated with the code entered by the user
Dim PSWD_Code As String, pswd As String

' Identify the cell that the Password Code is to be entered
PSWD_Code = "c3"

If Len(Range(PSWD_Code).Value) > 0 Then
On Error GoTo MyErrorHandler
pswd = Application.WorksheetFunction.VLookup(Range(PSWD_Code).Value, Sheet1.Range("F2:G10000"), 2, False)
On Error GoTo 0

MsgBox pswd

Else
MsgBox ("Enter the password code")
End If
Application.Goto Range(PSWD_Code)
MsgBox ActiveCell.Address


Exit Sub

MyErrorHandler:
MsgBox "Error"
End Sub

[Joliet_Tech]

Thank you for your reply Paul. I am embarrassed to say that I submitted a thread on this over a year ago titled: Trouble Selecting Range that was answered with a solution that worked fine. In that solution and Event was used: "Private Sub Worksheet_Change(ByVal Target As Range)" which made the difference.

Thank you again. While this was an important project for me, life got in the way and it had to become a "when I have some time" project and it prompted got forgotten. My apologies again for wasting your time.