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


ADVERTISEMENT

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

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 View Related

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

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

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

Comment Boxes On A Protected Worksheet

May 22, 2006

I am being asked to create a workbook where 80% of it is protected. And user input is restricted to specific columns and rows.

However the user wants column R unprotected so that Comment boxes can be used, but when I protect the workbooksheet the Insert option is not available when you right click.

View 9 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

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

Protect Sheet From Viewing?

Jan 18, 2012

I have a workbook with several worksheets. I need to prevent users from viewing worksheet mgrview unless they have a password for it.

View 1 Replies View Related

VBA - Protected Sheet But Enable Cell Range?

Jun 26, 2014

Have wrote the following code to enable 7 sheets to be unprotected, the pivot tables and charts housed within them to be refreshed then the sheets to be protected. However, once the macro has finished I need to be able to still use the filters on the pivots to flick through data. I also need the data table to be protected with the exception of a cell range that can be edited (preferably password protected.

Sub Refresh()
'
' Refresh Macro
' Refresh Pivot Tables/Charts

[Code].....

View 1 Replies View Related

Set Page Breaks Based On Cell Value On Protected Sheet

Mar 1, 2008

I'm not sure went wrong but my code was working fine until I protected the worksheet. Then I made some changes to the code by adding code to unprotect the sheet before running the code and then protecting it again when complete. That didn't work. I unprotected the sheet and ran the code again and now I'm getting a Run Time Error and the code stops when it attempts to set the Page Breaks which was previously fine. Because the data I use to generate this report is based on data from another report that does not need to recalculate before running. I have a Worksheet Activate Private Sub that sets the Application Calculation to Manual. When the sheet is deactivated it sets it back to Automatic. Hence the line in the following code to calculate this worksheet. When attempting to debug the line that highlights is the first line that attempts to set the page break that corresponds to the matching location criteria from cell A2.

Private Sub Worksheet_Change(ByVal Target As Range)
Application. ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.ResetAllPageBreaks
Range("A24").Value = Range("A1")
Range("A25").Value = Range("A2")
Range("A26").Value = Range("A3")
Range("A27").Value = Range("A4")
Range("A28").Value = Range("A5")
Range("A29").Value = Range("A6")
Range("A30").Value = Range("A7")
Range("A31").Value = Range("A8")
Range("A32").Value = Range("A9")
Range("A33").Value = Range("A10")...................

View 4 Replies View Related

Excel 2003 :: Unprotected Cell Won't Allow Input On Protected Sheet

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

Double-Click Cell On Protected Sheet To Open UserForm

Apr 23, 2008

I have spreadsheet with client names. I'd like to build logic that will open a form with client information when a user double-clicks on the client name. But I have a couple of problems.

- First, I'd like to protect the sheet and hide the formulas. But when I protect the sheet, I get the "the cell or chart you are trying to change is protected" error when I double click the protected cell.

- If I deselect the "Select Locked Cells" options when I protect the sheet, I no longer have the ability to double-click on the cell.

how I can double click on a cell yet not allow the user to edit the cell (or see the formula)?

View 2 Replies View Related

VBA Macro To Auto Change Cell Color On Protected Sheet Based On Input

Apr 13, 2009

I have a macro code for conditional formatting. The first 2 lines of the macro are


Private Sub Worksheet_Change(ByVal Target As Range) ....

View 9 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

Condition Formatting - Viewing CF Formula At Each Cell

Jan 6, 2010

I'm referring to a post I tried to solve:http://www.excelforum.com/excel-gene...ml#post2227328

NO need to read. Try the attachement instead.

Steps done:
Select Column D
CF function =D1<A1
Select Column A
CF function =A1<D1

If I select cell D3 and look at the CF then I see:
CF: =D1<A1
where I would like to see
CF: =D3<A3

I remember this was the case in <=XL 2003? (now using 2010 beta)

View 4 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

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 Contents Of Multiple Comment Boxes And Paste In Single Comment Box

Mar 13, 2014

how to copy the contents of multiple comment boxes and paste in a single comment box.

The big picture is that I have a number of cells with numerical values in and text in comment boxes. I want to be able to click a button to copy the contents of the comment boxes and paste them, along with the numerical value from the cell, into a single comment box, ordered by highest to lowest value within the comment box, then delete the original cells and comments.

I am quite new to VBA but have been coping quite well so far with information of the web and analysing recorded macros.

View 6 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

Insert A Comment Without Unprotecting Sheet

Sep 28, 2007

scenario: sheet & work book are protected to users; however need them to sometimes insert a comment to this sheet.

all columns/rows are locked except columns K, R, Y and every 7th column thru CJ (rows are from 4 - 100).

how do I enable a user to insert a comment without unprotecting sheet / WB? note: there's no pattern, it could be any cell within aforementioned columns.

View 9 Replies View Related

Macro To Add Comment To All Cells Containing Text Within Sheet

Jul 11, 2013

I am in need of a macro that will copy the text from A1, and paste it to a comment on every cell that contains text within sheet 1.

View 4 Replies View Related

Insert Text As Comment On Another Sheet Based On Date

Jan 18, 2013

Is it possible to insert text as a comment on another sheet based on a date?

I have Sheet 1, that has 3 columns, Name, Date, Reason

Sheet 2 is a monthly calendar with the dates in E5:AH5 and the names from D6:D10.

What I am trying to do, is when they enter their name, date and reason on sheet 1, I want the reason to to be inserted on sheet 2 as a comment in the cell that matches the date and the name.

View 1 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







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