Excel Newb needs help with VBA

[lneuberger]

Hi everyone. I am trying to create a check in / check out system in excel for equipment. I created a pull down menu for IN and OUT. I am trying to figure out how to make it so that when IN is selected it will take the contents of one or two cells in that row and move it to another sheet. Basically trying to keep a history of who has checked something out.

I hope this is enough information. I attached a sample sheet in case you want to see. I appreciate any help.

test sheet.xlsx
	A	B	C	D	E	F	G	H
1	ITEM	SERIAL	MODEL	NOTES	CHECK OUT	CHECK OUT DATE	NAME	EMAIL
2
3	DSLR'S
4	DSLR 1	1520 3200 3461	Canon T6i	has lens cap & Canon battery	IN		mike	mike@kkkkkkkk.com
5	DSLR 2	1520 3200 3464	Canon T6i	has canon battery
6	DSLR 3	1520 3200 3462	Canon T6i	has lens cap & Canon battery
7	DSLR 4	missing #	Canon SL2	has lens cap & Canon battery
8	DSLR 5	1120 7101 2723	Canon SL2	has lens cap & Canon battery
9	DSLR 6	1120 7101 2721	Canon SL2	has lens cap & Canon battery
10	DSLR 7	# damaged	Canon SL2	has Canon battery -- paint peeling off flip-out screen
11	DSLR 8	1120 7101 2724	Canon SL2	has Canon battery
12	DSLR 9	1120 7101 2686	Canon SL2	has Canon battery
13	DSLR 10	1120 7101 2679	Canon SL2	has Canon battery
14	DSLR 11	1120 7101 2681	Canon SL2	has Canon battery
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
E4	List	=$K$2:$K$3

 

[Micron]

Maybe this?

Option Explicit 'IMO this should be at the top of every module

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, wsht As Worksheet
Dim Lrow As Long

On Error GoTo errHandler
Application.EnableEvents = False 'optional

Set rng = Intersect(Target, Range("E:E"))
If Not rng Is Nothing Then
     If Target = "IN" Then
          Set wsht = Sheets("list")
          With wsht
               Lrow = .Range("A" & .Rows.Count).End(xlUp).Row
               rng.Offset(0, 2).Copy .Range("A" & Lrow + 1)
          End With
          rng.Offset(0, 2) = ""
     End If
End If
exitHere:
Application.EnableEvents = True
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere
End Sub

I usually use an error handler when altering application settings lest they remain altered after a runtime error.

 

[lneuberger]

Took a look. Doesn't look right to me. You should be running the code based on the choice of in or out, no? That would be column E as I showed you.
Simple enough to copy as you've done, then make the same cell = "". However, that would not be G4, it would be an offset of the target cell (whichever one was set to in or out) in the same row. What happens when G5 is set to IN? Your target value in the other sheet will be wiped out because G4 value was removed already.

What happened to the other values you wanted to copy over?

I see what you are saying. I will mess with it more. I decided on just doing the name because I was having issues and really that is all I need, a history of the names. I can make labeled columns for each piece of equipment and adjust the code so the name goes in the right spot. I will keep working on this but I am finally starting to get this. Thanks for the help

 

[Micron]

The revised code I posted does what you want in your file - assuming all you want is a list of names - so why are you still struggling with this?

If a camera comes back with a missing lens cap, how does the data example I put in post 6 tell you anything? Can you explain how you know which row tells you who lost it?

 

[lneuberger]

The revised code I posted does what you want in your file - assuming all you want is a list of names - so why are you still struggling with this?

If a camera comes back with a missing lens cap, how does the data example I put in post 6 tell you anything? Can you explain how you know which row tells you who lost it?

THanks for all your help. It is working now but one last question, I hope. I have say 20 cameras labels DSLR 1 -- 20. On my history sheet I have columns labeled DSRL 1 - 20. How can I set it some when someone checks in DSLR 10 It goes to the DSLR 10 column on this history sheet? Right now everything would list in one column. I attached the working file below -

Book1.xltm

Shared with Dropbox

www.dropbox.com

 

[Micron]

Do yourself a favour - put the item and person name (and whatever else you need) in rows, not columns.

Item

Name

Check In Date

DSLR 1

Joe

9/21/23​

DSLR 5

Sam

9/21/23​

DSLR 3

Mary

9/23/23​

DSLR 2

John

9/24/23​

DSLR 1

Joe

9/25/23​

Items come and go, and to facilitate that you will have to add/remove columns if you list the items across the sheet. If you do that you'll mess up code that uses Offset function. Rows will also allow you to filter and sort and will be more efficient in terms of file size. Your way, if a person only borrows and returns only one item, you end up with a row with 19 empty Item columns and one Item column with data.

 

[lneuberger]

Do yourself a favour - put the item and person name (and whatever else you need) in rows, not columns.

Item

Name

Check In Date

DSLR 1

Joe

9/21/23​

DSLR 5

Sam

9/21/23​

DSLR 3

Mary

9/23/23​

DSLR 2

John

9/24/23​

DSLR 1

Joe

9/25/23​

Items come and go, and to facilitate that you will have to add/remove columns if you list the items across the sheet. If you do that you'll mess up code that uses Offset function. Rows will also allow you to filter and sort and will be more efficient in terms of file size. Your way, if a person only borrows and returns only one item, you end up with a row with 19 empty Item columns and one Item column with data.

OK, that makes sense but not sure how to do it. I looked up the Offset function but am not sure where to use it or how. If I do it ths way, how do I get it to Copy the item and name to a row in the new sheet but do it incrementally so I do not overwrite anything?

 

[Micron]

how do I get it to Copy the item and name to a row in the new sheet but do it incrementally so I do not overwrite anything?

This was handled in post 11 and post 4 code:

rng.Offset(0, 2).Copy .Range("A" & Lrow + 1)

Function is Offset(row,column)
so that code stays on the same row and offsets 2 columns to the right. The copy part copies that to the next row after the last row with data (Lrow + 1) in column A on the list sheet because that's what you asked for. If you want other values in the row as well, repeat this line for as many times you want to copy. Example, copy from Name and Serial:

rng.Offset([B]0, 2[/B]).Copy .Range("A" & Lrow + 1)
rng.Offset([B]0, -3[/B]).Copy .Range("B" & Lrow + 1)

 

[lneuberger]

This was handled in post 11 and post 4 code:

rng.Offset(0, 2).Copy .Range("A" & Lrow + 1)

Function is Offset(row,column)
so that code stays on the same row and offsets 2 columns to the right. The copy part copies that to the next row after the last row with data (Lrow + 1) in column A on the list sheet because that's what you asked for. If you want other values in the row as well, repeat this line for as many times you want to copy. Example, copy from Name and Serial:

rng.Offset([B]0, 2[/B]).Copy .Range("A" & Lrow + 1)
rng.Offset([B]0, -3[/B]).Copy .Range("B" & Lrow + 1)

You are the best! Thanks for tolerating my coding stupidity. This is perfect

 

[Micron]

No problem, glad to have been able to help. Post back if you get stuck. If you have a working solution, please select a post as the solution to your thread.
Thanks.