Consulting

Results 1 to 5 of 5

Thread: Trouble Selecting Range.

  1. #1

    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.

    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
    Last edited by SamT; 06-29-2017 at 01:26 PM.

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    To correct your 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
    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
    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
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    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?

    ******************************************************
    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

  4. #4
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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)"
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •