Excel 2010 :: Unable To Use VBA Check Box With Protected Sheet / Columns?
Jul 9, 2014
I need to make a excel costing model to calculate various products prices. I am using Excel 2010.
I have many products and do not need all of them displaying at the same time, therefor I inserted a Checkbox (ActiveX Control) that hides my columns that I do not need. I did that by inserting the following and it works fine :
[Code].....
The problem is I now need to lock certain cells so that they can remain fixed and the recipe cannot change.
Once locked my checkbox no longer works and I get the following error :
"Run-time error '1004': Unable to set hidden property of the range class"...
View 1 Replies
ADVERTISEMENT
Jul 25, 2013
I could swear I used to be able to cut and paste columns in Excel 2010, but for the past week I haven't been able to. When I click on a column and do a right click, "CUT" is greyed out. I can cut any section, but not a whole column.
View 3 Replies
View Related
Feb 24, 2011
I am using Excel 2010 and I have a password protected workbook with password protected sheets that uses several macros. Most of them, in order to run, have to un-protect the sheet and then re-protect it again. This has been accomplished easily enough by adding ActiveSheet.Unprotect Password:= "mypassword" and ActiveSheet.Protect Password:= "mypassword" to the appropriate places in the script. All of my macros, which do various things like sorting and moving data, deleting blank rows, displaying dialog boxes containing warning messages etc. run fine.
My problem is this: when I password protect the sheets manually, I have checked the following options in the "Protect Sheet" dialog box. Under "Allow users of this worksheet to" I have checked 1)Select unlocked cells and 2)Format cells. After entering my password and closing the dialog box my sheet is protected, but I can edit cells in the manner my allowances permit. However, once I run any of the macros that un-protect and re-protect the sheet, I remain able to select and edit unlocked cells (practically, for my purposes, this means that I can input data which will appear in the default font size and color of the sheet) but I cannot format cells (which, practically, for my purposes would allow me to occasionally change the font color and size of the data). Naturally, after running a macro, the other cell-formatting options are unavailable to me as well. Is there any way to get my manual selections to remain in place after running a macro that functions as mine do? Or is there any way to make my manual selections the default settings for a protected sheet?
View 4 Replies
View Related
Oct 4, 2013
I'm using Excel 2010 and I've got a chart on a worksheet and the worksheet needs to be protected. I'm able to manipulate the chart in any fashion EXCEPT the position of the Data Labels. Is this a bug in Excel 2010?
View 3 Replies
View Related
Dec 30, 2013
I have a sheet protected and filters enabled. I use this code to enable sort and filters:
[Code] ........
Nevetherless, when I try to sort, I receive this message in excel: "The cell or chart you re trying to change is on a protected sheet.
To make changes, click Unprotect Sheet in the Review tab (you might need a password)."
View 5 Replies
View Related
Apr 6, 2009
i am uable to use autolfilter when protecting sheet via vbacode even though i have checked the autofilter box to be able to use it whilst protected, why does it work when i manually do protection but does not work via vba?
View 9 Replies
View Related
Jul 23, 2012
I have 2 workbooks in Excel 2010, each contain just 1 sheet. (see attached) I need to compare on sheet 1, cell D1 and column A:A (this column will be much longer), with the data in columns C:C & A:A on sheet 2, if a corresponding match is found, the data contained in column D on the same row on sheet 2 is written to the cell with the matching data in sheet 1.
My attempt is in cell D2 on sheet1.
View 5 Replies
View Related
Feb 15, 2007
Is it possible to use spell check on a protected sheet?
View 9 Replies
View Related
May 30, 2014
I'm on mac Excel 2010 and i'm trying to delete rows..but for some reason they don't go! My workbook is a basic one, just filled with a bunch of formulas, i can't understand why it wont let me delete it.
When i do try to delete it, nothing comes happens and when i try to delete a large amount it comes up with 'not enough memory, continue without undo deleting rows' but my laptop has 8GB and my other laptop has 16GB so i can't see why a 16GB laptop wouldn't be able to delete it.
View 1 Replies
View Related
Oct 5, 2012
From one moment to another I am not able to set the column width doing the following: right-mouse-button click (the column) and choose column width and then type in the desired width The column width is not set this way.
Setting the column width by dragging the right side of the column works well. Also double clicking the right side of the column (auto width) works fine.
I think I have pressed a certain key combination (I am not aware of) to activate this behavior.
How can I re-activate/enable the above (1st) mentioned method to change the column width?
View 3 Replies
View Related
Feb 11, 2014
I have a worksheet with a few "Multi-Select" type listboxes in it. I cannot seem to select the items within those listboxes... the mouse icon doesn't even change, it just shows that little plus-sign that is the default when you're just in your worksheet. (they're also ActiveX listboxes)
I can select them if I go into my Developer tab and select and deselect the "Design Mode" button. then everything works fine (mostly). Or if I resize the subwindow for the workbook. Then it (again, mostly) works fine.
It's like I have to trick Excel into allowing me to click on the checkboxes in my ListBox.
Some people I've given the file to use Excel 2007 and the file works for them. (I have Excel 2010)
I've looked up info on bound/unbound forms, but I can't tell if it applies, or how to use that. They were not created with a VBA macro, they were made using the buttons in the developer tab.
One final thing : Below the listboxes, I have some shapes that, when pressed, will take the selected items from their respective checkboxes and put them into some cells.
Here is the file: Setup form_Blank.xlsm
View 5 Replies
View Related
Oct 4, 2011
i have encountered a problem which happens when you write data that contain : in an excel sheet (i use excel 2010)
for instance if i enter to one of the cell 45:58 excel sees it as 01/01/1900 21:48:00 when i try to get the information by using a function i will get the wrong data for example typing in the different cell LEFT(Cell,5) will result 1.908
(i receiving the data from an outside source in this way and i need to make analysis)
i have noticed that the first 2 digits (21 in the example) are related to the number i have choosen in a 24 hour cycle for instance
24:58 will result 01/01/1900 00:58:00
26:58 will result 01/01/1900 02:58:00
48:58 will result 02/01/1900 00:58:00
View 7 Replies
View Related
May 3, 2013
If I type in the VBA Editor immediate window
Code:
?application.WorksheetFunction.average(array(1,3))
I get the expected answer of 2, but
Code:
?application.WorksheetFunction.mode(array(1,3))
produces a pop up error of:"Unable to get the Mode property of the WorksheetFunction Class"
(Excel 2010 on 64 bit machine)
View 2 Replies
View Related
Sep 27, 2011
Just recently My Excel 2010 has decided to not let me right click to format cells, delete or insert rows. I can do these functions from the ribbon, but not via right click. this happens in both existing spreadsheets where I am the author, or even a brand new spreadsheet like in the image below. we have restarted the computer, Uninstall and reinstalled Office and still get same symptoms. I got here thru google but cannot find an answer anywhere.
I am very computer literate and even our IT personnel have looked at this with no answer. as you can see in the image, these options are greyed out.
Running Office standard 2010, Windows 7 pro x64
View 8 Replies
View Related
Feb 4, 2013
I'm trying to compile a VBA that would allow me to compare 2 columns "A" in different worksheets (same Workbook) and output any unique values to 3rd worksheet together with the rest of the values in the corresponding row.
Sheet1
A
B
C
[Code]....
Excel 2010
View 9 Replies
View Related
Jul 10, 2014
I use Excel 2010 andexample.xlsx cannot make my code working because of the so famous errore above.
I have several pivot tables (pt) in different worksheets (ws), and a list of items stored in an array I created. I've written a macro for setting ON all items in pt except those ones in the array (listOffnet within the code). Everything works properly.
Now, I would like to do the complementary action: setting OFF all items that are not in the array. Unfortunately, I get the error at line:
[Code]....
View 1 Replies
View Related
Jun 25, 2013
I am using this code (below) to hide certain rows or columns depending on what number is entered. Everything is working fine and I am at the point where I would like to share this excel sheet with others but I would like to lock certain cells so that others cannot alter the formulas. As soon as I lock the cells and then enter values into the unlocked cells, I get the "run-time error '1004': Unable to set the Hidden property of the Range class".
I am using excel 2010 x64.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, Range("B3")) Is Nothing Then
If Range("B3").Value = 0 Then
Columns("G:P").EntireColumn.Hidden = True
Else
If Range("B3").Value = 1 Then
Columns("H:P").EntireColumn.Hidden = True
[code]....
View 3 Replies
View Related
Nov 11, 2013
It was suggested that cells formatted as lists will now allow new rows or columns. I don't think this is case but not sure how to tell? Other suggestions were merged cells or cells protected. Not sure if any of these are the case either.
View 5 Replies
View Related
Apr 17, 2013
S/W Environment: Excel 2010
Some of my worksheets are permission protected. They will allow me only to view the sheets - No editing, copy, cut, sort, add filter, etc. Is there a way to hack this and copy data (including formating) into other / new worksheets?
View 1 Replies
View Related
Feb 20, 2008
When my spreadsheet opens it automatically protects the sheets using the following
With Sheet1
.Protect Password:=SpreadsheetPassword, UserInterfaceOnly:=True
.EnableOutlining = True
End With
and I use the following to hide the column if the toggle button on my form is 'true'. The problem is it gives an error if the sheet is protected. it worksfine without protection.
If ToggleButton1.Value = True Then
Range("column_calories").EntireColumn.Hidden = True
Else
Range("column_calories").EntireColumn.Hidden = False
End If
View 7 Replies
View Related
May 16, 2014
I have a some sheets in a workbook that have collapsible columns, but I need to have the sheet protected/locked. This is for a my company's price book that goes out to distributors, so I can't have the sheet unlocked to where they can manipulate pricing. However, I need to have collapsible columns. Is it possible to have these functional while the sheet is locked?
View 10 Replies
View Related
May 13, 2009
I have a problem with a spreadsheet I am working on, and I'm not sure if its something I've done, or whether its corrupted.
Basically, Column A is Hidden and not Locked in the protection tab of formatting. The other columns are Locked and not Hidden.
I used to be able to use the arrow keys to scroll through Column A, but this now doesn't seem to work. I can select the cells within column A with the mouse and I can scroll with the mouse and the scroll bars, but I can't move between the cells with the arrow keys, enter button or tab button.
Does anyone know what might be causing this, or is the workbook corrupt?
View 9 Replies
View Related
Aug 28, 2013
I read somewhere that password of a protected excel sheet is located in the sheet itself , hidden somewhere. So , it can be located by some "curious" ones.
Second question is , how can we find that password and prevent anyone from detecting it?
View 1 Replies
View Related
Feb 28, 2013
We're setting up a test for candidates in an Excel 2010 workbook and want to get a sense of their native ability to spell. We'd like to disable spell check for the entire workbook. I've looked in Options>Proofing, even tried removing the dictionary, but haven't found a way to completely disable spell check from working. Is it possible to achieve? We'd settle for being able to disable spelling within a textbox.
View 7 Replies
View Related
Dec 22, 2013
getting a function working within Excel 2010. What I'm trying to do is to first look in a specific directory and loop through each foldername then check to see if the foldername exists in column B of my worksheet. If the foldername exists then check Column E of the same row for a specific value ("assigned") and then make sure column F of the same row has no picture inside the cell. If those three things exist (column B foldername and column E "assigned" and no picture in column F), then add a picture to column F of the same row as the foldername with a hyperlink to a filename of "notes.one" in that specific folder. Then just loop through each foldername in the specific directory until all foldernames have been checked.
View 1 Replies
View Related
Aug 12, 2014
I am gaining an error in my code due to the sheet being protected.
I cannot seem to get the correct code to allow for the code to still run, while the sheet is protected.
[Code] .....
I tried protecting the sheet via:
[Code] ....
But I still get an error.
View 2 Replies
View Related
Feb 7, 2013
Excel 2003
Windows Vista
I keep getting this error: (at the line highlighted in green)
Runtime error '1004'
PasteSpecial method of range class failed
**** Open workbooks to transfer temporary data
[Code]....
View 2 Replies
View Related
May 19, 2010
Using excel 2003, Workbook has several sheets, all sheets are protected workbook is not. I have my cells containing formulas locked, I have unlocked the other cells that require input of various data. When the user goes to type in a "unlocked" cell, (to over write the now obsolete data) it will not allow the user to delete the data, the curser remains a cross and the formula bar or the cell itself will now allow any changes.
I have double checked that the cells are unlocked. I can enter data after the last entry in the unlocked row and or column, but I want to be able to "cut or delete the data that is no longer need and begin entering data from the beginning cell of choice. I want to copy this "old" data to an archive sheet, but it will now allow me to select it.
Example: I have 6 columns starting, (a to f) the formulas are in columns e and f and start at row 4 These columns are locked and hidden. Columns a,b,c and d are unlocked for user input. All data is started from A5 which I want it to start from each time the old data is deleted and new data entered. If I place my curser on any of the cells in the e and f rows, the cursor remains a cross which is what I expect it to do, but columns a to d are doing the same thing, they are acting as if they are locked OR atleast those cells in the a to d areas which have existing data, as I can enter below.
View 7 Replies
View Related
Oct 24, 2013
This is Excel 2010 on Windows 7
Trying to figure out a VB Script but don't know where to start.
I have a sheet for each month. lets take September 2013
A Column with 200 Servers (A1:A200) on the Intranet listed as server-1/, Server-2/ Server-3 ... Server-200/
Each Server lists a location of a file for each day (31 columns per row)
I am trying to check for the existence of the files on each server (The Cells contain the URL and File Name in Range B2:AF201) starting with server-1 check the URL in B2 if it exists, go to C2, Continue with that row until the file check is False (The Page will show a 404 error) if not found delete cell url and continue. do this for all 200 rows
View 3 Replies
View Related
Aug 30, 2012
Looking forward VBA coding for:
If input is entered into column C (range C2:C100), then row cells for columns D, E ,F, H or I must NOT be blank upon save.
Could be that one or more of these row cells are left blank by mistake.
An error msg pops up upon attempting to save, stops the save and colors each cell yellow that needs info entered into.
Using Excel 2010.
View 9 Replies
View Related
