how to lock a cell date automatically after the user saves changes, it's current format it is a tick box with a reference cell Today() but it need to be locked once completed,
View 5 RepliesI have created a user form using the addins that come with excel to create a calendar that will allow the user to select a date and automatically put that in a specific cell. My system is office 2000.
The code is as follows:
Private Sub OK_Click()
Dim i As Integer
Dim myCell As Range
i = 0
For Each myCell In Selection
myCell.Value = Calendar1.Value + i
i = i + 1
Next myCell
Unload Me
End Sub
Now, I gave it to my boss who has xp, and I get the following VB error when she chooses a date and clicks on the ok button:
Method 'Value' of object 'ICalendar' Failed
I have a series of consecutive cells that I have a number entered in it. AP50 through AP 74 are numbered 1 through 25. I don't want the user to be able to change the number but I do want them to be able to click in the cell as this makes other functions happen.
View 3 Replies View RelatedIs there a macro that automatically saves a backup of your spreadsheet every week?
View 3 Replies View RelatedI need to create a macro to where once a cell has had a formula or data inputed that it locks afterwards and cannot be edited without unportecting the sheet or not at all. The problem is I have no idea how to program in VBA. I can get there and select the worksheet but after that I am stumped. How would I enter the macro and what should the macro look like?
View 2 Replies View RelatedI have a worksheet that is protected with data entry cells unlocked.
1. Unprotect worksheet
2. Lock all cells in the worksheet and then protect.
3. After a date in a cell in the worksheet.
I'm a newbie to vba and at least know vba is the only way. And this need to be automated.
is there any way a macro can be automatically run when a user clicks unto a cell?
View 5 Replies View RelatedHave a worksheet that will be used multiple times. User will enter name and employee number only on the first instance that the worksheet is opened. Would like to protect that info automatically for further usage. Most of the worksheet is protected but some cells need to remain unlocked so the "whole" sheet protection will not work.
How do I protect only specified cells, say F3, G3, H3, I3, after the user enters data thru an input box without changing any other protected cells on the sheet?
Is there a way to freeze the user input when it first comes up for a given amount of time. In essence, I am trying to force the user to read before clicking.
View 9 Replies View RelatedI've got an Excel file that I'll be offering for download. I'd like to customize each copy for each user as they download it from the server and am trying to figure out if this is possible & what technology I'd need to use to do so. Simply, I'd like to edit a range in a document, lock it with the built in Excel password protection, and then resave it for the user. Is it possible to write a script to do this w/o opening the file? Any ideas on what technology I'd need to use to do so (.NET framework? OWC?)?
View 4 Replies View RelatedI have two workbooks. I am copying cells data from one workbook A to workbook B with macro - pretty easy. Now, after selecting and copying cell data in workbook A and switching to workbook B I want to be able to pause the macro and wait for the user to chose a correct cell where he/she wants to paste the data. After the selection the macro would automatically paste the data in selected cell. Since I am completely newbie any help would be appreciated. Here is the macro I have so far :
Sub CopyVIN()
'
' CopyVIN Macro
' Macro recorded 5/26/2009 by Pacific Coachworks
'
Sheets("Sheet1").Select
Range("G9:R9").Select
Selection.Copy
Sheets("For copying VIN").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Cami's Production Schedule.xls").Activate
' Here I'd like to have a code for waiting for a user selection and pasting the data automaticlly.
End Sub
I have a code which should automatically lock the cells when ever i change a certain value of cells. the code is given below. But the change doesn't happen. I've first protected the sheet then using code unlock and then again protect the sheet after locking the cells. I'm getting no errors but the cell doesn't get locked.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 14 Then
ActiveSheet.Unprotect Password:="password"
[Code]....
I have a grid used as a shared diary and I need to automatically protect a cell by changing its colour to red, can this be done using VBA?
View 9 Replies View Relateda code that would automatically lock a cell every time I input a value to it?
View 2 Replies View RelatedI am trying to apply a conditional cell lock to a worksheet. I am using Excel 2003, not 2007.
In column J, I have a list validation being used. The default value is "Not Claimed", I want to lock the cell from being edited once the value has been changed to any other value on the list.
The worksheet will be password protected to prevent entries into static fields (A:I & N:P).
I also would like to give the user the ability to unlock a specific cell, not the entire worksheet, by entering a password or something if they accidently choose a wrong value from the list.
I have column Q and beyond available if I need to have the password entered somewhere else.
I have attached a sample of the file I am working with.
I need a macro where user choose a value from validation, automatically lock the other cells and should show an error message.
Here is what I am looking for. User will choose Yes or no from cell H15 validation. If user choose "NO" from the validation then all other cells has to be locked in the workbook automatically except Cell H15 and a msg box needs to appear stating: "Select Yes from the validation".
I have an excel sheet which has a macro which captures the current time in a cell with the formula =now(). What I am looking for is that once a user runs a macro and the time is inserted into that cell then that cell should be locked and cannot be changed then. If this can be done by any formula / function or VBA.
View 2 Replies View Relatedi have this macro that saves into specific folder dependant on cell values and it works spot on with the exception that my po numbers are like st010,st011,st012 etc etc but when it saves it doesnt have the st in front. ive tried numerous ways but to no avail
View 5 Replies View Relatedi would like to use the formula "now()" to establish a date in a document. is it possible to lock this date to the initial date? (so when the document is created the date won't change the next day)
View 2 Replies View RelatedI am trying to check a user input to see if the user has entered the date correctly. I am using the code below but it keeps giving me a "Type Mismatch" error.
View 2 Replies View RelatedIs there a really easy code which will insert the current users ID into a cell adacent to the one previously completed automatically? For example if a date was entered in A2, then B2 would automatically be completed with the user identity?
View 7 Replies View Relatedi have the following code to insert comments into cells, however if the user cancels the input box (i.e. doesn't insert any text), a comment is still added with their details and the comment 'FALSE'. Is there anyway to stop comments being added if the user cancels the input?
Sub Insert_Comment()
Dim iReply As Integer
Dim cl As Range
Dim sComment As String
Dim sText As String
Dim UserNameWindows As String
I'm looking for a way to achieve the following:
When entering data in any cell in a excel sheet I would like the previous date to automatically appear in particular cell so the cell will show the date I entered something in the cell. I want the date to be a fixed date, I don't want it to change.
On entering something in a different cell in the sheet, the date of the cell remain same.
After saving the sheet, the next time I open it the dates will still have to be the same as they were the first time.
I am trying to automatically add a date to a cell (D2 on sheet1) when there is data entered in a range of cells (C8:I59) on sheet2, so I can see the date of the last there was data entered.
View 3 Replies View RelatedI want to enter the word 'Closed' in cell B1 when the due date in cell A1 is exceeded by 272 days. I have tried using conditional formatting and excel accepts the formula but nothing seems to happen. Here is what I have tried;
B1=IF(A1<=NOW()-272,"Closed")
I have 6 Headings in excel named...
"A" in cell A1, B in B1, "C" in C1, "D" in D1, "E" in E1 and "F" in F1.
There are two projects.
Project 1 has phase A, D & F and Project 2 has phase A, B, C, D & E.
My Specification follows...
1). Take Prject 1 - Which starts from A...in cell A2 I will keyin "A". When the phase comes to an end I will key in the end date of the phase. As soon as I key in the end date in cell A2 Letter D should automatically appear in the cell D2 and when Phase D comes to an end I will key in the end date in Cell D2 which should automatically keyin F in the cell F2. and is the same case for Project 2.
I have the following
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 = Date
.EntireColumn.AutoFit
End With
End If
End Sub
I just want the date to appear in cell H6 autmatically and the time to appear in H7.
Is there any way to automatically lock in the date after you pull it up with the TODAY function? Or is there another function that will do what I'm trying to do?
I want it to automatically fill in today's date, when a certain empty cell has a value put in, then freeze there.
I have a cell that is constantly being changed from a date to a number. Excel automatically formats the cell as a date when a date is entered but if I re-enter a number it leaves the format as a date.
Is there anyway to automatically change the format back to general if a date is not entered.
I had this bit of code obviously it dosent work.
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("E8:E46")) Is Nothing Then
If IsDate(Target.Value) Then
Target.NumberFormat = "d/mm/yy;@"
Else
Target.NumberFormat = "General"
End If
End If
Cancel = True
Is there a way to lock the date format as MM/DD/YYYY. I'm using the excel file as data file but one of the softwares fails to run this excel-based data file due to date format since the date format has to be in numeric format.
1/12/2013 changes to 12-Jan while processing? How to lock this format?