-
Trouble Selecting Range.
I have a macro that I wrote several years ago that works but I don't know how! Ok, yes, I am a very part-time vba coder. This script runs by entering a 5 digit number in a cell (C3) in my spreadsheet. I did not use a InputBox. It then returns an alphanumeric code associated with it. The result is in cell (D3). I am using the VLOOKUP function. The cursor ends up in the cell below the user entry cell and I want it to be returned to the original, user entry cell (C3). As I said the code functions. If I step through the code with the Debugger, I get errors. Here is my code.
Code:
Sub FindPSWD()
' This sub finds the password associated with the code entered by the user
Dim PSWD_Code As String
Dim PSWD As String
' Identify the cell that the Password Code is to be entered
PSWD_Code = "c3"
PSWD = Application.WorksheetFunction.VLookup(PSWD_Code, Sheet1.Range("F2:G10000"), 2, False)
' Move the cursor back to C3
ActiveCell.Offset(-1, 0).Select
Range("c3").Select
End Sub
-
To correct your code
Code:
Sub FindPSWD() ' This sub finds the password associated with the code entered by the user
Dim PSWD_Code
' Identify the cell that the Password Code is to be entered
PSWD_Code = Range("c3").Value
On Error Resume Next
Range("D3").Value = Application.WorksheetFunction.VLookup(PSWD_Code, Range("F2:G10000"), 2, False)
If Err.Number <> 0 Then MsgBox "Not Found"
' Move the cursor back to C3
Range("C3").Select
End Sub
To simplify use
Code:
Sub FindPW(Target) Dim PW As Range
Set PW = Sheet1.Columns(6).Find(Target)
If Not PW Is Nothing Then [D3] = PW.Offset(, 1)
Range("C3").Select
End Sub
To "automate", put this in the worksheet module. it will run when code is entered in C3
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim PW As Range
If Target.Address <> "$C$3" Then Exit Sub
Application.EnableEvents = False
Set PW = Sheet1.Columns(6).Find(Target)
If Not PW Is Nothing Then
[D3] = PW.Offset(, 1)
Else
MsgBox "Not Found"
End If
Range("C3").Select
Application.EnableEvents = True
End Sub
-
Thank you so much. I implemented the "correct my code" changes to try to understand it better. It still retrieved the password but the cursor still moves down to C4.
Why this change? Range("D3").Value I think that this just saves using another variable but more importantly puts the answer in D3. Is that correct?
If I am correct, how did the previous macro know to put the answer in D3?
For some reason the Range("C3").Select does NOT put the cursor back in C3. I use this same code in another part of the code that allows a user to store a new password. Below is that code FWIW.
Finally, how is the macro activated upon the user entering the password code into C3? What is the function or mechanism?
******************************************************
Code:
Private Sub CommandButton21_Click()
' This script records the new password
Worksheets("sheet1").Activate
Dim id
Dim idnew
Dim PSWD
' Unprotect the spreadsheet to allow moficication
' ActiveSheet.Unprotect Password:="wood3481"
PSWD = Application.InputBox("Enter New Password")
' Find the end of the password codes column
Range("f1").End(xlDown).Select
' Remember last code assigned
id = ActiveCell.Value
' Move down 1 cell which is where new code will be stored
ActiveCell.Offset(1, 0).Select
' Write new code to database
ActiveCell.Value = id + 1
idnew = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = PSWD
MsgBox ("Here is your new password ID: " & idnew)
Range("c4").Value = idnew
' Range("c4").Locked = True
' Protect the spreadsheet before returning teh unlocked cell
' ActiveSheet.Protect Password:="wood3481"
' Move cursor back to Input cell
Range("c3").Select
'Macro is digitally signed by CEB
'Added password to lock the code on 6/2/2016
'Cleaned up old notes, added relavent coding notes
End Sub
-
Private Sub Worksheet_Change(ByVal Target As Range)Can you post a sample workbook showing the relevant sheets. I'm not clear if C3 and D3 etc. are on the password list sheet or elsewhere and I suspect there may be other code, ptherwise how is FindPSWD run?
The automated code is triggered by an "Event" macro. In this case a change to the value of C3 on a particular sheet uses the built in routine
"Private Sub Worksheet_Change(ByVal Target As Range)"
-
First of all, 1000 apologies for having never answered your post from over a year ago. Things got crazy in my life and I just put this project on the back burner -- until today.
I implemented your Automate solution and it worked great. Thank you thank you. Now, I'd like to understand why.
C3 and D3 are in the same sheet as the script. The user enters a code into C3. The passwords are stored on the same sheet as well.
It must have to do with using the Event macro: "Private sub Worksheet_Change(ByVal Target As Range)".
I did not mention in previous posts that I had created a worksheet module (unintentionally, it is the default I think) which had other code in it. I can't find where/how to attach a copy or I would. There are 3 code sets in the module. I don't think it had anything to do with it as your Automate solution worked.
