Drop Down Arrow Changes - Todays Date Is Created In Cell

**Antligen** · 04-22-2015, 05:36 AM

tester neb.xls

Here is an attached Sample of my workbook.

I've got equipment that comes and goes, I like to keep a track of it by simply using a drop down arrow list and selecting whether equipment has been sent off for repair, on loan etc.

When I change the selection in the drop down list, the column next to it puts todays date in it, so that I can log when the update was made.
I've had to delete a few rows, as equipment has been decomissioned etc. Now that I've down that, the formula isn't working anymore and I can't understand why. I'm a complete novice with VBA, so laymans terms please!

Would anyone be willing to take a look at my formula and streamline it so that I can easily add or remove new rows in my table? Whilst also being able to change my selection in the lists and have todays date appear in the adjacent column?

Many thanks to all on here that have the time to help.

**LokeshKumar** · 04-22-2015, 06:06 AM

Hi,

Your code should look like this

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B13:B34")) Is Nothing Then Exit Sub

Target.Offset(0, 1).Value = Date
Application.EnableEvents = True

End Sub

There is a list in Range "B13:B34" so in case you increase or decrease "Location" then adjust accordingly....
If you get an error then pls remove the library (it will always start with Missing) .....however I am attaching the sheet for your reference....tester neb.xls

If your question is resolved, mark it SOLVED using the thread tools. Click on the star if you think some-1 helped you.

**Antligen** · 04-23-2015, 04:21 AM

Hi,

It doesn't seem to be working. I don't understand why the range is B13:B34? The last row is on B26?

Also, I've tried adding this code to my spreadsheet and it hasn't worked. Again I'm a complete novice at this, it would be very handy if you could show me where this code needs to be place so that it.

As soon as I add/remove a row it messes up the formula and comes up with a debug?

Perhaps the way inwhich I've set this up is a little bit too complicated for me to handle!

Basically all I'm after is a simple spreadsheet that can easily add/remove rows, have drop down lists in the Location column and when a list is changed, todays date is put into the adjacent cell in the Since (Date) column.

If there's a more simple, efficient way of a novice doing this, I'd be much appreciated.

Thanks again

**LokeshKumar** · 04-23-2015, 05:18 AM

Hi,

It doesn't seem to be working. I don't understand why the range is B13:B34? The last row is on B26?

Yes you are right it should be

Range("B13:b26"))

Image......Attachment 390851
As per this image just click on Sheet3 on then click on View code and paste the code.......after changing the range as required.

Drop Down Arrow Changes - Todays Date Is Created In Cell

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Drop Down Arrow Changes - Todays Date Is Created In Cell

Re: Drop Down Arrow Changes - Todays Date Is Created In Cell

Re: Drop Down Arrow Changes - Todays Date Is Created In Cell

Re: Drop Down Arrow Changes - Todays Date Is Created In Cell

Thread Information

Users Browsing this Thread

Similar Threads

Different calculation in the same cell using a drop down arrow

Opening a drop down list on the value defined by todays date.

Using VB to find a range based on todays date and todays date +30

in-cell drop-down arrow missing

[SOLVED] Drop-down arrow only visible when cell selected

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions