Protect Original Data From Changes
Apr 22, 2009
I have a tracking log, stored on a network drive, that tracks open purchase orders on about 13 different buyers. Each buyer has access to this data, and would be interested in looking at it in different ways. I have an idea for protecting my original data in this workbook, but allowing others to view and edit the data anyway they want. I thought I'd run it past the pros first, to see if they knew a better way, before I started trying to code it.
My idea was to put a macro in the workbook open event that opens an input box, "Enter password or select okay." If they enter the correct password, the macro exits, and they are in the workbook. Any changes made after entering the correct password will be made to the original data. If they enter anything OTHER than the correct password, the code would save the workbook AS another name, so now any changes made would be only made to a COPY of the original data.
This way, when I do my daily updates, I can open the file using the password, and save the file after editing. Anybody else would only get to edit a copy of my original. The other question is..., is it possible to put a macro in the workbook exit event that requires a password to overwrite the original? Otherwise, the user could edit the data in the "saved as" version, then just overwrite the original file, either accidentally or maliciously. I know they could still overwrite it by editing the macro, but I don't think they're that savvy.
View 5 Replies
ADVERTISEMENT
Sep 5, 2012
The problem is when I highlight a row with some color the original color of the row is gone, so I tried this code, and again, it's removing the original format and color for the row This is the code from McGimpsey & Associates : Excel : Highlight row with background colors
Code:
PrivateSub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Const cnNUMCOLS AsLong=256
Const cnHIGHLIGHTCOLOR AsLong=36'default lt. yellow
Static rOld As Range
Static nColorIndices(1To cnNUMCOLS)AsLong
Dim i AsLong
IfNot rOld IsNothingThen'Restore color indices
[code].....
How can I retain the range's historical color so that when I deselect the row it reverts properly?
View 4 Replies
View Related
Nov 14, 2008
Id like to apply a formula, any formula to an entire column if it contains data, and incorporate the original data in the calculation and then replace the original data with the result. I don't want to have to create new columns.
I'm using this to fix up database results; a common problem is dates in dot format e.g. 14.11.2008
All I have so far is an autofill formula that overwrites everything. Can someone help me with the rest? I'm using the SUBSTITUTE function to replace the dots '.' with slashes '/'
Sub Create_formula_result()
Dim Limit As Long
Dim r As range
Set r = range("A1")
r.FormulaR1C1 = _
"=IF(RC[0]"""",(SUBSTITUTE(RC[0],""."",""/"")+0) ,"""")"
Limit = ActiveSheet.UsedRange.Rows.Count
r.AutoFill Destination:=range(r, Cells(Limit, r.Column))
End Sub
View 9 Replies
View Related
Dec 20, 2012
I'm using Excel 2010 and I applied a Data Filter to a simple table. I then messed around with the drop downs in each column, sorting the data by different criteria. After doing this, is there a simple way to get the table to revert back to its original order/form?
View 3 Replies
View Related
Dec 2, 2008
I have a range b2:g37 filled with numbers. I use max function to get the max number in this range in cell C41. For example, the number is 20400. Now I want to know where the 20400 is originally located in the spreadsheet (ie. in column B, C, D, ...? in row 2, 3, 4, ...?).
View 9 Replies
View Related
Apr 27, 2007
I need a method of updating a set of data held on tab1 from tab2 where details of one record is shown using VLOOKUP, possibly via a macro? For example:
Sheet 1 holds info on various companies - (Column headers: Name, address1,
address2, postcode, " DATE LETTER SENT")
Sheet 2 Uses a drop down menu to select the desired company and some VLOOKUP
formulas show the info from sheet1 on that one particular company. Sheet 3 There is a printable standard template letter which draws info from the record selected on sheet 2 (using standard =Sheet1!A1 formula) formated so the letter is addressed to the specific company selected. Back to: Sheet 2 - At the bottom there is a button which says 'Print' This runs a macro which selects sheet3, prints it then returns to sheet2. I need this macro to also go back to sheet1 and update the relevant record with ideally todays date (the date the letter was printed) in the "DATE
LETTER SENT" column mentioned before but can just be an 'X' to show a letter
has been printed for this company/record at some point. So in the future, by looking at sheet1 you can easil determine which companys have had letters printed against, and which havn't.
View 2 Replies
View Related
Feb 18, 2014
I need to merge rows with duplicate values in column A (Patient Name being the most important one), with columns B, C, & D usually having different isolated values as well. Columns E, F, G, H, & I are date columns, but the data is always going to be the number 1, meaning a patient was seen once that day (if they were seen two times that day for different reasons, information would be in an unmerged second row [same patient name listed in two separate rows], where columns B & C would be different). Column J is an autosum of columns E through I if that makes a difference. Column K is a notes column. The data that needs to be merged is always added to the bottom of the spreadsheet in order to show that a patient was seen on any given day, with columns B through K almost always being blank. Example:
Column A---------Column B---Col C----Col D--Col E--Col F--Col G--Col H--Col I--Col J---------Col K
Patient Name-----Therapist---Shared--%P----2/3----2/4----2/5-----2/6----2/7---Total Visits--Notes
Alice Alpha--------AB----------PT-------1---------------------------------------------0-------------blah
Boris Beta---------BC----------SELF----2----------------------------------------------0------------blahblah
Carl Carlisle-------CD---------PTA------3----------------------------------------------0
Carl Carlisle-------AB---------SELF-----2----------------------------------------------0
Donny Delta-------DE---------PT--------1---------------------------------------------0
Ernie Elephant-----EF---------PTA-------2---------------------------------------------0
Alice Alpha-----------------------------------------------1
Carl Carlisle--------------------------------------1--------------1---------------1
Ernie Elephant-------------------------------------------1---------------1
This is what I'm hoping it can look like:
Column A---------Column B---Col C----Col D--Col E--Col F--Col G--Col H--Col I--Col J---------Col K
Patient Name-----Therapist---Shared--%P----2/3----2/4----2/5-----2/6----2/7---Total Visits--Notes
Alice Alpha--------AB----------PT-------1---------------1-----------------------------1-------------blah
Boris Beta---------BC----------SELF----2----------------------------------------------0------------blahblah
Carl Carlisle-------CD---------PTA------3-------1--------------1---------------1------3
Carl Carlisle-------AB---------SELF-----2----------------------------------------------0
Donny Delta-------DE---------PT--------1---------------------------------------------0
Ernie Elephant-----EF---------PTA-------2--------------1---------------1-------------2
In this example Carl Carlisle is being seen for two different things, however how would it be written so the macro would know which Carl Carlisle row to merge with? I'm thinking that before running the macro I could manually enter the information into column B so it knows which Carl Carlisle row above to merge with.
Data always starts at row 14 (row 13 is frozen pane header column), and extends to a row that is different every week depending on how many people happen to be in the list.
I found something from this link that looks very similar to what I need, but with no knowledge of coding, I have no idea how it should be tweaked: Merge Duplicate Rows Keeping Data In Same Columns
I know I'm asking a lot, but the amount of time this takes to manually go through hundreds of rows of patient names every week is incredibly time consuming, and I have too many other things to stay on top of at work for this to drag me down day in and day out.
View 5 Replies
View Related
Dec 2, 2009
Instead of Excel creating a new sheet when I double click on the results of a pivot table, is it possible to just filter the results of the original source data's sheet?
Here is my situation, I have source data that needs to be updated after someone double clicks on the pivot table data. They won't be able to update the source data if it just creates a new sheet (since it's a copy).
I'm running Excel 2003.
View 14 Replies
View Related
Sep 22, 2006
I have 6 columns of data laid out as follows,
columnA columnB columnC columnD columnE columnF
XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX
XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX
I need to find data in columns B and C and add the figures from column F, which drop down one line. I have written the following formula which is giving me far higher figures than I should be getting
= SUMIF($B$2614:$B$2640,"bhree*",IF($C$2614:$C$2640,">=01/07/2006",( OFFSET($C$2614:$C$2640,1,3,1,1))))
View 9 Replies
View Related
Jan 17, 2013
Some sensitive data is held on worksheets that are used by people that do not have access to see the data.
It would be most convenient to keep the data in these worksheets but hide it from view of the users.
Is there a way to password protect a users ability to unhide a column?
View 2 Replies
View Related
Nov 12, 2009
I'm trying to find a way to search a second sheet in a workbook for specific criteria outlined in a first sheet (in my attached example, from A3 downwards within the 'list of search criteria' sheet), and then to copy any secondary data found against a successful search match to the original sheet, transposed against its corresponding matched search term.
As you can see in the example, the search term 'bindi' (A4 in the 'list of search criteria' sheet) appears in the 'data' sheet 3 times - the secondary data for these occurences ('feathery', 'Fibonacci', 'glassy') is copied to the 'bindi' row on the first sheet and is offset with each copy to produce a transposed-esque effect of copy and paste.
If it's any help, there are a maximum of 9 matches for a single search term in the real document.
Thanks in advance for your help... I tried to adapt a previous solution given to me for a similar question but failed miserably. I bow humbly to your expertise!
View 9 Replies
View Related
Feb 27, 2014
I have a spreadsheet with multiple formulas in several columns - my staff will be using this spreadsheet to enter data throughout the year so it will be constantly touched by several users. Some of the columns are hidden, but I worry about someone sorting something funky or deleting or adding a row. Any way to protect the columns I have formulas in while still allowing them to enter data in the rest of the spreadsheet?
View 2 Replies
View Related
Oct 27, 2009
I have my spreadsheet protected and all the neccessary fields locked/unlocked, but one of the columns contains data validation. How can I prevent a user from copying and pasting a value into the data validation cell versus of course the intended purpose of picking from the list?
View 9 Replies
View Related
Dec 23, 2007
I'd like to protect Excel Worsheet and use the function from Menu Data -> List. The problem is that when I protect the Worksheet the option for new row is not appearing. Although I select all CheckBoxes and allow Sort, Insert Row, Insert Column, Select locked cells, Select unlocked cells etc...
p.s. Excel 2003
View 9 Replies
View Related
Oct 28, 2009
I have my spreadsheet protected and all the neccessary fields locked/unlocked, but one of the columns contains data validation. How can I prevent a user from copying and pasting a value into the data validation cell versus of course the intended purpose of picking from the list?
View 9 Replies
View Related
Jan 12, 2007
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
View 3 Replies
View Related
May 24, 2006
I have created a workbook that imports data from another workbook which is used frequently on a network drive. After I import the data to my new workbook, it locks the original workbook for editing. Is there a property that will allow me to disable this 'locked for editing' read only mode or any other way to get around this?
View 6 Replies
View Related
Mar 15, 2013
I have a spreadsheet whee teammates can inut the date in one cell and the exact time in another cell - how do I lock the cell with the time so it can't be changed?
View 1 Replies
View Related
Jun 6, 2013
1- If I wish to write in the cell I can do it ?
2- When I do not wish to write in the cell I can do it (Its meaning protect the cell.)
View 2 Replies
View Related
Jun 6, 2009
I need to protect the whole sheet except for a small area where data can be entered and if possible make it so that the work sheet can be sorted. I have attached an example sheet.
View 2 Replies
View Related
Dec 3, 2012
I'm using Excel 2007. I know how to protect specific cells within a worksheet, but I have not been able to find any information online pertaining to locking or protecting inserted header/footer data, even if I protect the worksheet.
I'm not concerned about setting specific VBA commands to ensure the header/footer prints, but rather preventing other users from being able to edit the data upon distribution of the worksheet.
View 3 Replies
View Related
Jan 17, 2007
I have an excel file that imports data from an access database. Now the user wants the database password protected I edited my code to include the line "user ID=admin;password=risk;" but I'm getting an error message stating: "Cannot start your application. The workgroup information file is missing or opened exclusively by another user."
I'm bothered because I did not create a workgroup information file. I just want a password on the database I did not create users and groups.
Note this code works fine without the password protection and the added line.
My
Sub Update_Click()
Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C: est.mdb;" & _
"user ID=admin;password=risk;"
szSQL = "SELECT * from data"
Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
View 4 Replies
View Related
May 15, 2014
create macro in order to protect Data Validation rules.
e.g. If user copy and paste cells from others source which is not in the same validation criteria or not contain any validation rules, the existing validation will gone.
so, is there any macro which will be able to automatically run to prevent the data validation?
View 7 Replies
View Related
Jul 26, 2006
I would like to protect the intellectual property of an application and have read differing opinions of the merits of protecting VBA code. What are the opinions of the experts on this forum? Are the password breaker programs so advanced that protecting code is a waste of time and only a hinderance to debugging and troubleshooting?
I am using MS Excel 2003 (11.5612) on XP.
I apologize if this topic has already been covered on this forum. I searched but did not find exactly what I was looking for. I am happy to review an old post if one exists.
View 7 Replies
View Related
Aug 21, 2014
I'm setting up a payroll for several employees in one workbook. I start from the particular employee's worksheet and then I need to go to another sheet where various information is stored and then come back to the original employee's sheet. The current macro works fine if the various information is stored on each employee's worksheet but I can't figure out how to go to another sheet and then come back to my active cell in employee sheet.
[Code] .....
View 14 Replies
View Related
Jan 19, 2010
I'm using the function Proper(A1) to clean up some columns that have some entries in all CAPS. It works fine. What I'd like to do now is get rid of the original column and just keep the altered one. Of course since the original is the source for the new one, when I delete it the new one goes nuts.
View 3 Replies
View Related
Apr 12, 2012
I have two issues:
1, How do I calculate the original value of something if it has dropped say 9.3%
Ex Stock value 5.56 down 7.4%. How do I calculate its original value.
2, I have copied some numerical data from a website, on one computer when I drop that data into an excel sheet it falls into separate cells allowing me to manipulate it, on another computer I drop it into an excel spreadsheet and all the data goes into one cell not allowing me to manipulate it.
I was wondering is there a setting on one excel spreadsheet that I am inadvertantly not using on the other.
View 4 Replies
View Related
Apr 27, 2012
How do I work out the parts scanned value from a percentage. Example below.
Overall99.75%Total Stock 27194Parts scanned??
View 2 Replies
View Related
Jun 21, 2006
I am writing a macro that copies a pdf from a temporary directory to a permanent directory. After the copy is complete, I want to delete the original file. Looking in Visual Basic Help, it appears that I need to create a FileSystemObject, but I have no idea what that means or how to do it. sDirectory is a string variable of the path of the file and sBatch is a string variable of the name of file. This isn't working.
Set fs = CreateObject("Scripting.FileSystemObject")
sDeleteFile = (sDirectory & sBatch)
Set a = fs.CreateTextFile(sDeleteFile, True)
a.DeleteFile.sDeleteFile
View 4 Replies
View Related
Mar 16, 2007
I recorded a macro in a workbook called masterschedule. The macro works only if I run it when I am using the masterschedule. Each week I open the masterschedule workbook and name it the current week, for example 3-26-07 schedule is the name of the most current schedule. I then open 7 other spreadsheets and paste information from the current schedule 3-26-07 (this week) onto 3 different sheets in all of the other spreadsheets. It works fine if I am using the masterschedule. Others have access to this workbook once it is named something else. I do not want to allow others to have access to the master workbook. Can someone help me with this? I've attached part of the macro below. I need it to work in whatever the masterschedule is renamed to.
Windows("MASTERschedule.xls").Activate
View 9 Replies
View Related