I have an excel file with several worksheets and 1000's of lines of data.
Protection is already given courtesy of having to enable macros or sheets are hidden, and copying of data is not possible. It is however unfortunately possible to copy the excel file, therefore I would like to have code to to render the file inoperative if a date elapses. In addition since the file is password protected (as regards alterations - read only for others), I would like the clock to increment for example 7 days further every time the correct password is entered. In other words if the time period elapses simply entering the password will reset the clock and permit all users access for another week once the file is saved..
I have a raw of data consists of expire date. I would like the excel to control the first expire first out by highlighting the nearest expire date for each materials.
Note that for each materials there are several batch with different expire date. I want the excel to highlight to me which batch i should use first base on nearest expire date. And also highlight in different color if the batch had expired.
Example is attached. I had manually highlight suggest batch to use in yellow, and expired batch in red. How should i do it with formula? Perhaps with "conditional formatting"?
SOLVED: adjusted functions. creating a vba macro for other reset goals. other goals have changed as well.
I have a table where the subsheets have the specific days that an employee uses vacation, sick, personal or other hours of off time. i wrote alot here to get my goals/thoughts down
my goal is to master one section then apply it to the other vacation date types. i'm stuck on going with a formula or using vb. also another concern is odd months such as someone hired 10/31 compared to a month like 6/30. not sure if this is a real concern.
a. need to reset vacation hours for the year based on annual anniversary date -"vacation hours per year" -"vacation hours remaining" =E8-('Vacation 1'!B8+'Vacation 2'!B8) this data is taken from the subsheets. there is vacation 1 for jan-jun and vacation 2 for jul-dec
1year=40 hours after 1 year anniversary =80 hours 3 years+ =120 hours Vacation only rolls over for 3 months after anniversary date (this is a concern after part a is completed)
second part of my project: b. vacation hours remaining can be carried over up until 3 months after their anniversary date. at this point they are wiped back to 0 until the next anniversary date (where the carry over hours recalculate)
ATTACHED TEMPLATE
things i am changing in design: vacation hours per year (to include a formula to calculate anniversary date, with the amount given for 0 year, 1 year, 3 years....) rather than a manual added # (which was lets say 40 hours+ carry over hours) -thats why i am separating the carry over hours... which i'm hoping to auto calculate by taking
anniversary date - 3 months in a statement to reset it back to 0 after the three month period.
I am currently working on a workbook to have employees fill in data on what tasks they compelted for the day, and how long it took. There are 5 colums (for this purpose) Task, Description, Time, Required to complete, Completed. The tasks are predefined and listed out in each row. There is also a space for employees to select the date they are entering the data for.
I would like to have a macro that is linked to a submit button and when pressed populates this data into a database on another sheet. This database is split into two sheets (1 with time and one with tasks required/compelted). Each sheet has the list of tasks going down a cloum on the left, and dates along a row on the top. When the employee presses submit I would like this data to popuplate in the coresponding date and task fields and then reset the form on sheet 1 to all 0's.
I have products that have a 3 year shelf life from date of package, I've got:
Col A: code of product. Col B: date of package. Col C: expiry date. Col D: I would like to construct an if statement which will state:
Condition 1: that if expiry date (Col C) is less than current date (today()) then to display "expired by x years, y months and z days" with reference to the current date - using pearson's DATEDIF formula:
"Expired by "&DATEDIF(C2,TODAY(),"y")&" years "&DATEDIF(C2,TODAY(),"ym")&" months "&DATEDIF(C2,TODAY(),"md")&" days"
Condition 2: that if expiry date (Col C) is greater than current date (today()) then to display "in date - x years, y months, z days left to expire" I'm unsure as to how to construct the second formula - as it will be a countdown date difference from the current date to the expiry date? Final if statement will be: If(C2<=Today(),formula for condition 1 as shown above,formula for condition 2)
I.e. it will only work for a specified time and requires the author of the script to renew it at a given point?
I want to be able to do this to strengthen my position as an independent contrator financially...and copy protect my work from unwanted exploitation
Some ideas: 1-MSG box requesting a pre-defined password to renew the time for the script to work along with contact info for the author
2-Ability to define # of days, months and or years the script will work
3-Ability to define the # of days, months and or years that a MSG box warning of pending expiration will appear
4Ability to define the # of days, months and or years that the renew will extend the use of the script with multiple pre-defined passwords allowing different lengths of time extention! Perhaps an encryption method allows you to in another excel sheet (say the comfort of your own home) generate at will a password that will extend a scripts life for 3 months?! They contact you again, and again you generate a "random" password that will extend the original script
5-Anti-hacking measures, using MS's built in security
This might be a useful way of extracting monies over time, for the use of a script you provide....pre-set
I recall a few years ago that I located a program that allowed me to create an Excel spreadsheet and share it with others, but that locked the spreadsheet after a given period of time. I need to create a spreadsheet, but I want to put a time frame on it which requires users to request an access code from me every so many days. I don't want users in my business to be able to take the spreadsheet and use it should they leave the company. I know there was a subscription program that allowed me to do this, but I can't recall the name of it. It also allowed Excel to hide all of the formulas, even though the calculations worked.
I have my Employees List in one Sheet and in another Sheet2 I want to Display those Employees Names and List whose Passport going to Expire in the dates entered in Sheet2 A1 and B2 Cell.
Ive been trying to do this for ages now, but decided i need further help! Im fairly new to VB but getting the hang of it, however this one gets passed me.
I have made a spreadsheet that works out figures and conversion rates and stuff, however i would like it to expire with the option of a password lets say for example after 30 days, this will effectivly dissable, or close the spreadsheet down until a password is put in, then it will all come back to life.
ive been trying to think of a way round this now for days, but headache after headache i still cant get it to work. The formula doesnt have to be that secure, as the people using it im certain would never even look for it untill its too late!
When opening a new Excel file, it is asking me for a password to an old excel file that was password protected, which I have deleted from my computer. It is still asking for this password every time I open a new or existing file. When I hit cancel it lets me open the file.
I have a spreadsheet, which has certain worksheets that are password protected. I need to make changes to some of the formulas, and the VBA modules, however I cannot remember the password!! Is there a way of identifying the password??
Range("G7").Select Selection.Copy Workbooks.Open Filename:= _ "Q:PublicPAYMENTS Q&RREPORTSSuspense Activity BUSINESS2008 - Suspense BUSINESS - Activity Reports2008 Avon.xls" Application.CutCopyMode = False ActiveWorkbook.Save ActiveWindow.Close End Sub
When i open the workbook it requires me to insert a password, i tried to type this but excel will not follow keystrokes, i then inserted the password in cell G7 which i then copy and paste, which you can see the copy at the beginning of the macro.
Is there a way where you can get it to insert the password so i can open the workbook in a macro.
What im trying to do is save 26 suspense spreadsheets by just clicking on a macro.
I have a sheet that is password protected. I have this code attached to a command button. It will unlock the worksheet, autofilter it, print it, unfilter it, and password protect the sheet again. However it is protecting it without a password. I need to have it protected with the password so that someone will not be able to just go to tools to unprotect the sheet.
Private Sub CommandButton1_Click() ActiveSheet.Unprotect "rainforest" Columns("O:O").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlAnd ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Selection.AutoFilter Field:=1 Selection.AutoFilter ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowSorting:=True, AllowFiltering:=True End Sub
I have a workbook with 20 worksheets the first is a summary called Discount Set the 19 sheets with various names
The 19 sheets in column H4:H40 u enter a number greater than 0 which is the quanty required. This is used to calculate a price on which ever row it is entered
the selection must only affect numbers as there are rows that are just shading and they contain no data .
So u could enter quanties on serval rows of different sheets to get a total price displayed in the Discount set
What I want to do is have button on the Discount Set which will clear any quantity that does not equal 0 back to 0 in column H which will reset the total 0.00
I have Record ID on Column A: Auto Increment I have a vendor Name on Column B: Vendor 1, Vendor 2, Vendor 3 I have a Code on Column C, Code A, Code B
What I want to happen is the Counter to count +1 each time. it sees the same vendor, same code and only to count to 4. As soon as it counts to 4; the next counter should be 0 (Zero).
Col A Col B Col C Col D ======= ====== ===== ======= Record ID Vendor Code Counter ======= ====== ===== ======= 1 Vendor 1 Code A 1 2 Vendor 1 Code A 2 3 Vendor 1 Code A 3 4 Vendor 2 Code B 1 5 Vendor 1 Code A 4 6 Vendor 1 Code A 0 8 Vendor 2 Code B 2 9 Vendor 2 Code B 3 10 Vendor 1 Code B 4 12 Vendor 1 Code A 1 13 Vendor 1 Code A 2 14 Vendor 1 Code A 3 15 Vendor 2 Code B 0
I know that a variable of string type is equal to "" before it's assigned a value. So I reset the value of strings to empty (i.e. "") when I want them clear of a value. I also know that variables of Variant Type are equal to NULL when they are unassigned so I reset them to NULL when I want to clear them of a value.
But what about a Long? When you declare a variable of Long Type or Int Type or Double Type, what is it's inital value when empty and unassigned. I assume it's not 0 since 0 is an actual number.
I want to reset my lng variable and clear it of a value. So I'm wondering what should I set to... How can i do this?
I have a textbox that pulls language using the following code:
Code: Private Sub TextBox1_Enter() TextBox1.Value = Sheets("Language").Range("B2").Value 'places the value of the cell into the textbox
[Code]....
Randomly and sporadically, when working in the textbox, the form seems to reset. By reset, I mean that the language resets to what was there originally.
In the below mentioned query, i want to reset the range according to the find result. Here the range I6 need to change with the active cell with a row down
When I execute a macro, and if during the macro execution I press the "Esc" key, the execution stops and I get a Microsoft Visual Basic window with the message that code execution has been interrupted with buttons to Continue, End, Debug and Help. If I click the "End" button, the window closes and Excel is at rest - which seems to be some sort of Reset process.
I have a need to execute that process while Excel is at rest.
i have cells A1 to A10 filled with numbers i have cells B1 to B10 blank what i need to do is when something is typed in cell B1 - A1 to A5 are reset to zero and when i type something in cell B2 - A2 to A6 reset to zero.
I have tried to do this as a range ie
A1- A10 have numbers in B1 - B10 copy these numbers and reset if anything is typed in C1 with this formula =IF(C1:C5="",A1,"0") what it should do is reset B1 to zero if anything is typed in C1-C5 but only when something is typed in C1 does this work ..
I am not experienced at all with Excel, here is my problem: In column "A": I have 288 rows and has nothing but numbers in them. Row 1 is the lowest number, row 288 is the highest number. In each row the numbers typically increase; every once in a while the numbers may stay the same, but the number will never be lower. The numbers range from 0 through 600,000.
What I want to do is have column "B" follow column "A" until the cell total reaches 12000. The next cell in column "B" would then reset and start all over from "0". I want it to keep on reseting every 12000 counts. Another potential problem is that a majority of the time the cell value will not be an even 12000, 24000, 36000 etc. They may be more like A40: 11742 A41: 13201 etc.
My spreadsheet has 459 rows; however, the vertical scroll bar ends at row 569738. I have attempted to delete all of the extra rows by holding SHIFT and CTRL, striking the down arrow then right-clicking on the left margin of highlighted row numbers and choosing delete. I have also done the same procedure except clicking clear all from the editing menu. None of these methods has removed the rows and enlarged the vertical scroll bar to a proportionate size in relation to the number of rows.
The first worksheet is all formula generated and has filters at the top, which I've variously sorted alphabetical. The second worksheet is raw data the user isn't intended to read.
Now I want to alter one of the formulas. The problem is, if I change it in row 2 and fill down I get all kinds of different information. This is because while the present formulas read
='other worksheet'!A265 ='other worksheet'!A934
- and so on at random, when I resort them I just get A2, A3, and so on.
If I run the macro and get the message box too pop up because I did not move, the macro will not run. I have too choose another one before I can get the one I want to work. I guess it needs a reset if the message box comes up.
