Odd One, Up And Down Arrows Stop Working When Sheet Protected

Jan 10, 2008

The sheet was working fine, then all of a sudden (i dont know what i have done) the up and down arrows (on my kayboard) wont work when the sheet is protected, when i unprotect the sheet they work fine, i can use left and right. i have checked the scroll lock isnt pressed/illuminated on my keyboard.

View 9 Replies


ADVERTISEMENT

Macro Not Working When Sheet Protected?

Sep 19, 2013

I have certain cells which I want the formulas hidden to the end user and protected so they can't update them.

I've locked and hidden these cells and protected the sheet however my Macro's fail to work thereafter.

Protect the sheet for the user interface only. This has to be done in VBA:

- Activate the sheet.

- Activate the Visual Basic Editor (Alt+F11).

- Activate the Immediate window (Ctrl+G).

- Type

ActiveSheet.Unprotect

- Press Enter.
- Type

ActiveSheet.Protect UserInterfaceOnly:=True

- Press Enter.

The sheet is now protected for the end user, but VBA code can freely manipulate it.This works well however when I go to another page and do the same it stops working on the original sheet.I would like to know if there is an easy way to:

protect/unprotect all sheets in one go and have the macros still work?

When I protect pages individually I want the following to be allowed:

Select unlocked/locked cells
Format
Sort
Use autofilter
use pivottable

I will first save which cells are un/locked & hidden.

View 9 Replies View Related

Hyperlink Not Working If Sheet Protected

Jun 1, 2009

I have a excel worksheet with hyperlink. If the sheet is protected I am unable to use the hyperlink even though I have unblocked the protected cells.

View 9 Replies View Related

Me.Rows.Autofit With A Protected Sheet Not Working.

Jul 23, 2009

Me.Rows.Autofit with a Protected Sheet not working.
Basically I want to use vb code which is

View 2 Replies View Related

AutoFilter And Sort Not Working On Protected Sheet

Dec 20, 2012

When I either manually check the box for allowing sorting and autofiltering, or I do it through vba with:

Code:

With Sheets("Inventory")
.Protect Password:="###", AllowSorting:=True, AllowFiltering:=True, AllowUserInterface:=True
.EnableAutoFilter = True
End With

I still am unable to use the icons. The icons are clickable to filter or sort, but every time they say that the cells I am selecting are protected. I want them to be protected from editing the data, but want to allow them to be sorted or filtered.

View 1 Replies View Related

Macros Not Working When Sheet Is Password Protected

Jan 9, 2007

I am trying to password protect some of the cells in a worksheet. However doing that starts giving errors with Macros. The Macros are no longer working and infact giving error. What can I do to stop that happening?

View 9 Replies View Related

Dynamic Ranges (tables) Not Working On Protected Sheet?

Sep 10, 2013

I have a data set that is defined as a table (Ctrl + T). There are formulas so I locked those cells and Protected the sheet. If I am at the bottom right cell, and press Tab to add a new row, I get an error message: "Cannot use this table functionality on a protected sheet"

I really need to protect the cells with formulas so that the are not accidentally deleted. But I also want to retain the Table format.

Is there a way around this without using VBA?

View 1 Replies View Related

VBA Not Working On Another Worksheet When Protected

May 20, 2012

I have the following code which works fine when Sheet1 is unprotected (The code is placed in Sheet3)

however if I protect sheet1 then the code does not work

I have tried it with

Code:
ActiveSheet.Unprotect

Code:
ActiveSheet.Protect
it still will not work on Sheet1

here is the code

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
On Error GoTo Handler
With Sheet1.Range("B6:B10045")

[Code] ....

View 2 Replies View Related

VBA Macro Not Working When Workbook Is Protected

Apr 23, 2008

I wish I could have been more specific with my Title. This is what I am encountering. I used the McGimpsey code for generating a unique sequential number every time a workbook opens. I tailored the code to fit my needs and stuck it into my invoice sheet. For the longest time I was unable to make it work until I moved the code from a template to a standard workbook. Then I was able to make it work as long as the sheet was unprotected. I realized that to get the code to work I had to then unprotect the cells that the code was writing to. Now that I've done that I am getting errors in the code that weren't there before, rendering the macro useless. The code I am using is:

View 14 Replies View Related

Arrow Keys Not Working On Protected Worksheet

May 4, 2008

Have a file that I have been coding and find when I open to a worksheet with limited access cells, the Up and Down arrows will not move vertically though Lrft and Right arrows OK though not raping arround. This problem may have been on for sometime and I haven't been back to it. Other Excel files all OK.

View 4 Replies View Related

Macro Stop Working With No Error

Jul 24, 2006

I have wrote a simple macro/ function that used to work before I decided to improve it. When I run the macro, I can step F8 until the before last line in the following code

Function bla() As Boolean
Set rCell = Cells(1, 1)
If rCell.Value <> "" Then
x = rCell.Value * 0.02 'Works
rCell.Value = rCell.Value - x 'Doesn't work
End If
bla = False
End Function

Excel does stop running the macro, but it doesn't show any error so I don't know what to fix.
Steps to reproduce the problem:
1. Create new Excel document
2. Type any number in the very first cell
3. Open VBA, add module and copy above function
4. Type in another cell "=bla()"

View 3 Replies View Related

Backup Script Stop Working

May 15, 2007

I have trouble with the last part of a backup script, this part checks in subdir and should delete the oldest file (of 4) but since a while back it instead delete the new file saved earlier in the script and I dont know how to fix it. Here is the ...

View 5 Replies View Related

Hyperlinks Stop Working When Page Is Sorted?

Oct 15, 2013

I have a Master sheet with data on it that corresponds with multiple sheets in the same workbook that I have links to in the Master sheet. It all works fine until I sort the Master and then my links start sending me to the wrong worksheet.

Is it just the nature of hyperlinks that you can't sort, or is something else going on here?

View 2 Replies View Related

Stop Working Out File Paths For Referenced Workbooks....?

Apr 29, 2009

I'm working on a sheet which references a sheet in another workbook. I want to refer to the other workbook simply by it's filename (Forecast.xls) but whenever I save and reopen Excel has replaced the filename with the full path (C:Documents and SettingskoconnorMy DocumentsAuto-sheet project[Forecast.xls])

This is quite inconvinient as I am eventually going to want to move both workbooks together to a different location. Is there a way to make Excel stop working out full file paths and just look for the filename in whichever folder the active sheet is sitting in?

View 3 Replies View Related

Stop Close Form Button Working On Userforms

Jul 16, 2007

I have a useform that loads up with two buttons and I waat to force the user to chose one of the buttons. However, I have noticed that the user can just press the cross in the top right corner of the form to close the window and therefore not press either button.

Is there anyway I can stop the close form button working?

View 4 Replies View Related

Formulas Stop Working When Put The Rows Of Data To Test My Spreadsheet

Mar 17, 2009

I am inputting rows of data to test my spreadsheet and all of the sudden the formulas stop working??

Can someone take a peak at it and see if they can tell why its no longer working at I19?

View 5 Replies View Related

Excel 2010 :: Macro Runs On Protected Sheet But Changes Protect Sheet Options?

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

Copy And Paste From One Sheet To Another On Protected Sheet?

Apr 29, 2014

I have spent ages creating a work diary and have protected the sheets using VBA however on finishing the sheet one important thing that needs to happen is the ability to copy and paste information from the boxes to another worksheet. i.e MONDAY - SUNDAY

View 1 Replies View Related

Password Protected Sheet :: How To Unprotect A Sheet?

Jan 18, 2008

Does anyone know if there is a way to unprotect a sheet. The person who created this spreadsheet no longer works for our company and although we can see the spreadsheet, the formulas are hidden so we can't figure out where the data is being pulled in from. Any ideas?

View 9 Replies View Related

Stop Macro From Changing Onto Sheet Top Sheet And Flickering

Dec 24, 2008

How can I stop my macro from changing onto sheet top sheet and flickering away like mad??

View 2 Replies View Related

Cant Use [alt] + Pg Up Or Pg Dn On Protected Sheet

Jun 11, 2008

is there a code to still allow me to move between sheet tabs by using [alt] pg up or [alt] pg dn on a protected sheet?

View 9 Replies View Related

If Sheet Protected Then...

Apr 26, 2007

What code would I use to test if the sheet is protected or not? In other words:

If Activesheet.Protection = True Then
MsgBox "You have no authority!!!"
Else
' Some code here
End If

View 5 Replies View Related

Searching A Protected Sheet

Feb 11, 2010

I have a workbook whose worksheets use protection (don't want my calculations stomped on).

When the Protection is on, the search function does not work. You call it up, write what you are looking for, it accepts it, and then when you tell it to search it ignores you.

When you turn the protection off, the search function works just fine.

HOWEVER....Other workbooks I have with protection on its sheets do not share this problem, just this one workbook. And the problem is on all sheets in this workbook. The problem is not on any of the sheets in the other workbooks. I can't see anything different between them, but then I may not know what to look at.

View 2 Replies View Related

Allow Insertion Of Row In Protected Sheet

Sep 4, 2013

I wanted to know:

Q1. How can we insert new rows in a protected sheet?

For example: In the attached sample protected excel sheet "insert rows.xlsx" , if for Employee ID and Name fields, the user has more no. of records than provided, how can he insert new rows considering the fact that Company ID and Name field are locked.

Or any way to address this issue via some other method like adding a scrollbar,etc.

View 5 Replies View Related

Protected Sheet & Allow Use Of Autofilter

Dec 20, 2007

I've locked certain columns on my worksheet so that users cannot overtype target dates etc. I've password protected the worksheet.

The password protection means that for some reason the users can't use the autofilters that are on the header row.

Can someone help me solve this problem; I still need the locked cells and password protection on the sheet but the success of the sheet depends on users being able to filter for specific rows using the autofilter....

View 9 Replies View Related

Protected Sheet Will Not Allow Macro To Run

Mar 27, 2009

I have protected several sheets in my workbook to protect the forumlas and data for being tempered with by the users.

When I try to run my macro, I get an error message stating:

Runtime '1004'
You can not use this command on a protected sheet

And the debug is pointing to this part of my code

View 14 Replies View Related

Autofiltering A Protected Sheet

Dec 11, 2006

When I protect my sheet and have the option to allow the users to autofilter the sheet, how come it doesn't work? I want to allow people to autofilter the data in the worksheet but not modify the contents of the cell.

View 9 Replies View Related

Use Dropbox When Sheet Is Protected

Jan 26, 2009

I have a dropbox located on a sheet which other information needs to remain protected.

How do we give access to users to the dropbox while the remainder of the sheet is protected?

View 9 Replies View Related

AllowFiltering In Protected Sheet

Nov 10, 2009

I have a question relating to the use of autofilter in a protected sheet.

My code is working fine in setting the autofilter and locking down the sheet but a user then loses the ability to use the (Data - Filter - Show All) menu button.

Is there a way to have the worksheet protected, allow the use of the auto filters, and allow the use of the show all button?

Code below incase it is of relevence ...

View 9 Replies View Related

Copy Down Last Row On Protected Sheet

Jul 31, 2008

I am looking for VBA code that will unprotect the Workbook, prompt the user for the number of rows to be added, find the last row in the Worksheet, insert the requested number and reprotect the Worksheet. I would like the location where the rows are inserted to NOT be dependent on the "active" cell.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved