I have (several) worksheets that have protection enabled. I have unlocked all the cells that users need access to and locked all the column and row headers/labels.
When I enabled protection on the sheets, the ability to add comments was taken away.
Is there a way to add comments to an UNLOCKED cell in a PROTECTED worksheet?
I have A TABLE (Named Table1) and some columns filled by formula. (Like E,F,G,H columns) When I protect the worksheet E,F,G,H columns are locked, but A,B,C,D columns are UNLOCKED. When is sheet PROTECTED , If I add a row bottom of Table1, does NOT automatically fill the E,F,G,H columns and Table1 range does not expanding. But, if UNPROTECTED, it's OK. when add a row bottom of Table1, automatically fill all columns with formula and table range is expanding aotomatically with new row.
When sheet is PROTECTED , How can I allow, to add a row and aouto fill all columns like unprotected.
And I want to protect sheet in any case, after allowing to add row.
I need a macro code to do this.
I was hoping to force end users to use a user form to input data into a work sheet. The problem is that the form wont input the data when the sheet is protected.
View 2 Replies View RelatedI have slight problem with an OnChange Event by Target.
I am looking to be able to change a cell value from one to another, create a comment block that says "person B for person A" , change the color and be done.
If the cell is empty then the code should exit the sub (not working). If the cell has already been change once then I want the comment box to show a second line of who changed what.
What I tried in the comment box, thinking it would keep the value:
I have the following code which deletes the specified value from each cell in column B that contains that value, (note it only deletes the value from that column and not all in the row).
Code:
Sub delete1()
Dim lr As Long, i As Long
With Sheets("Database")
lr = .Range("B" & Rows.Count).End(xlUp).Row
For i = lr To 2 Step -1
If .Range("B" & i).Value = 1 Then .Range("B" & i).ClearContents
Next i
End With
End Sub
Now what I need is to adapt it slightly so that it also adds a comment to the cell in Column I and the current date in Column L, (on each row where the deleted value was).
The comment would be something like "old data archived" and the date in any format, preferably dd/mm/yyyy.
Is any way to add automatically comments in VBA
Some combination of "ctrl" with something else.
I need to identify any cells in a range that do not contain text then colour and comment them. I intended to use the following code, however it doesn't work as I expected it to.
With rRange.SpecialCells(xlCellTypeBlanks)
If .Count > 0 Then
.Interior.ColorIndex = zErrorColour
.AddComment "This cell must contain a value"
End If
End With
The blanks cells are coloured OK but only the first blank cell gets a comment. Is there a reason why I can't add comments to all the cells in the range? And if there is a good reason, then I guess I'm going to have to write a loop to add the comments.
When adding a comment I check for an existing comment, if exist then delete and add new comment and some text in that cell. This works as long as the cell has an existing comment; other wise it advances to the "Else" where I thought I would be entering a comment and text in an empty cell. Why does my macro treat an empty cell as if it has a comment?
View 4 Replies View RelatedExcel 2007
I have a few dozen pictures created when a macro runs. They all have unique names. I'd like to add comments to cells, where the cell.value decides which picture to pull. All the examples I've found online show how to do this if you have pictures saved on your hard drive by referencing the file path "c://mydocs/...blahblah/"
Is there a way to reference the pictures I've created/named with my macro?
Here's the snippet of code that creates the pictures and names them:
Code:
For i = 2 To Application.CountA(Sheets("Allocation").Rows(1))
Sheets("Allocation").Activate
Set rInput = Sheets("Allocation").Range(Cells(1, i), Cells(10, i))
sPicName = "_" & Sheets("Allocation").Cells(1, i) & "_"
sSheet = Sheets("Allocation").Cells(3, i)
dDate = Sheets("Allocation").Cells(5, i)
[Code] ......
Here are some examples that are close to what I'm looking for.
VBA Popup Pictures - 1108 - Learn Excel from MrExcel Podcast - YouTube
VBA Express : Excel - Add pictures that float like comments.
I'm trying to manage my Stock by using protected sheet, but i stuck with this "row adding with formula" stuff.
The user can only write in the colored cell, but everytime the user add new row, the formula doesn't copying by itself
I attach the file below with no password, if it's protected, the password is none, just press enter ( password : "" )
Book2.xls
I have a table of data 300 rows long with protected formulas in adjacent columns and at the bottom of the column. Some of the data cells are blank but the non-blank cells are contiguous and start at the top of the range (see Before Example).
a macro that would allow users to insert a blank cell at any point in the data table without losing any of the other data or disrupting the formulas. In other words I want to move all the data from that point to the bottom of the range down by 1 row. A before and after example for a smaller 10 row data set is shown below.
Before
AB1data aformula 12data bformula 23data cformula 34data dformula 45data eformula 56data fformula 67 formula 78 formula 89 formula 910 formula 1011formula Aformula B
After
AB14data aformula 115data bformula 216data cformula 317data dformula 418data eformula 519 formula 620data fformula 721 formula 822 formula 923 formula 1024formula Aformula B
One of my work colleagues need to add a worksheet to an existing Excel Spreadsheet which has VBA behind it, but the worksheet is password protected. The developer who wrote the application has now left, so we have no idea what the password is. Is there anyway of getting round it, like cracking into the spreadsheet to find out the password, or another way?
View 2 Replies View RelatedI have a workbook that I received from a vendor that when I click in a cell a comment appears. There is no line or comment indicator for each comment. So I went into the tools - options - views - comments section and chose no comments. The comments still appear and when I go to the tool bar - view - comments nothing appears. I checked for any VBA code and didn't see anything. I was able to get around this problem by copy/paste special into a new worksheet, but curiosity is killing this cat.
View 2 Replies View Relatedhow to turn on the Comment Indicator through VBA? The code equivalent of setting Tools / Options / View and then selecting the Comment Indicator Only option.
View 2 Replies View RelatedUsing Excel 2007 I duplicated a worksheet by right clicking the worksheet tab and selecting Move or copy... with the Create a copy option. Of the six comments on the original worksheet only one appears in the duplicate.
View 2 Replies View RelatedI have an inventory sheet in which I am trying to accomplish two things:
1. Have comments pop-up when certain cells are selected that will explain to the user what kind of information he should enter. (So far, I have been able to make this work.)
2. When the user begins to enter information into the first of the aforementioned cells, a worksheet change event will automatically select the next cell for him, so that he will be immediately ready to enter information into it. That is, the worksheet will automatically "tab" over to the next relevant cell. (I'm having problems with this part.)
All in all, for each product on the inventory list, there are three cells that the user must enter information into.
Step 1: The ending inventory amount (the amount of product on hand).
Step 2: The user's initials.
Step 3: The time that the user physically counted the inventory.
(Then, when step three is done, the worksheet automatically tabs to STEP ONE ON THE NEXT ITEM. That is where I am failing miserably, although that is not the only place. . . . Keep reading.)
Here is my problem. I am not able to have my cake and eat it too. The comments are popping up beautifully, at the right time in the right way. However, when I enter the appropriate lines of code to make the worksheet change "tab" over the next cell, problems occur: the comments that provide instructions to the user do not stay visible. They flash for a split second, and then disappear. I am attaching a sample so you can see what I mean (and then I'm heading off to lunch with the boss, so it will be while before I can respond). The lines of code that I would like to have working are currently "commented out."
I'm attempt to automatically validate QTP spreadsheets with an EXCEL VBA macro. All is going well but I now want to look smarter. Is there a method to globally remove all comments from a worksheet?: D What I actually want to do is add comments to cells which fail validation but some of these cells may already contain comments. If they do contain comments then I want to overwrite with my comments. I thought the easiest way would be to delete all comments at the start of macro but I am all ears to any other approach.
View 4 Replies View RelatedI have a form that has three fields (1. Comments (TEXT), 2. Legacy_Comments (TEXT), 3, Comment date (DATE))
Now my users need to keep adding comments to the comment text box, and when they do it automatically adds the date they entered the comment in the Comment date box. Now my problem is that since they keep adding comments to the comment box, I need to keep track of these comments in the Legacy_Comments (Text box).
For example, the First time a user enters a comment into the (1) comment text box it auto populates the date in the comment date box, and then adds the comment and date to the Legacy_Comment box. the end result is (comment,4/3/2014 now lets say a user needs to add a comment to the comments box tomorrow - I want the legacy_Comment box to then read (comment, 4/3/2014; comment2, 4/4/2014, ...., comment(n),date(n)) OR it can be vice-verse, because I just need to keep track of the comments, I am not worried if the new comments are before or after older (yesterdays / the day before yesterdays comments)
How can I write a VBA code that will always add the new comment to the legacy_comment field, without deleting the comments that were entered previously?
Code:
If isnull(me.comment.value) Then
Exit Sub
ElseIf me.comment.value = true Then
me.comment_date.value = date
me.legacy_comment.value = me.comment.value & "," & me.comment_date.value & ";"
me.legacy_comment.value = me.legacy_comment.value & "," & me.comment_date.value & ";"
It adds the comment only the first time, but it does not concatenate the string from yesterday to the string to today. I do not care which order the comments are, meaning if I added a comment today it can be before OR after the comment from yesterday.
I have this workbook to keep track of current and new work for the team - each individual inputs information on their own tab.
In turn, the current information is displayed on a summary page so we can all see who's doing what.
I want to protect that summary page and its formulas from accidental amendment.
However, while the summary page (when unprotected) updates as soon as someone enters new information on their own tab, the summary page won't update when I have it protected.
Is there any way of doing this - preferably without VBA as it's a work situation and the employer doesn't like VBA code running?
I want to run a macro in a worksheet which is proteced.
It's a simple macro which erases the content in (some) cells, nothing more. Even those celles are formatted as unlocked the macro fails.
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] ....
I have a protected worksheet where I allow all users of the worksheet to filter yet when I filter, Excel gives a run time error 1004 - you cannot use this command in a protected worksheet. Could someone let me know what am I doing wrong?
View 9 Replies View RelatedI've built a workbook using Excel 2000 that uses several combo boxes. When I try to protect the workbook the combo boxes become locked, even when I have done FORMAT CONTROL / PROTECTION and unticked the LOCKED box. Is this normal?. I also have data validation cells and they work fine if I unlock them and Protect the workbook. As a result, I am thinking of converting the combo boxes to data validation cells, but should I even need to do this
View 3 Replies View RelatedI have the following macro and when it "reprotects" my worksheet I need it to also turn on these Protect Sheet properties:
Format cells
Format columns
Format rows
Insert hyperlinks
and I need it to leave on the defaults of Select locked cells and select unlocked cells
Sub Spell_Check()
ActiveSheet.Unprotect Password:="pmo"
Cells.CheckSpelling "SRdictionary.dic", SpellLang:=1033
ActiveSheet.Protect Password:="pmo", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub
I have a data list which needs to be updated by others on a shared drive. I want to protect the worksheet as there is other info on it (advancefilter from the main list). The problem is that the >>Data>>Form tool will open, but all the boxes necessary to update the list are greyed out (New, Delete etc.)
My other option is to create a macro to advance filter out the other data to another worksheet.
What if you forgot the password in a protected sheet? is there a way to unlock it?
View 1 Replies View RelatedI have created a table where the first 6 columns are data entry and the next 6 columns are formulas to give desired results.
I have made all the data entry cells unlocked and the rest of the spreadsheet locked (cells with formulas etc.)
I would like to be able to insert rows to the protect sheet which I have managed to achieve but when I insert the row the formulas do not update in the row.
I was able to create a macro that allows a user to unhide rows in a protected worksheet without unprotecting the other locked fields (see below). My question is: I want to add to this command so that 1 row can not be "unhidden" if the previous row is still hidden. For example, row 25 can not be unhidden if row 24 is still hidden.
Macro command used:
Private Sub Commandbutton4_Click()
Sub Hide_Rows2h()
ActiveSheet.Unprotect Password:="xxx"
Rows("25").Hidden = Not Rows("25").Hidden
ActiveSheet.Protect Password:="xxx"
I have the following VBA in my spreadsheet.
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
ActiveSheet.Unprotect ("kellyz")
If Intersect(Target, Columns("o:o")) Is Nothing Then Exit Sub
For Each C In Intersect(Target, Columns("O:O"))
[Code] ......
As soon as i type anything in the columns, my sheet is unprotected. I need the sheet protected so that the users can not change my formulas.
I have a protected worksheet that from time to time I need to insert a new row and copy a specific range of cells that are with formulae (protected) to the newly inserted row. For a better idea of what I want to achieve, a snapshot of the worksheet is attached.
******** language="JavaScript" ************************************************************************>Microsoft Excel - ACR-INFRA.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutI23K23M23F24H24I24J24K24L24M24N24F25H25I25J25K25L25M25N25H26I26J26K26L26M26N26D28E28F28G28H28I28J28K28L28M28N28D29I29=
ABCDEFGHIJKLMNO22CR NoCodeStatusEstimateContr's PriceDeltaAgreed ValueCommitment Impact Impact ImpactFunded from23 2,730,382.00 320,000.00 180,000.00 241380AIA180,000.00
180,000.00 2,910,382.00 (180,000.00)140,000.00 0.00 180,000.00 0.00 252482AII
0.00 2,910,382.00 0.00 140,000.00 0.00 180,000.00 0.00 26 0.00 2,910,382.00 0.00 140,000.00 0.00 180,000.00 0.00 27 28 180,000.00 0.00 0.00 0.00 180,000.00 2,910,382.00 (180,000.00)140,000.00 0.00 180,000.00 0.00 29 A0.00 2,910,382.00 1036
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I would like to insert the new row at row27 downwards each time the macro runs.