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]

Don't know if this will help you or not. Why don't you just have Check Out and Check In columns with dates? If there's no check in date, it's out.

ITEM	SERIAL	MODEL	NOTES	CHECK OUT	CHECK IN	NAME	EMAIL
DSLR'S
DSLR 1	1520 3200 3461	Canon T6i	has lens cap & Canon battery	9/15/23	9/25/23	mike	mike@kkkkkkkk.com
DSLR 2	1520 3200 3464	Canon T6i	has canon battery	9/21/23
DSLR 3	1520 3200 3462	Canon T6i	has lens cap & Canon battery
DSLR 4	missing #	Canon SL2	has lens cap & Canon battery
DSLR 5	1120 7101 2723	Canon SL2	has lens cap & Canon battery
DSLR 6	1120 7101 2721	Canon SL2	has lens cap & Canon battery
DSLR 7	# damaged	Canon SL2	has Canon battery -- paint peeling off flip-out screen
DSLR 8	1120 7101 2724	Canon SL2	has Canon battery
DSLR 9	1120 7101 2686	Canon SL2	has Canon battery
DSLR 10	1120 7101 2679	Canon SL2	has Canon battery
DSLR 11	1120 7101 2681	Canon SL2	has Canon battery

This would also allow you to filter the list and find only those that are out.
EDIT - sorry, I missed that you are trying to create a loan history. Perhaps using the vba sheet change event to copy over cells (not move as you stated)?

 

[lneuberger]

Don't know if this will help you or not. Why don't you just have Check Out and Check In columns with dates? If there's no check in date, it's out.

ITEM	SERIAL	MODEL	NOTES	CHECK OUT	CHECK IN	NAME	EMAIL
DSLR'S
DSLR 1	1520 3200 3461	Canon T6i	has lens cap & Canon battery	9/15/23	9/25/23	mike	mike@kkkkkkkk.com
DSLR 2	1520 3200 3464	Canon T6i	has canon battery	9/21/23
DSLR 3	1520 3200 3462	Canon T6i	has lens cap & Canon battery
DSLR 4	missing #	Canon SL2	has lens cap & Canon battery
DSLR 5	1120 7101 2723	Canon SL2	has lens cap & Canon battery
DSLR 6	1120 7101 2721	Canon SL2	has lens cap & Canon battery
DSLR 7	# damaged	Canon SL2	has Canon battery -- paint peeling off flip-out screen
DSLR 8	1120 7101 2724	Canon SL2	has Canon battery
DSLR 9	1120 7101 2686	Canon SL2	has Canon battery
DSLR 10	1120 7101 2679	Canon SL2	has Canon battery
DSLR 11	1120 7101 2681	Canon SL2	has Canon battery

This would also allow you to filter the list and find only those that are out.
EDIT - sorry, I missed that you are trying to create a loan history.

We have a 24 hour policy so we need to know when it is checked out to enforce it. Just trying to figure out how to make an easy way to mark that it is checked in but then keep a history of who had in case there was an issue.

 

[Micron]

See if you can make this work with some modifications

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim strWhat As String, strWho As String

Set rng = Intersect(Target, Range("E:E"))
If Not rng Is Nothing Then
     If Target = "IN" Then
          strWhat = Target.Offset(0, -3)
          strWho = Target.Offset(0, 2)
          'code to write who & what to some sheet would go here
     End If
End If

End Sub

No idea where you want to put what. Wherever it's going, I just guessed that it might be person name and item serial.

 

[lneuberger]

See if you can make this work with some modifications

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim strWhat As String, strWho As String

Set rng = Intersect(Target, Range("E:E"))
If Not rng Is Nothing Then
     If Target = "IN" Then
          strWhat = Target.Offset(0, -3)
          strWho = Target.Offset(0, 2)
          'code to write who & what to some sheet would go here
     End If
End If

End Sub

No idea where you want to put what. Wherever it's going, I just guessed that it might be person name and item serial.

Thank you. I can semi understand what you did. Just not sure what is the What and Who? Basically, when the pull down is set to IN I want to take the name and email of the row and move it to another sheet, labeled history or something like that.

Also, is there a way if we set that up we can also copy paste something else in the same row to the other sheet?

Long version is when set to IN, Item is copied / pasted to new sheet and name and email is moved to same sheet?

I hope this makes sense.

 

[Micron]

it will take the contents of one or two cells in that row and move it to another sheet

You're going to have to be a lot more specific than that if you want focused answers. Best I can do otherwise is answer with general statements/ideas. The who/what was explained.

No idea where you want to put what. Wherever it's going, I just guessed that it might be person name and item serial.

I now get why you want to move the values but how would this be very useful?

Basically, when the pull down is set to IN I want to take the name and email of the row and move it to another sheet, labeled history

NAME	EMAIL
mike	mike@kkkkkkkk.com
joe	joe@kkkkkkkk.com
sam	sam@kkkkkkkk.com
susan	susan@kkkkkkkk.com
mary	mary@kkkkkkkk.com

 

[lneuberger]

You're going to have to be a lot more specific than that if you want focused answers. Best I can do otherwise is answer with general statements/ideas. The who/what was explained.



I now get why you want to move the values but how would this be very useful?

NAME	EMAIL
mike	mike@kkkkkkkk.com
joe	joe@kkkkkkkk.com
sam	sam@kkkkkkkk.com
susan	susan@kkkkkkkk.com
mary	mary@kkkkkkkk.com

It helps us with inventory. It is for students and if someone checks out a camera and say it is missing the lens cap, we can see who had it last to contact them. Thanks for the help, I am going to play around and see what I can do now

 

[Micron]

Well I don't see how but if you say so. All I see there is who returned something but it doesn't show what it was.

 

[lneuberger]

You're going to have to be a lot more specific than that if you want focused answers. Best I can do otherwise is answer with general statements/ideas. The who/what was explained.



I now get why you want to move the values but how would this be very useful?

NAME	EMAIL
mike	mike@kkkkkkkk.com
joe	joe@kkkkkkkk.com
sam	sam@kkkkkkkk.com
susan	susan@kkkkkkkk.com
mary	mary@kkkkkkkk.com

OK, I got it working where it copies the name to the other sheet. WHat I need help with now is 2 things.

1. How do I set it to move the name instead of copy it?
2. How do I make it so everytime a new name is entered and the value is set to IN, the name moves to the next cell down in the new sheet?

I am so close, just need help to the finish line. I appreciate the help. Here's a link to the file

Book1.xltm

Shared with Dropbox

www.dropbox.com

 

[Micron]

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?