Macro - Remove Entry and then Auto Populate w/ generic template info

[Oranjin]

A

B

C

D

E

F

G

H

I

J

K

L

15

(None)

Department

Teams

660 Floor

660 Quad

samaccountname

Seat Type

First Name

Last Name

Display Name

Station Location

Workstation Power Port

16

(None)

Finance

B

4

NW

Abe.Lincoln

Support -Sr. Acct

Don

Trump

Abe Lincoln

4026

West 64

17

(None)

Marketing

K

4

SE

Mark.Zuckerburg

Analyst

Mark

Zuckerburg

Mark Zuckerburg

4034

East 64

18

(None)

Open

4

NW

Open Slot

Open Slot

4030

West 86

19

(None)

20

(None)

<tbody>
</tbody>

Hello,

I want to write a macro that allows me to look up someone, and remove them from this chart, and repopulate the row with generic, blank information. So - I want to create a text entry field that the macro will then follow. Let's say I enter, "Abe Lincoln" into the text entry field to remove him from the sheet. I select, "activate macro" - I then want to hit and have all the text removed from row 16 (based off of the text in column J) and then have it populate row 16 to look like row 18.

I'm not even sure where to begin with this. Any tips on what to look for, where to search, would be great.

 

[JLGWhiz]

Assume you have a cell which you name as "Entry" in the name manager. Then with the worksheet event code below, when somebody enters a name in that field it does what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim rw As Long, fn As Range
Application.EnableEvents = False
    If Not Intersect(Target, Range("Entry")) Is Nothing Then
        Set fn = Range("J:J").Find(Target.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                rw = fn.Row
                Range("B" & rw).Resize(1, 11).ClearContents
                Range("B" & rw) = "Open"
                Range("D" & rw) = "4"
                Range("E" & rw) = "NW"
                Range("G" & rw) = "Open Slot"
                Range("J" & rw) = "open Slot"
                Range("K" & rw) = "4030"
                Range("L" & rw) = "West 86"
            End If
    End If
Application.EnableEvents = True
End Sub

To install the code, right click the worksheet name tab on the sheet where your data resides. Click 'View Code' in the pop up menu and then copy and paste the code into the large pane of the VB editor window that opens. If not already done, close the VB editor and save your workbook as a macro enabled workbook (.xlsm). The code should now run when you make an entry into the cell named "Entry" If the code does not run, check to be sure you are not in design mode which is indicated by the design icon (triangle, pencil and ruler). If the icon is not on your Excel ribbon, open the VB Editor, Alt + F11, and check it there. If you are not in design mode. Install this snippet in the standard code module and run it to reset the event trigger.

Sub t()
Application.EnableEvents = True
End Sub

 

[Oranjin]

Wow.

That's pretty incredible. I'm having a couple of issues with it.

[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]If Target.Cells.Count > 1 Then Exit Sub

                Range("D" & rw) = "4"
                Range("E" & rw) = "NW"
                Range("K" & rw) = "4030"
                Range("L" & rw) = "West 86"
            End If
    End If
Application.EnableEvents =

So for those columns, I don't want "4" and "NW" and 4030 and West 86 to be the generic text. I want those columns to STAY as they were prior to the entry of the name. those rows will stay the same regardless of what name is entered. The sheet I'm working with is not organized as it should be. It is organized around physical desk location. So changing the desk location will be an issue. How do I make it say, when removing the name, leave the above rows (listed) as they were?

Thanks so much. This is an pretty incredible macro here. I've just started to delve into the mysteries of making macros actually work. Thanks so much

Steve

 

[JLGWhiz]

Replace the first macro with this one.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim rw As Long, fn As Range
Application.EnableEvents = False
    If Not Intersect(Target, Range("Entry")) Is Nothing Then
        Set fn = Range("J:J").Find(Target.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                rw = fn.Row                
                Range("B" & rw) = "Open"                
                Range("G" & rw) = "Open Slot"
                Range("J" & rw) = "open Slot"               
            End If
    End If
Application.EnableEvents = True
End Sub