VBA - Allow User Select Locked Cells While Sheet Is Protected

Mar 29, 2012

Here is an excerpt of my code

Code:
Else
Sheet8.Unprotect Password:="rm123abc"
Sheet8.Range("V" & rw) = "Item is not found in the Data Base. Please Contact DME for all documentation requirements for this item"
Sheet8.Protect Password:="rm123abc", AllowFiltering:=True, AllowInsertingRows:=True, AllowDeletingRows:=True
End If

Unfortunately, once the code has run and has finished re-protecting my sheet, it doesn't allow the user to select locked cells. These locked cells contain hyperlinks, and they need to click on these hyperlinks to navigate through my workbook.

So I have two questions:

1. How can I enable allow user to select locked cells
2. Do I have to allow the user to select locked cells to access my hyperlinks, or is there another option?

View 2 Replies


ADVERTISEMENT

Protected Sheet With Timestamps And Locked-after-saving Cells

Dec 31, 2013

I have this excel sheet I'm trying to create (to make my life better at work). It's to track credit card refunds. It'll be saved on sharepoint. So my department will fill in the data on the "Ops" side, and then the accounts department will fill in their side. It's very rudimentary, but anything is better than the old, tattered BOOK we are currently using.

So far, I have the user/date/time stamps autofill, when the user fills in "Entered by" or "Recieved by". I have those cells locked so no one can edit it. I have separte passwords for both sides (Ops and Accounts).

What I want now is to be able to:

1. Lock each line once it's been entered and saved. Say my department fills in one entry. We save it. After that, it CANNOT be edited. If there are any changes, I'll put in a remarks column so people can enter stuff.

2. If someone enters the first two columns (Guest name, booking number), then the "Entered by" HAS to be filled, or the workbook cannot be saved.

3. ONLY letters can be used for Guest Name and Entered by. I've tried different things for this. While I've managed to make it work if the user enters a digit first, I can still enter John56, and it'll pass. I need it to only allow letters (and spaces).

4. The "Booking Number" should only allow six digits. No less no more than six digits. It can be 000001 even. But it has to have six. And, obviously, no letters, no spaces.

In the "ThisWorkbook" module, I've got this code:

Code:
Private Sub Workbook_Open()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="hurlEy!", _
UserInterFaceOnly:=True
Next wSheet
End Sub

In the sheet1 module, I've got this code:

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 3 Then
With Target
If .Value "" Then
.Offset(, 1).Value = Environ("username")

[code]....

View 1 Replies View Related

How To Copy Row With Formula In Locked Cells And Insert Copied Cells In Protected Sheet

Mar 29, 2014

Have you ever copy a row with formula in locked cells & insert it in a protected worksheet?

View 1 Replies View Related

Exl2K User: Sheet Is Password Protected But User Can Change Color In Unlock Cells

Jun 16, 2006

find attached zip file . if you open the file then you can understand the problem.

I need to change cells color and text color as per user choice in Password protected sheet : (user is useing excl2000)

Kindly open attahced file i putted the note inside the file.

View 3 Replies View Related

Allow Cell Formatting On Locked & Protected Sheet

Jun 22, 2008

I have locked some cells on my Spreadsheet so other people can not change them. What I would like to know is how do I change the colour of the font? I also would like to Bold/unbold the font.

View 6 Replies View Related

Allow Locked Cell Comment Viewing On Protected Sheet

Nov 16, 2008

I just realized that some of my comments on a worksheet are being cut off or not viewable. By Default the Comment is displayed to the Right of its cell. I have a Scroll Lock to prevent users from scrolling off the work area. Some of the comments are being displayed past this scroll area and the user is unable to read the comments as they are being cut off or being displayed beyond the scroll area.

Is there anyway to reposition or set the area that these comments are displayed? For Example, to the Left of the cell so that they are able to be viewed. The Workbook is Protected and the Cells with comments are locked so the user won't be able to click on the cell. I found 2 Codes that would work if the user could either click on the cell or if the comment always visible(they are not)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Dim cTop As Long
Dim cWidth As Long
Dim cmt As Comment
Dim sh As Shape
Application.DisplayCommentIndicator _
= xlCommentIndicatorOnly
Set rng = ActiveWindow.VisibleRange
cTop = rng.Top + rng.Height / 2 ................

View 9 Replies View Related

Color Protected/Locked Cells

Oct 7, 2007

How can i set up my spreadsheet so that all protected cells are a different colout to the main sheeT.

I would like this colouring to appear in the sheet when in protected and unprotected mode

View 4 Replies View Related

Keeping Cells Locked And Protected When Copying And Pasting?

Jun 19, 2006

So i have sheet 1 with some cells locked and protected (but open to copying) as a template and sheet two will be where people paste one or more of the template formula on an ongoing basis. How do i mantain the locked and protected cells after they have been pasted? I have unlocked sheet two pasted the first template and had to choose protect cells after pasting, and further pasting of locked cells are not protected.

View 1 Replies View Related

Paste Into Range But Skip Protected/Locked Cells

Feb 29, 2008

I've been trying to paste formula throughout a range of cells in the same column. Trouble is, I protect some sub- total cells and don't want the formula to paste over those sub-total formulas.

If I protect the sheet and paste the formula throughout the column including the protected cells, I would get an error message.

How can I paste the new formulas without getting into trouble of the protected cells?

View 8 Replies View Related

Change VBA To Password Protect All Sheets To Allow User To Select Protected

Apr 8, 2009

I need to change VBA to Password Protect all sheets to allow user to select protected. I have:

View 2 Replies View Related

Select Locked Cells In 2000

Jun 15, 2009

I've created quite a few workbook/worksheets in excel 2007 compatibility mode that are data entry intensive. When protecting these worksheets, you have the option of allowing the user to "select locked cells". I invariably uncheck this option because the user doesn't need to interact with these cells.

However, several of my users are running Excel 2000 and the same option is not available when one protects a worksheet in Excel 2000. Is there a way to do that in VBA for these Excel 2000 users such that when they click on a locked cell, the cursor will not respond to that cell?

View 5 Replies View Related

Disable User Access To Master Sheet Through Doubleclicking Locked Linked Cell

Feb 23, 2010

I have a master spreadsheet that houses most of the information that remains in my control, and several other user spreadsheets that update cells from that master through links.

I am having some problems lately with users doubleclicking the locked linked cells in their workbooks, and excel then wanting to open and give the user access to my master spreadsheet. Is there a way to disable this feature? I would like it to not even acknowledge the action if that's even possible because it confuses them as to why they are getting a prompt to open another document.

View 5 Replies View Related

Submit Data Entry Into Protected Sheet Without Being Able To Edit By User?

Sep 26, 2012

I have created a simple userform that is linked to a button on sheet1. When the data in the userform is submitted, I set it up so it goes into a database under sheet2. The problem is, I don't want anyone modifying the entries in the database. Usually I would protect the sheet with a password, but when I do that, there is an error when the userform is submitted. The only thing I can think of is to hide sheet2, which doesn't seem like a great solution.

Is there any way to protect my database from being modified yet still allow the userform to be linked to it?

View 3 Replies View Related

Insert Row While Maintaining Merged Cells - Locked Sheet

Oct 28, 2011

I have a protected sheet with merged cells. I would like the user to be able to insert a row and have the formatting (including merged cells) duplicated on the new row. I've searched and seen some different options using VB but I'm clueless as to how to use those. Is there a simple way to do this?

Copying the row and using "insert copied cells" won't work because they get an error because of the sheet protection.

View 1 Replies View Related

Unable To Use Arrow Keys To Select Hidden Cells On A Protected Workbook

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

How To Get User To Select A Sheet And Use It Later In Macro

Oct 10, 2013

I have a workbook with several sheets. I have written a macro to check if the value in a column is matching with that in another column in another sheet and return the value in a preceding column in the second sheet into a preceeding column in the first sheet. The code looks likes this and seems to be working fine.

Sub Copy_Price_Code()
Dim Rw As Long
For Rw = 6 To Sheet6.UsedRange.Rows.Count

[Code] ....

However, there would be more sheets in the workbook in future and I need to edit the macro in such a way that the user can select the source and destination sheets which I could use in the above macro. I tried to do it as follows but its not working.

Sub Copy_Price_Code()
Dim Rw As Long
Dim Rw2 As Long
Dim Sourcesheet As String
Dim Destinationsheet As String
Sourcesheet = InputBox(Prompt:="Enter source sheet name. (example: Sheet1).", _

[Code] .......

View 3 Replies View Related

Allow User To Select A Range Of Cells

May 9, 2008

I'm trying to create a macro for another user that she will use repeatedly for many different spreadsheets. Each spreadsheet will have a varying amount of rows that will need to be exported to a different file. She will highlight the cells she wants exported and then hit a button to do so. My problem is this: how do I code a macro so that it will know which cell is the beginning of her selection and which is the end?

View 2 Replies View Related

File Locked For Editing By 'User Name' On Save

Mar 9, 2008

i have this code to run autofilter in protected sheet2, assigned to a check Box,
but it gives me this Msg when i run the code by checking the check Box: File in use.
The file is locked for editing by the (my name). However, when i excute the code by F5 in the VBE window, it works fine also when i excute the code from Worksheet_Activate , it works fine

Sub t()
With Sheet2
. Protect "0", , , , userinterfaceonly:=True, Allowautofilter:=True
.AutoFilterMode = False
.Range("A5:DT5").AutoFilter
End With
End Sub

View 2 Replies View Related

Protected Sheet And UnProtected Cells

Feb 24, 2009

Excel 2007 or 2003 Is there code to be able choose the formating of a cell when right clicked if the cell is Unprotected. But, the sheet is Protect and the Workbook is Protected. Without Unprotecting anything 1st.

The reason being is sometimes the user may need to change the type of font from Arial, or Gdt or Y14.5M.

View 9 Replies View Related

Format Unlocked Cells On Protected Sheet- Mac

Sep 25, 2009

I'm using Microsoft Excel 2008 for Mac version 12.2.0 (090605) (Mac OS 10.5).

I created a sheet, selected some cells to be locked and others to be unlocked (using Format > Cells), then I used Tools > Protection > Protect Sheet (choosing Contents only, not Objects or Scenarios). This has the desired effect of protecting the locked cells from users accidentally typing changes and overwriting their current contents.

I can still enter new data in the unlocked cells as expected, but unexpectedly I cannot change the formatting of unlocked cells (e.g., shading, font - bold). These are *unlocked* cells so even if the sheet is protected, why can't I change the formatting for them? It is too tedious to Unprotect the sheet every time I need to change formatting of an unlocked cell.

I have zero experience with Visual Basic and can't even find options that others have described (e.g., right clicking the sheet name does not yield "Show code" and Tools > Macros does not yield anything comprehensible about VBA). So if your suggestions involve something complex, please do include detailed instructions!

View 4 Replies View Related

Fire VBA, On "Locked,Protected And UnSelected Cell?"

Apr 29, 2006

I want a VBA function to fire "ONLY" when a cell is changed.Peramiters:
Data ertry range is full, Last cell in SubTotals range has changed from $0.00 to anything >, Last 3 cells are Locked, Protected and Unselectable Attached is a copy of the work book. I have posted on VBA Express and we have tried to solve this problem, We have come a long way. You might want to read the history link above.

View 4 Replies View Related

Protected Sheet - How To Correct Text In Unlocked Cells

Mar 14, 2014

The text disappears when I want to correct it (protected sheet). see the discription of the problem in the attachment with figures.

View 3 Replies View Related

Macro To Lock Cells In Protected Sheet Depending On Criteria

Dec 13, 2007

I have a Workbook that I use to log invoices and credit notes and it is password protected so that the cells that contain formulas are locked and therefore not selectable.

What I wanted to know is. Is it possible to lock other cells depending on what values are in certain cells?

Let me explain. I have Columns G & H which contain the heading 'Credit Note Number' & 'Credit Note Amount'. Now in columns I & J I have the heading 'Invoice Number' & 'Invoice Amount' respectively.

What I have done is to make a condional formatting so that If I put a value in cells in column G it will change the colour of the adjacent cells in columns I & J, likewise If I fill in cells in column I the conditional formatting will change the colour of adjacent cells in coulumn G & H. This helps me to see whether a row contains a credit note or an invoice.

What I would ideally like is for a Macro that would automatically locks cells G & H in a particular row that I am currently working on if I enter data into the cell in column I. Likewise if I am entering data in column G in the row I am currently working on for a credit note, then lock columns I & J in the row I am currently in.

Any Ideas how this can be done? Also can locking and unlocking of cells be done in VBA while the sheet is protected or would the macro need to temporarily unprotect sheet, lock the cells then protect sheet again?

PS: I have uploaded a Jpeg of what my conditional formatting does to try and illustrate what it is I need.

View 14 Replies View Related

Getting Data From MDB File Protected With User Level Permissions?

Aug 30, 2013

I need to get the data from an MDB file. No problems normally but in this case the MDB is protected with a workgroup file (MDW) from the old user level permissions that was available in Access.

Is it it possible to get data from the Data ribbon from a protected file or do I need to resort to vba?

View 1 Replies View Related

Protect Certain Locked Cells From Editing And Allow Certain Unlocked Cells To Be Changed On Multiple Worksheets?

Jan 31, 2014

1.I need to protect certain locked cells from editing and allow certain unlocked cells to be changed on multiple worksheets.

2.When all of the changes are made to the unlocked cells, I need to password protect the entire workbook (except one worksheet) from any changes. (i.e. Prevent even the unlocked cells from being edited)

3.I also need a password to un-protect the workbook and return it to the state described in # 1. above .

View 1 Replies View Related

Select All Unlocked Cells On Sheet

Jun 17, 2007

I am trying to select all unlock cells in the active sheet. However, my method checks through each cell in the sheet and it takes too long.

Sub SelectUnlockedCells()
Dim rng As Range

If IsNull(Cells.Locked) Then
'if null then there is unlocked cells
For Each c In cells
If c.Locked = False Then
If rng Is Nothing Then
Set rng = c
Else
Set rng = Union(rng, c)
End If
End If
Next c
Else
End If

If rng Is Nothing Then Exit Sub

rng.Select

End Sub

View 9 Replies View Related

Protect Sheet But Allow User To Unhide Specific Range Of Cells

Jul 19, 2014

All, can I protect my worksheet while at the same time allow users to unhide a specific range of cells?

I'm hiding cell range FA:FK, but based on certain conditions I want to allow the user to unhide the cells but I do not want the user to be able to unhide any other hidden ranges. if there is a way to do this without using VBA. If it cannot be done with VBA

View 1 Replies View Related

Web Query Fails When Sheet Locked

Jan 26, 2007

I have a web query that returns a value to the first column of the row.

I have unlocked the cell (Format -> Cells, Protection Tab, uncheck the Locked check box).

If I run my web query, it works fine. If I then lock the worksheet, the web query fails, even though the cell that I am returning the web query to is not locked.
Code below:

Sub create_pay_proposal(pUserId As Range, pRespID As Range, pWorksheet As Worksheet, pRange As Range, pParms As String)
Dim strURL As String

'On Error GoTo WebConnError 'Error handling if unable to connect to database/URL

strURL = <removed For security>

With pWorksheet.QueryTables.Add(Connection:= _
"URL;" & strURL, Destination:=pRange)
.Name = "fnd_web"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False

View 5 Replies View Related

Select Cells, Then Copy Those Rows To New Sheet

Dec 9, 2009

I'm trying to create a macro that will allow the user to select a range of cells, (in column V in the attached sample), and then insert a new worksheet and copy the entire row where the user selected the cells to that worksheet.

For example: (I've deleted most of the data from the sample). The user want to create an order that has an exteded cube of 300 feet. By holding the "Ctrl" key down, they could pick and choose the parts they want to add, until the sum in Excel shows nearly 300.00 extended cube. They would then run the macro, which would copy only the rows which the user has selected in column V to the new worksheet.

View 4 Replies View Related

Prevent To Take Me To Another Sheet When Clicking On A Locked Cell.

Jul 11, 2009

I have one issue with my protected worksheet. Some locked cell contents are linked to cell contents located in another sheet. When I double clicked on these cells, a warning window poped up but also opened the sheet in which the corresponding cell is located. This is quite annoying to have to go back to the former sheet. How can I prevent it ?

View 3 Replies View Related







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