Free Microsoft Excel 2013 Quick Reference

Auto insert date, time & user in columns when data is entered in another column?

I have been looking for a macro that will automatically insert the date in column a, time in column b, and userid in column c, when date is entered into column d. Can someone help me write this? I would greatly appreciate it. I believe it is a Workbook_Change Event but I am having trouble writing the code to reflect the variables involved. Any input would be greatly appreciated. Thanks!
SHEL


I need to auto insert date/time in column A when an area is picked from
pulldown in column B. I have this: =IF(COUNTA(B45)0,NOW(),"") it works but
it updates all previous in column A.

I'm looking for a timestamp to automatically appear in column D when data is entered in columns A, B, and C (so nothing appears if all 3 cells are not filled). In addition, I want those 3 cells to become password protected once data is entered.

This is what I have in column D:
=IF(AND(NOT(ISBLANK(A4)),NOT(ISBLANK(B4)),NOT(ISBLANK(C4))),NOW(),"")

I have managed to get all of this to work out, except that the timestamp all have the same time and updates with the real-time instead of when it was entered.

I have read somewhere that I may need to go to, Tools => Options => Calculation and check the Iteration Box. I have also read somewhere that I may need to go to, Data => Validation => Settings, select List under Allow and select the timestamped cells under Data. Neither have worked!

I've attached the .xls. Please help! Thanks in Advance.

OK, first post for me, be gentle. I am trying to automatically populate a cell with todays date when data is entered (or changed if that's possible) to an adjoining cell, and blank if the adjoining cell has no data. Just a list I want to track when a field is updated, without having to manually populate the date field beside the data, ie

A B C
Date updated
Salesperson 1 Neil 01/02/08
Salesperson 2 (blank)
Phone number 02 === 16/02/08
Fax number 02 === 16/02/08

I found the 'Creating formulas that only return results from non-blank cells which can get me part of the way, but no idea how I can get a 'todays date' return.

Anyone?

Hi,

i attached my sample file to help make things easier. what i hope to do is
1) when data is entered through the userform, it will be sorted according to their expiry dates in ascending order.
2) there is a formula in the "balance" cell which i hope to auto fill to the next row when data is entered.

Thanks in advance

Can Excel recognize when data is entered into a cell and then apply the
appropriate formulas from the cells above? I have a spreadsheet set up for
people to use but some of the cells are locked and I don't want to print the
whole worksheet until info is entered into cells and then the formulas be
applied.

Hi there,

I need to create a spreadsheet where I want to force the user to enter data in the right order.

So, for example, I want cell B5 to be locked UNTIL data is entered in A3, A4 and A5, BUT for the remainder of locked cells - column C say - to stay locked.

I recently found this answer to a similar problem:

"The first thing you need to do is to protect the worksheet. By default all cells are "locked". Select the cell(s) you wish your user to enter data into. Right click and choose Format Cells. In the Format Cells window select the Protection tab. De-select the Locked checkbox and click OK.

I usually fill the user data entry cell with the pale yellow color to draw the users eye to that cell(s). With the desired cell unlocked, go to the Tools menu and select Protection - Protect Sheet... You can accept the default and click OK. For this example I did not select any other attributes nor added a password.

Open the VBA Editor (Alt+F11) and on the right in the Project-VBAProject pane locate your Workbook in the tree. Open the Microsoft Excel Objects and select your Worksheet.

Paste this code in the code pane:

Private Sub Worksheet_Change(ByVal Target As Range) 
If Not Intersect(Target, Range("B5")) Is Nothing Then 
ActiveSheet.Unprotect 
End If 
End Sub
Note: change
to reflect your user cell. 

Still in the VBA Editor in the Microsoft Excel Objects for your Workbook locate ThisWorkbook. Paste this code in the code pane:

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
ActiveSheet.Protect 
End Sub
When the WB is closed it is set back to Protected.

Save the Workbook"

However, this turns off protection for the entire sheet once data has been entered.

Does anyone know how to change this so that only a certain range of cells are unlocked, not eveything?

Thanks very much for any help!

Hi,

I need to get Column locked automatically once the data is entered. I have attached the sheet here in that when the stop button clicked end time will come in E5, then only F5 to H5 should get locked automatically, Similarly next time when end time comes in E6 then F6 to H6 get locked along with E5 to H5...Can i get any suggestion/help on this?

Hi Everyone,

I'm putting together some vb code to change the colours of the bars or lines
on a chart in one go. I've set it up so far for when the data is in rows (ie
the individual series lie along the x axis). However, I need to consider how
to cycle through the data points for when it is in columns, which essentially
means I need to search for xlcategory names and the relavent points (I
think). I suppose I also need to consider other types of charts and so would
also be grateful for any guidance in how to best do this (as I presume you
can't use the 'interior' object for when using lines!).

Here's the code I've written which works fine for when data is in rows.
Below also is the data I've used.

-----------------------

Sub SetChartColoursForMults()

Dim Chartobj As Chart
Dim s As series
Dim pt As Point

On Error GoTo error_it
Chartname = ActiveChart.Name
On Error GoTo 0

Response = MsgBox("Please make sure you have selected the chart to change
the series colours." & Chr(10) _
& "The following Multiples will be set to the following [excel]
colours:-" & Chr(10) & Chr(10) _
& "Tesco Blue" & Chr(10) _
& "Sainsbury Orange" & Chr(10) _
& "Asda Bright Green" & Chr(10) _
& "Morrisons Black" & Chr(10) _
& "Safeway Red" & Chr(10) _
& "Somerfield Turquoise" & Chr(10) _
& "Kwik Save Pink" & Chr(10) _
& "Waitrose Green" & Chr(10) _
& "Iceland Tan" & Chr(10) _
& "C&I Gold", vbOKCancel, "Set Chart Colours for Multiples")
If Response = vbCancel Then Exit Sub

With ActiveChart

For Each s In .SeriesCollection
seriesname = s.Name

Select Case seriesname 'for when data is in rows
Case "Tesco": NewCol = 5 'Blue or could use vbblue
Case "Sainsbury": NewCol = 46 'Orange
Case "Asda": NewCol = 4 'Bright Green
Case "Morrisons": NewCol = 1 'Black
Case "Safeway": NewCol = 3 'Red
Case "Somerfield": NewCol = 28 'Turquoise
Case "Kwik Save": NewCol = 26 'Pink
Case "Waitrose": NewCol = 10 'Green
Case "Iceland": NewCol = 40 'Tan
Case "C&I": NewCol = 44 'Gold
End Select

With s.Interior
.ColorIndex = NewCol
.Pattern = xlSolid
End With

' plus need to check for columns when data is in coloumns
' hmmm how do i do this?!

Next

End With

Exit Sub

error_it:
Select Case Err
Case 91
MsgBox "Please select a chart!", vbOKOnly, "Set Chart Colours for
Multiples"
Case Else
MsgBox "There is a problem! Error code: " & Err & Chr(10) &
Err.Description
End Select

End Sub

-------------------

Data:

Multiples sales '04 sale '05
Tesco 23 34
Sainsbury 34 56
Asda 56 56
Morrisons 68 45
Safeway 65 23
Somerfield 32 34
Kwik Save 34 56
Waitrose 56 68
Iceland 56 65
C&I 45 32

Thanks in advance for your help.

<<<<<<<<<<<<STOP PRESS>>>>>>>>>>>>>

Hiya,

Update for you all,

I've managed to get a bit further on the code ie testing for xlrows /
xlcolumns by using the plotby command. However I'm still stuck on getting
the corresponding x axis category label / value for that particular point.
Can anyone help?

Many thanks

RodP

Hi all
Have attached a small 2007 excel spreadsheet to help explain my needs.
I would like to know if there is a function that when data is entered into column A it is then transfered to column B, B data transfers to C, C to D, D to E etc. with the K data falling off (10 Columns). I need this process to happen even when the new A input data is of the same value as the last A input data.
Thanks

As in my previous question I require that zero values only show when data is
entered in adjoining fields. What I failed to mention is that the field that
I want the zero values to show up in is a calculation (=SUM(C2-F2)+D2). How
do I combine your suggestion with my calculation?

Please let me know if you come up with a solution for my print problem as
well.

Thanks
Happy New Year

I want to automatically change the colour of a cell in Excel when data is
added to that cell. For example: an empty cell has a blue backcolor when a
number is put into the cell the background automatically changes to white.

Is it possible to automatically enter an equation only when the relevant row of data has been entered. For example I a have a document with many equations in it but I have them copied down five hundred rows regardless of whether data is entered or not.

The data can be entered by any number of people, so when the document reaches the end of its life and is due to be saved it gets saved with the 500 rows of equations in it whether they are used or not.

If myself is the last person to make an entry I unlock the sheet delete the equations where relevant and then secure it again. This is to save space on the drive.

Is this possible

PS the cells must be locked and secured where the formulas are enterd if it is at all possible.

Hello Everyone,
I have a formula in cell A4 that multiplies cell A1,A2 & A3 together but if data is entered into the cell I lose the formula. Is there any way to get around this.

Thanks
kdilas

Hi,

On once worksheet i have 4 graphs one above the other. On another
worksheets there is a table to enter data relevent to each graph.

What im looking to do is that when the data is entered on the other
workesheet it either unhides a cell under the respective graph showing
the comments or it adds a new row with the data present.

Is this possible?

Best Regards

Ian Grey

--
greyo
------------------------------------------------------------------------
greyo's Profile: http://www.excelforum.com/member.php...o&userid=26053
View this thread: http://www.excelforum.com/showthread...hreadid=393974

Hi,

On once worksheet i have 4 graphs one above the other. On another worksheets there is a table to enter data relevent to each graph.

What im looking to do is that when the data is entered on the other workesheet it either unhides a cell under the respective graph showing the comments or it adds a new row with the data present.

Is this possible?

Best Regards

Ian Grey

Hi,
I'm using this code. But i found the date+time is inserted eventhough i had deleted A2 value.
I guess this date+time is inserted whenever there is "changes" in the cell.
Can i fix the coding to allow for date+time insertion when there is a value?

Private Sub
Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

        If Not Intersect(Target, Range("A2:A100")) Is Nothing Then

            With Target(1, 2)

                .Value = Now

                .EntireColumn.AutoFit

            End With

        End If
End Sub
Thanks.

Does anyone know a code that will insert an auto date into a specified field when data is entered into other fields. I need a sheet to enter today's date when the user begins filling out the form.

I am using Microsoft Office Pro 2003, and am working with a spreadsheet that
is located in a shared directory accessible to nearly all users at the
company for which I work. The computer literacy of the employees ranges from
'zero experience' to 'expert', so I don't have the luxury of having all
options available to me with this spreadsheet. For example, the problem I'm
having, I decided to solve with creating a macro. Unfortunately, the default
security setting is 'High' for all users, and I simply can't ask everyone to
lower their security settings. So, macros are out of the question, I'm
guessing.

Anyway, what I'm trying to do is: Column D of the spreadsheet is a
"description" column, and Column C of the spreadsheet is the "date" column.
I am looking to have this spreadsheet automatically enter just the date in
column C when data is entered into column D. I need the date to be entered
into the cell in column C directly to the left of the cell in Column D in
which they're entering a description. There are several catches, though. I
don't want this to be a volatile function, to where the date is re-entered
every time the spreadsheet is opened. I have to be able to track when the
data was *actually* entered. Additionally, I need to protect Column C from
manual entry, while still allowing the date to be automatically entered when
a description is entered into column D. Also, I need this to be able to work
for all users, with High security settings, without requiring password input.

Basically, I just need to make sure the date is automatically recorded,
ensure they cannot alter the date (I can't allow back-dating), and make sure
the date *never* changes once automatically entered. If I think of anything
I've left out, I'll post again.

Thank you for your time, everyone. After staring at this for a few hours,
I'm a bit desperate.

Hello,
I am new to this forum. I have learned a lot from this site and thank everyone for that. I have searched this site for my answer and can't find.

I have used this code below(Credits to daniel.c) in my spreadsheet, and it works wonders. What I don't like, and can't figure out how to stop is the following.

Column A is where my date/time updates.

Row 10: I want to delete. (This row has data I no longer need)
Row 11: This row also has data.

When I delete Row 10, Row 11 obvisously now becomes Row 10. The problem is that Row 11 already had a date/time stamp in Column A.

Can I stop the date/time updating Row 11 (now becoming Row 10) from updating?

Thank you all for taking time to help.


	VB:
	
 Range) [/COLOR][/SIZE] 
[SIZE=2][COLOR=#000000]    Dim c As Range [/COLOR][/SIZE] 
[SIZE=2][COLOR=#000000]    Application.EnableEvents = False [/COLOR][/SIZE] 
[SIZE=2][COLOR=#000000]    For Each c In Target [/COLOR][/SIZE] 
[SIZE=2][COLOR=#000000]        If c.Column > 1 And c.Column < 18 Then [/COLOR][/SIZE] 
[SIZE=2][COLOR=#000000]            Cells(c.Row, 1) = Now [/COLOR][/SIZE] 
[SIZE=2][COLOR=#000000]        End If [/COLOR][/SIZE] 
[SIZE=2][COLOR=#000000]    Next c [/COLOR][/SIZE] 
[SIZE=2][COLOR=#000000]    Application.EnableEvents = True [/COLOR][/SIZE] 
[SIZE=2][COLOR=#000000]End Sub [/COLOR][/SIZE] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello!

I need help setting up a worksheet that forces a link to another worksheet when text is entered.

For instance, I will have several columns in the first worksheet (Sheet 1). For each entry, a tally ("X") will be added under the applicable columns. Most of the column headings are pretty straightforward. To keep things uncluttered, I want one of the columns to have an auto-link (?) feature so that when the user adds an X in this one column (we'll call it Column D), the user will then be auto-linked to another worksheet (Sheet 2) in the workbook. Sheet 2 will have room for more information regarding Column D, Sheet 1.

Is this possible? Please help!

Hi everyone,

Sorry in advance for my very lengthy explanation and question! This is my first post and I hope this isn't a duplicate - I searched these boards and the most recent similar question to mine was in 2004. After 2 1/2 days of searching the internet and reading various forums, I can't seem to come up with the right macro or vba code to have a worksheet automatically protected after a user enters data in a specific column. Let me say up front, I have pretty much no experience with macros or vba code, but my basic knowledge of Excel is good. I've created a spreadsheet that I basically want to use as a time clock. I found vba code that places a static date/time stamp in Column C when a name is entered in Column B. This works great. What I am stuck on is the protecting part. I would like the entire worksheet to be protected automatically after data is entered in Column B and for this to be transparent to the user. I've already got the cells in Column C locked, but that does no good until the entire worksheet is protected, and I don't want the users to do the protecting. Without the worksheet being protected, they can delete the time stamp. I also need the worksheet to automatically unprotect upon opening. Any suggestions would be SOOO appreciated! Here is the code I already have in place for the time stamp:

Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRngToCheck As Range
Dim myIntersect As Range
Dim myCell As Range
 
Set myRngToCheck = Me.Range("C:C")
 
With Target
Set myIntersect = Intersect(myRngToCheck, .Cells)
If myIntersect Is Nothing Then
Exit Sub 'nothing to do
End If
 
Application.EnableEvents = False
On Error Resume Next
For Each myCell In myIntersect.Cells
With myCell.Offset(0, 1)
.NumberFormat = "mmm dd, yyyy hh:mm:ss"
.Value = Now
End With
Next myCell
On Error GoTo 0
Application.EnableEvents = True
End With
 
End Sub
I hope I've supplied all of the relevant details - if not please let me know.

Thank you in advance!

how do I set up comments in Excel that automatically insert date/time stamp
as they are created?

I wonder if anyone can tell me if it is possible to auto proect cells
after data has been entered?

Basically I have a spreadsheet that has two levels of protection - some
cells are password protected and others available for all to enter data
into.

What I would like to achieve is once data has been entered into a row
in the editable area that row becomes part of the password protected
area automatically.

Also is it possible to use the NOW (or similar) function to populate
one cell with a date when another is filled?

Ie a log entry is made in column B. When some data is entered into
column B cthe corresponding entry in column A gets filled with todays
date.

Any help much appreciated.

Regards
Clive

--
ccarmock
------------------------------------------------------------------------
ccarmock's Profile: http://www.excelforum.com/member.php...o&userid=27670
View this thread: http://www.excelforum.com/showthread...hreadid=471820

Hello all,

I am struggling with creating a data validation button!

My spreadsheet has several columns (Invoice Number, Vendor, Amount Due, Received Date, and Due Date) in which a user fills out the userform and hits save and the data is entered into the next row of the correct column.

Now I would like to add a 'Validate' button to the spreadsheet which, when clicked, will run through each row and check that the values in each cell are valid entries (ie. Invoice number as string, vendor as string, amount due as double, due date as date) but I am not that great with VBA and I have no idea how to do this!

** I understand that excel has a data validation tool that you can set for a particular cell, however when I set these rules, it does not apply to the data that is entered into the spreadsheet from the userform. For example, I set a data validation for the due date to be greater than the received date, but no errors occur when I enter a due date that is before the receive date in the userform.

Any help is greatly appreciated!