Error in code

[Jietoh85]

Hello All!

I'm working on a VBA code that will work in conjunction with several others to create a user interface to add / edit / and remove contacts on an Employee Directory. The issue I'm having right now is with the edit portion. What I want the code to do is (1) find a specific contact based on preset criteria (working), (2) add the text "Edit" to column A of that row indicating that row as the target to be edited (not working), (3) and then open a UserForm that pulls all of the information from the row into the correct fields (partially working), and (4) once changes have been made in the UserForm clicking the 'submit' button will push the changes back to the spread sheet updating the target row (not working).

My problems are this: (1) the code I wrote for step # 2 does not work, but I cannot figure out why, (2) I can get the UserForm to open, but not all of the fields pull the information. Could this be because the UserForm is a combination of textboxes and listboxes? And finally (3) right now when I click submit the UserForm just adds a new row instead of updating the old. I think this can be resolved with a line that designates the insertion point as the row with the text "Edit" in column A and simply overwriting the data.

Here is the code that finds the correct contact, puts "Edit" in Column A of that row, and opens the UserForm.

Sub Edit_Employee_Contact1()
'Last Name

    Dim MyCell, Rng As Range
    Set Rng = Sheets("Faculty & Staff List").Range("T2") '''''sets the range to use
    On Error GoTo ErrMsg

    Columns("C:C").Select
    Selection.Find(What:=Range("T2"), After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=True, SearchFormat:=False).Activate

'This part is supossed to put "Edit" in Column A, in this case 2 columns to the left of the active cell
        Acticecell.Offset(0, -2).Select
        ActiveCell.FormulaR1C1 = "EDIT"

        ActiveCell.EntireRow.Select
        EditContact2.Show
        Exit Sub
        
ErrMsg:
    MsgBox ("Employee Does Not Exist")
End Sub

This is the code behind the UserForm EditContact2

'This part of the code should pull the data straight from the active row and insert into the corresponding text or list box
Private Sub UserForm_Initialize()

Dim RW As Long
RW = ActiveCell.Row
    Me.TextBox1.Value = Cells(RW, 2)
    Me.TextBox3.Value = Cells(RW, 3)
    Me.TextBox2.Value = Cells(RW, 4)
    Me.TextBox4.Value = Cells(RW, 5)
    Me.ListBox1.Value = Cells(RW, 6)
    Me.ListBox2.Value = Cells(RW, 7)
    Me.ListBox3.Value = Cells(RW, 8)
    Me.TextBox5.Value = Cells(RW, 9)
    Me.TextBox6.Value = Cells(RW, 10)
    Me.ListBox4.Value = Cells(RW, 11)
    Me.ListBox5.Value = Cells(RW, 12)

'There are additional elements to the Initialize sub that load each of the List boxes with their full range of options, but I did not include them.  
End Sub

Private Sub CommandButton1_Click() 'Add New Contact
'This is the code that runs when the 'Update' button is clicked

Dim ws As Worksheet
Dim rngNext As Range Set ws = Worksheets("Faculty & Staff List") ' change for the worksheet you want data to go to
 
 
'This is the Line that I think needs to be tweeked to fix the 3rd problem
      Set rngNext = ws.Range("B2").End(xlUp).Offset(1)

 
       rngNext.Value = TextBox1.Value ' column B
 
       rngNext.Offset(, 1).Value = TextBox3.Value ' column C
 
       rngNext.Offset(, 2).Value = TextBox2.Value ' column D
 
       rngNext.Offset(, 3).Value = TextBox4.Value ' column E
      
       rngNext.Offset(, 4).Value = ListBox1.Value ' column F
      
       rngNext.Offset(, 5).Value = ListBox2.Value ' column G
      
       rngNext.Offset(, 6).Value = ListBox3.Value ' column H
      
       rngNext.Offset(, 7).Value = TextBox5.Value ' column I
      
       rngNext.Offset(, 8).Value = TextBox6.Value ' column J
      
       rngNext.Offset(, 9).Value = ListBox4.Value ' column K
      
       rngNext.Offset(, 10).Value = ListBox5.Value ' column L
      
       rngNext.Offset(, 11).Value = TextBox7.Value ' column M
 
 
       Unload Me
       Application.Run "'Master List.xlsm'!New_Employee_Contact"
 
       EditContact.Hide
End Sub

Thanks in advance for your help and guidancce.

 

[Jerry Sullivan]

Hi and Welcome to the Board,

Here's some feedback on your first question, I'd be glad to help with the other parts once you have this working.

There's a typo in one statement of your code- ActiveCell is misspelled

Acticecell.Offset(0, -2).Select

Not sure if that happened while posting, but if it's in your actual code then it would cause an error that would be masked by your error handler.
Using Option Explicit and compiling your code will catch many errors like that prior to runtime.

With that fix, your code would probably work; however it could be improved by eliminating the use of Select, Selection, Activate and ActiveCell and instead just referencing objects without selecting or activating them....

Sub Edit_Employee_Contact2()
    Dim rFound As Range
    
    With Sheets("Faculty & Staff List")
        Set rFound = Columns("C:C").Find(What:=.Range("T2"), _
            After:=.Range("C1"), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=True, _
            SearchFormat:=False)
        If rFound Is Nothing Then
            MsgBox ("Employee Does Not Exist")
            Exit Sub
        End If
        .Cells(rFound.Row, "A").Value = "EDIT"

    End With
    EditContact2.Show
 End Sub

Placing the word "EDIT" in your worksheet, probably isn't the cleanest approach, since it gives you an extra item to clean up however, it's fine to start out with this method and perhaps it can be replaced as your code is refined.

One other note. When declaring more than one variable in the same Dim statement, you need to declare the data type of each variable or else the default Variant is used.

This statement will cause MyCell to be declared as the default data type Variant because no type is specified.

Dim MyCell, Rng As Range

To declare both variables as Ranges use..

Dim MyCell as Range, Rng As Range

 

[Jietoh85]

Jerry,

Good spot on that Activecell. That was the issue. Once I fixed that (and cleaned up a few other pieces) the code works great.