Excel 2010 :: Allowing Edit On Specified Range
Jan 6, 2013
Run on Excel 2010
Sub UseChangePassword() Dim wksOne As Worksheet Set wksOne = Application.ActiveSheet
' Protect the worksheet. wksOne.Protect ' Establish a range that can allow edits ' on the protected worksheet.
wksOne.Protection.AllowEditRanges.Add _ Title:="Classified", _ Range:=Range("A1:A4"),
_ Password:="secret" MsgBox "Cells A1 to A4 can be edited on the protected worksheet."
' Change the password. wksOne.Protection.AllowEditRanges(1).ChangePassword
_ Password:="moresecret" MsgBox "The password for these cells has been changed." End Sub
The red color code is giving error message: Application defined or Object defined error.
View 1 Replies
ADVERTISEMENT
Feb 6, 2014
I made a sheet to control some reports, and it works like this: you click a button ("Insert"), it opens a userform, you type everything there (date, serial number, client, city) and it place that info on the first row available. Then there's a "Clear Last Entry" button, and that's it.
I would like to protect the whole sheet and only allow users to insert new entries via the "Insert" button. How can I do this? If I protect normally Excel won't allow the VBA code to write on cells.
View 4 Replies
View Related
Aug 25, 2009
I have an event handler that runs a procedure when a user double clicks a cell. This procedure modifies the contents of this cell and other cells. I want the user to be able to select the cell so they can double click it, but I don't want them to be able to single click it and modify it by hand (accidentally or not). Is there a way to lock/unlock the contents of the cell without locking the cell itself?
View 6 Replies
View Related
Nov 16, 2009
I want my macro to protect my sheet again after it is done to allow editing objects and formatting rows. I am really stuck and have come up with the below, but it isn't working.
View 5 Replies
View Related
Feb 13, 2009
I would like to alter this code so that the user chooses the column where the numbers are. They aren't always in column D....
View 9 Replies
View Related
Jan 12, 2009
i have a spreadsheet that data is imported to from a different program. however the amount of rows of data will change from day to day. I need to be able to select this range of data before i run a macro on it.
View 11 Replies
View Related
Feb 7, 2014
I am working in excel 2010. I have a tracking document that lists free tickets and their expiry dates. In the adjacent columns we track redemption details of these free tickets. What I want to do is return the oldest expiration date from A only if the ticket has not been used (i.e. B is empty). This will allow me to see the date the upcoming tickets about to expire so we can make sure they are used.
A________________B
Expiry Date________Redeemed by
15/08/2014
15/02/2014
15/08/2014________John
15/02/2010________Marc
15/02/2011________Bob
View 4 Replies
View Related
Sep 10, 2013
I have created an Excel spreadsheet in 2007. When a friend opens the workbook in Excel 2013, It does not let him add data to the cells. ( just one column which is the "date" column) The worksheet is not locked or protected.
When I right-click on the column and goto format cells, protection, the box is ticked, but the note says this doesn't take effect unless the sheet is protected witch it isnt.
View 6 Replies
View Related
Aug 5, 2013
I have a column of dates in Col H with associated values in Col I. I need to specify a date range in Cell I1 and I2 such that the row number for the first encounter of the first date is placed in Cell I3 and the last encounter of the end date is placed in Cell I4. For example, with this data when I specificy 4/2/13 and 4/3/13, I would like to get a 19 in Cell I3 and a 53 in Cell I4.
Matty supplied the formula in Col K for each of these cells. They worked well in the application that I supplied earlier (different locations for these variables), but my real application is as shown here, and these two formula give the incorrect results shown. Both of these formula are arrays.
Excel 2010HIJK14/2/201324/3/2013310=MATCH(I1,INT(H10:H5000),0)+1444=MATCH(2,1/(INT(H11:H5000)=I2),1)+1536789X10 114/1/13 1:366.97124/1/13 2:04134/1/13 2:04144/1/13 4:563.95154/1/13 4:573.27164/1/13 5:165.55174/1/13 5:172.35184/1/13 10:30194/2/13 14:00204/2/13 14:59214/2/13 15:01224/2/13 17:192.81234/2/13 17:191.59244/2/13 17:252.14254/2/13 17:262.05264/2/13 21:07274/2/13 21:07284/2/13 21:11294/2/13 21:11304/3/13 1:38314/3/13 1:38324/3/13 2:10334/3/13 2:10344/3/13 4:24354/3/13 5:152.84364/3/13 5:154.11374/3/13 5:173.45384/3/13 5:173.24394/3/13 9:35404/3/13 9:35414/3/13 9:59424/3/13 10:01434/3/13 13:36444/3/13 13:37454/3/13 13:41464/3/13 13:42474/3/13 17:124.03484/3/13 17:133.62494/3/13 17:15504/3/13 17:15514/3/13 21:12524/3/13 21:13534/3/13 22:214.41544/4/13 1:52554/4/13 1:52564/4/13 1:53574/4/13 1:53Ppk Raw Data (2)
View 2 Replies
View Related
Nov 28, 2013
Workbook contains a number of named ranges where the name is no longer used. I wish to remove the name assigned but not the actual range of referenced cells. When using Name Manager to try and delete the name the Delete button is greyed out and not available. Workbook is being heavily modified from its original form used by another. Is this possibly from a protected area from prior user? However, I have not had a problem creating new worksheets, cell ranges, etc. Also, even for new ranges I create I am not able to delete the name as the Delete button is greyed. What is going on and how can I delete just the unused range names? Version is Excel 2010.
View 3 Replies
View Related
Jan 27, 2013
I am trying to insert a Pivot table with dynamic Name Ranges. It needs to start from Cell B1 as Column A has hidden formulas in them. I created an Dynamic Name range and tried to insert a pivot table. Excel then throws out a error stating "Data source reference is not valid". I tried re-saving the document but still no luck.
View 1 Replies
View Related
Jan 27, 2014
I am relatively new to VBA and am trying to convert a range of data to a table in the same sheet. I receive the following message when I try to run the code as shown below:
"The worksheet for the table data must be the same sheet as the table being created." The code stops on the third line of the code.
Sheets("Data Forwards").Select
ActiveSheet.Range("$A$1:$U$1000").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$U$1000"), , xlYes).Name = _
"Table1"
ActiveSheet.Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium2"
I can see that the range is highlighted in the sheet before the code breaks.
View 3 Replies
View Related
Sep 6, 2012
Excel 2010. I have a macro, stored in a workbook.
The macro is: collecting data from another opened workbook,processing the data,saving the processed data in yet another workbook.
The workbooks' names it is operating on are hardcoded in the macro. I'm trying to make it more flexible, that is to allow user to specify both source and target workbooks along with the data columns.
The workflow follows:
User opens both source and target workbooks,User opens the workbook with the macro,User runs the macro (with a button for example),Macro prompts the user to select range in the source workbook,Macro "memorizes" the range and the source workbook's name from user's selection,Macro prompts the user to select range in the target workbook,Macro "memorizes" the range and the target workbook's name from user's selection,Macro processes the data.
I found out that this can be achieved with Application.InputBox("Select cell(s)", Type:=8) method. However there are some issues that I was not able to solve playing with the returned value's methods:
The .InputBox defaults to active workbook. I was able to select another opened workbook only with Ctrl+Tab combination, but it will not work with the end users - they are too poor with these tricks. Is there a better, more intuitive way to allow user selecting a workbook?I was not able to get the workbook's name from the .InputBox returned value property. I was trying with .application.caption and .application.activeworkbook.name - none of these worked. They returned name of the workbook with macro, not the one with selected range. How to get the workbook's name from given cells range?
View 2 Replies
View Related
Jan 23, 2013
Excel 2010
I have this code in a macro, the range will change as more data is added. so that I dont have to keep changing the range. How can I have this code autofill from the activecell to the last cell that has data in column M.
Selection.AutoFill Destination:=ActiveCell.Range("A1:A50000")
ActiveCell.Range("A1:A50000").Select
View 7 Replies
View Related
Mar 19, 2013
I have to report Actual Headcount every month. Since I cannot add the Headcount numbers in the FY Column. What is the best possible forumula I can use to pick the last month's reported numbers into the FY Column.
For example.
I have 13 columns (A - M). Jan - Dec and the 13th column is the FY number.
This month, I am reporting March numbers in Column C
Column D through N (Apr through Dec) are emplty
I need Column N (FY number) to read Column C and enter the value from that cell.
similarly when I report April numbers in Column D next month, I want Column N to read April numbers and ignore the prior months.
I tried using the min/max function but since h/c fluctuate every month I could not use that. I could use an "if" statement and go back every column but that seems to be inefficient way to solving for this. Any thoughts?
BTW, I am using Excel 2010
View 4 Replies
View Related
Oct 5, 2011
There is a formula
=vlookup(a1,Named_Range,2,false)
if I change a value in the range named "Named_Range" this vlookup does not update.
This formula works but does not allow for any updates. Meaning the vlookup returns the original value even after a cell has been changed. "Named_Range" is on a different sheet but in the same workbook.
Auto calculate is on. I have recalculated the cell manually. I have Office 2010
View 1 Replies
View Related
Jul 26, 2012
I've gotten the desired result in Column B, but this will not work going forward as we add to the table in columns E:G .
I'm looking to search between columns E:F, Identify the date-range where my dates in column A belong, and pull the corresponding rate from column G into column B.
Excel 2010
A
B
C
D
[Code]...
View 8 Replies
View Related
Nov 27, 2012
Using Office 2010, I am trying to do a averageifs formula for a specified date range. I can get it working by specifying the date range in the formula itself, however when "pointing" the formula to a specific cell with a date in it, the formula gives me a div/0 error.
Formula that works is:
=AVERAGEIFS('UHP Weld Data'!M:M,'UHP Weld Data'!B:B,"08/29/2012")
What I need is a version of: (currently not working)
=AVERAGEIFS('UHP Weld Data'!M:M,'UHP Weld Data'!B:B,"H1")
The date range will constantly change as I want it to show me the past 30 days only.
View 1 Replies
View Related
Jan 8, 2013
Had been using this formula for almost 3 years, recently the formula didn't work properly as the range goes down halfway only instead of to the last data in the column. I'm using Excel 2010 now.
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
View 2 Replies
View Related
May 31, 2013
How to set up a formula on Excel 2010 using Conditional Formatting.
I want to identify duplicate account #'s within 15 days of the work date. Here is sample of the report.
Account Number
Facility
FC
Payor
Current Balance
Aging Level
Team Member
Work Date
87890
HHSS
1
BCBS OF TX (PPO)
$9,616.94
121+
Steven Johnson
5/20/2013
[Code] .........
View 4 Replies
View Related
Oct 16, 2013
range formula that i am using for drop-down lists. One of the drop down lists is based on the range where the data changes(it is a list of jobs that has been released from cad room and when they have been machined a "yes" in one of column appears and then it needs to be filtered so that only non-machined jobs are visible).
Bitmap Part Description
Expected Spindle Hours
Clock In
VLOOKUP
[Code]....
I need a formula that is not taking into account values from hiden rows. OR the formula that is not taking into account values from the rows that have Yes in the 4th column.
View 4 Replies
View Related
Oct 27, 2013
how to use the offset function to create a dynamic range in Excel 2010. An in-house Excel form I work with spans columns A thru P and has three sections. In Section 2 the user fills in employees who are requesting to work overtime. Section 2 starts row 12 and ends row 61. It's sometimes necessary to add rows to this section. How do I make this section/range dynamic using the offset function?
View 1 Replies
View Related
Dec 3, 2013
Code:
=SUMPRODUCT(--(_NamedRng1=NamedRng2),$B$49:$F$49)
I am using the above formula in my code with two Named Ranges
Code:
Set Rng3 = Range("_NamedRng1").Offset(1, 0)
=SUMPRODUCT(--(_NamedRng1=NamedRng2),rng3)
[/CODE]
I want to set the range $B$49:$F$49 in my code and I have tried the above, but it does not work.
I want to allow for the fact my end user may insert rows so do not want to use $B$49:$F$49
View 2 Replies
View Related
Apr 4, 2014
I'm using Excel 2010 and when I highlight a range and right click 'name a range' is greyed out. I can bypass this by going to the formulas tab and defining a name from there.
Anything I can check to see why this is not an option for me?
View 3 Replies
View Related
Apr 7, 2014
Not sure if there is a way to do this. I'm using excel 2010. I used this function
=INDEX(Sheet5!A1:A149,RANDBETWEEN(1,ROWS(Sheet5!A1:A149)))
I have a list on names that is in the range on Sheet5!A1:A149 I'm using the function to randomly select one of those names.
What I want to do: I want to add the randomly generated name to a range that I can then refer to later to see if that name has already come up. So I know if that name has already been used.
View 3 Replies
View Related
Jun 21, 2014
using excel 2010
I created named range selection called "Contractors".....how do I put the drop down lists in a column of cells now?
View 2 Replies
View Related
Jul 9, 2014
Please find the attached 2010 version excel file <For a given date Time Range average .xlsx>.
I want to calculate the average value (Column C) for the given date (Column A) and given time range (Column B).
Values to be plotted in the color cells yellow and light pink / magenta.
Voting Results.xlsx
View 4 Replies
View Related
Dec 5, 2013
I'm using Excel 2010. I have a range of data like:
1 1 1 0 1 0 1 0 0 1 0 0 0 1 0
I need to count the number of "isolated" zeroes in this range of data. In this example the answer would be "3". What is the best way to approach this?
View 4 Replies
View Related
Jun 4, 2013
I am trying to clear the contents of a range of contiguous cells (containing formula) in Excel 2010 64bit. But it is taking a LONG time (read >30min to clear a range 288 x 100). Originally was doing it in VBA but it was taking too long so I started stepping through the macro and identified that the slow down was occurring on a particular sheet only. So the problem is in Excel....not VBA because it is still very slow when deleting the same range in Excel without using VBA.
I've tried all the usual suspects including: Application.EnableEvents = False (run from the VBA Immediate window when using Excel without VBA), Setting to Example of slow clear contents5.xlsxExample of slow clear contents5.xlsxManual Calculation, Turning off screen updating, deleting all the conditional formatting on the sheet, removing any data validation from the sheet (yes all these at the same time). There are no links to external workbooks. I've even tried in VBA changing from .ClearContents to .Value = vbNullString. All to NO AVAIL! By way of background the file is about 72Mb #.xlsm.
If I try to clear contents of larger ranges on other sheets in the same workbook it is instantaneous. If I copy the problem worksheet off to a new workbook, I can delete the problem range almost instantly. Why deleting a range of cells should take so long! Again to clarify, I am using the terms "delete" and "clear contents" interchangeably, but they both mean "clear contents" (not delete and move up cells). P.S I've also looked into the reported conflict between Excel and Google Desktop Office Add-in (Office Button->Excel Options->Addins->Com Addins and deselect Google Desktop Office Addin) but I didn't have Google Desktop Office Add-in so it isn't that.
View 6 Replies
View Related
Jul 6, 2013
I have tried and been searching but cannot find the answer. Is it possible to change the row height and column width for only a selected range in my spread sheet, for example, F13:I23? I am using Excel 2010.
View 2 Replies
View Related
