Is There A Method To Globally Delete/remove All Comments From A Worksheet?

May 19, 2006

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


ADVERTISEMENT

Cannot Remove Comments In Worksheet

Nov 6, 2008

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

(worksheet).delete Method Causes Project Reset

Jun 26, 2008

I have encountered what seems like a very frustrating and stupid Excel vba bug.

When I use the worksheet.delete method. My vba project is RESET.

All forms object classes and variables disapear into the BIT BUCKET.

View 9 Replies View Related

VBA- Getting A Run Time Error 1004 Delete Method Of Worksheet Class Failed

Dec 28, 2009

i am getting a run time error 1004 Delete method of worksheet class failed,

ws.Delete


Sub Save()
Dim myName As String, myFolder As String, e
Dim fso As Object, temp As String

ThisWorkbook.Save

Set fso = CreateObject("Scripting.FileSystemObject")

myFolder = "C:UsersRecsDocumentsTestVBA & ExcelEmail_Files_Temp" & Year(Date) & "" & Format$(Date, "mmm")
For Each e In Split(myFolder, "")
temp = temp & IIf(temp = "", "", "") & e
If fso.FolderExists(temp) = False Then fso.CreateFolder (temp)

View 9 Replies View Related

Remove Lines From Comments

Apr 20, 2009

Here is a picture example of what I mean, just randomly found on google:
http://www.projectwoman.com/uploaded...ent-763577.jpg

Maybe an insignificant thing I want, but can the line from the comment connecting to the red indicator be removed? Added an example since my comments show up below the selected cell with VBA code.

View 8 Replies View Related

Remove Comments Showing Up On Mouse-over

Apr 18, 2009

In the example added a code is running to show the comments (pictures) when the cell is selected. This way you don't have to mouse-over to show the comment, you just use your arrow keys.. Is there a way to not show comments on mouse-over? I only want them to show while the cell is selected, using the VBA code. Because now you can show 2 comments while a cell is selected, and you put your cursor on another cell.

View 2 Replies View Related

Remove Comments Showing Up On Mouse-over?

Apr 18, 2009

In the last thread Andy gave me this code to only show the comments when a cell in Column A is selected. It doesn't show them anymore when you put your cursor on a cell. But when 2 excel files are open, including the example, and you close the other one it asks the regular stuff; Do you wan't to save .. etc. It also askes that with the example added, and when you press cancel, the code doesn't work anymore and I have to re-open it. Or if, for some reason, you close the sheet, but still want to edit something, press cancel, the code doesn't work anymore. How can this be changed?

View 6 Replies View Related

Unable To Delete Or Edit Comments

Feb 9, 2009

I have a comment which I wish to edit, but I am unable to either edit or delete the comment. I have checked the 'shared workbook', 'shared worksheet', and 'protected cell' options and they are all turned off (never had them on).

The cell I have the comment on has a dropdown box, but I can't see that being a problem. I am able to add a comment, which I can edit and delete, but the original comment seems to be 'stuck'.

View 3 Replies View Related

Macro To Delete Comments And Unhighlight Cells

Oct 24, 2007

I am having trouble writing a macro to do the following. Someone had created a spreadsheet a while back that highlighted the cell green and inserted a comment with the person's name and date/time if they changed anything in that cell. The macro works great, but I need a macro to undo the changes.

I would like a macro that will remove all comments from the sheet and revert the cells back to their original color. Since the cells encompass many colors, at the very least, I would like the macro to remove the comments and make the cells the default shading (white).

View 9 Replies View Related

Comments From Dropdown List - Delete If Cell Value Deleted

Sep 11, 2013

I created comments to be shown when drop down list items are selected using the helper cell and workbook change solution from Leith Ross at the thread below.

[URL]....

This works perfectly to show the comments. What I'd now like to do is delete the comment if the content of the cell is deleted.

View 5 Replies View Related

Make Changes Globally

Dec 18, 2007

I would like to create a master workbook that can moderate other workbooks referenced by it. So something that is set up like a regular template, yet when I make changes to the master workbook, all documents that reference it will make the same change.

The only possible way I can think of doing this is linking, but 90% of the document will be all linked up and knowing how fragile links are I was hoping there was another way to do this.

View 3 Replies View Related

Turning On Comments On Worksheet

Apr 23, 2007

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

Comments With Copied Worksheet

Jul 31, 2007

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

Adding Comments To A Protected Worksheet

Jan 20, 2010

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?

View 5 Replies View Related

Worksheet Change Removes Comments

May 11, 2007

I 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."

View 5 Replies View Related

Workbook With A Globally Defined Named Range

Feb 19, 2010

I have a workbook with a globally defined named range "MyArray" which refers to a constant array = {"Item1","Item2","Item3"}. My general module contains the following

View 4 Replies View Related

Excel 2007 :: Method Delete Of Object Range Failed

Oct 3, 2011

I have written a sub with the purpose of finding rows based on a critieria, copying the rows to another worksheet and deleting the original row.

The code:

Code:
Sub MoveToIgangvaerende()
'
' Flytter opgaven til sheetet Igangværende projekter
'
Dim i, j, Index As Integer
Forfra:
For i = -1 To 1000
If Sheets("Pipeline").Cells(3 + i, 20) = "Ja" Or Sheets("Pipeline").Cells(3 + i, 20) = "ja" Then

[Code] ......

I get the error message "Method 'Delete' of object 'Range' failed" at the line "Sheets("Pipeline").Cells(3 + i, 18).EntireRow.Delete".

The sub has previously worked perfectly fine.

View 4 Replies View Related

Delete Method Of Range Class Failed - Error 1004

Feb 9, 2012

I'm attempting to write an OutLook 2007 macro that extracts excel spreadsheets from incoming email and saves them as a .csv file. Before saving the file I need to delete the header row. I am getting a "Error 1004 - Delete method of range class failed" error when running this code.

Here is the code: (the bolded line is where I'm getting the error)

Public Sub ConvertToCsv2()
Dim xls As Excel.Application
Dim oWB As Excel.Workbook
Dim tmp As String
Dim ws As Excel.Worksheet

[code]....

View 7 Replies View Related

Method Of Worksheet Class Failed

Aug 29, 2007

I have a worksheet "Create Origin Zones" - sheet #17 in the array - that has 56 checkboxes.

Users can click anywhere from 1 to 56 checkboxes, and for each checkbox that is checked, I unhide a sheet "Shp Profile Tmpt", copy it after sheet #17, rename it to "Origin " + checkbox#, and give it a title based on a variable in another sheet.
Once that loop is done, I then hide the "Create Origin Zones" sheet, but the user can click a button on the new sheet to go back to the 'Create Origin Zones" sheet and add more zones by clicking more checkboxes and re-running the macro.

Problem is I get that "Run-time error '1004':Copy Method of Worksheet Class failed" when I copy too many sheets. All the solutions I saw involve saving, closing and reopening the workbook but this interrupts my loop. (It would save my workbook and close it.)

I am trying to have somewhere in my loop, say every time 20 or more checkboxes are checked, its saves, closes, reopens and continues the loop to the next checkbox and repeats the copy and paste and renaming etc.

Below is my

Sub pick_origin()

chkcounter = 0

shtnum = Sheets("Create Origin Zones").Index ' find sheet# of 'Create origin zones' to copy sheets after

For i = 1 To 56 'for the 56 origin zones checkboxes

View 4 Replies View Related

Paste Method Of Worksheet Class Failed

Aug 21, 2014

I have an application I just built last week that seemed to be working fine. It's purpose is simple. It allows the user to select multiple .rtf files from a network drive, and then it loops through them, opens them up one at a time, copies the entire contents into a "temp" sheet in the excel workbook, then copies certain data from the temp tab to the next two columns in the main "data" tab of the worksheet, deletes the temp sheet, and then repeats the process for each selected .rtf file. Finally , it does some formatting of the "data" tab. All in all, it works quite well. Or at least, it DID.

VB:
Sub CopyRTFDocToTemp()
Dim WordApp As Object
Set WordApp = CreateObject("Word.Application")
With WordApp

[Code] .....

As of last night, we started getting various errors.

"Run-time error '1004': Paste Method of Worksheet class failed" error messages occasionally. The frequency seems to have increased.

We were also getting an error message that reads "Microsoft Excel is waiting for another application to complete an OLE action" error messages.

Then when I stepped through the code, I was getting an error message that read [filename I'm trying to open] "is locked for editing by" [me]. "Do you want to Open a Read Only copy, Create a local copy and merge your changes later, or Receive notification when the original copy is available"

I suspected two issues
1. The macro is not successfully opening the source file before it tries to copy it, so there is nothing to paste into Excel
2. The word file was not closed the LAST time I ran the app, so the file is getting stuck open, and I have no way to manually close it.

Finally, I rebooted the PC, and added code (activedocument.close) to close the word app after copying the contents of the rtf file

VB:

Sub CopyRTFDocToTemp()
Dim WordApp As Object
Set WordApp = CreateObject("Word.Application")
With WordApp

[Code] ....

I'm considering adding a wait loop to ensure that the app has actually opened a copy of the rtf file, so it has something to copy, but I'm unsure what variable to check with that loop.

View 2 Replies View Related

Select Method Of Worksheet Class Failed

Nov 17, 2008

I have all sheets selected:

Dim ws As Worksheet
For Each ws In Sheets
If ws.Visible Then ws.Select (False)
Next

' The thing is that i now want to ungroup or select the first worksheet

Sheets(1).Select ?

And then run the same sub on all the worksheets by this:

Dim wSheet As Worksheet

For Each wSheet In Worksheets

Next wSheet

Debug error is:

Select method of worksheet class failed: Sheets(1).Select ?

View 9 Replies View Related

Copy Method Of Worksheet Class Failed

Dec 15, 2008

I am getting this error when I run the following VBA script.

Sub AutoShape3_Click()
Sheets("Template").Select
ActiveSheet.Copy After:=Sheets(14)
Duplicate.Hide
wksName.Show
End Sub
The break mode is highlighting
ActiveSheet.Copy After:=Sheets(14)
as the source of the error.

I've run this code many times before with no problem. The workbook has 48 worksheets in it. It's my understanding that Excel can handle many more worksheets, so that shouldn't be a problem.

View 9 Replies View Related

Inputbox Method: Cannot Select Cells In Worksheet

Aug 26, 2009

I'm using an inputbox to let the user select a range of cells. Here's the line
Set MyRange = Application.InputBox("Select cells with numeric data", Type:=8)

When the inputbox comes up, it won't let the user click any cells (or anywhere in the worksheet, for that matter). The cell/range reference has to be typed in manually.

View 9 Replies View Related

Method Range Of Object Worksheet Failed

Sep 10, 2006

Sub a()
z = 4
y = 7
sWork. Range(Cells(1 + nRow(z), 4), Cells(1 + nRow(z), 12)).Replace y, 0 lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
End Sub

but when I include it in my main code

sWork.[A1:L9] = 123456789
For z = 1 To 81
y = [A1].Offset(nRow(z), nCol(z))
If y > 0 Then
sWork.Cells(1 + nRow(z), 1).Replace y, 0, lookat:=xlPart,searchorder:=xlByRows, MatchCase:=False, matchbyte:=False
sWork.Cells(1 + nCol(z), 2).Replace y, 0, lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False, matchbyte:=False
sWork.Cells(1 + nBox(z), 3).Replace y, 0, lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False, matchbyte:=False
sWork.[A1].Offset(nRow(z), nCol(z) + 3).Value = 0
sWork.Range(Cells(1 + nRow(z), 4), Cells(1 + nRow(z), 12)).Replace y, 0, lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
sWork.Range(Cells(1, 4 + nCol(z)), Cells(9, 4 + nCol(z))).Replace y, 0, lookat:=xlPart, searchorder:=xlByColumns, MatchCase:=False
sWork.Range(Cells(1 + Int(nRow(z) / 3) * 3, 4 + Int(nCol(z) / 3) * 3), Cells(3 + Int(nRow(z) / 3) * 3, 6 + Int(nCol(z) / 3) * 3)).Replace y, 0, lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
End If
Next

I get a "Method 'Range' of object '_Worksheet' failed" error at line 9,
when z = 4, y = 7, and nRow(z) is a UDF which = 0.

View 8 Replies View Related

Copy Method Of Worksheet Class Failed ..

Nov 18, 2006

I've developed an administrative package in Excel for an After School Care programme. Essentially, there are 57 Child Records. Each record is stored on its own sheet. There are four other sheets in the workbook: three hidden templates, and the front page which holds a summary of all child attendance and balances.

Up until a short time ago, invoices were created from an "Invoices.xlt" file - the program would open the file as a new book, and copy the 1st Template page out until all invoices were created. This worked fine. I've recently changed this so the Invoice template is stored within the main workbook. After I did this, we started having problems.

We can run off up to 25-35 invoices fine (32 at this stage), but eventually it hits a point where the Activesheet.Copy command fails. After this point it is impossible to copy further sheets. I CAN, however, use Insert -> Worksheet. It is only the copy function that fails.

Because it's such a strange problem, I've uploaded an example with children's names changed. It can be found on [url]

The steps to recreate the problem are simple: Open the file, select all of the children's names (from Child to Child z), and click the "Create Invoices" button at the top.

View 4 Replies View Related

Method 'range' Of Object 'worksheet' Fails

Apr 26, 2007

I'm trying to accomplish the following: on "LogSheet" sheet, click on the DailyCloseButton and have various lists on the "lists" sheet automatically sorted before I save and end. The code below gives an error: "Method ' Range' of object '_Worksheet' failed". I have no idea why it failed.

Private Sub DailyCloseButton_Click()
' DailyClose and sort routine

'Sort lists
Sheets("Lists").Activate

Range("Products").Select
Range("Products").Sort Key1:=Range("Products").Cells(2, 1), _
Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal.............

View 9 Replies View Related

Method Range Of Object Worksheet Failed ..

Jul 6, 2007

I am having a problem with this bit of code. When I try to run it it shows Method Range of object Worksheet Failed.

The original code I adapted this from works just fine, and the only thing I have changed is the ranges and the sheet.

The code is:

Sub Filter()

With Sheet18
'
.Range("DisbData").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=.Range("Criteria"), Unique:=False

.Range("DisbData").SpecialCells(xlCellTypeVisible).Copy Destination:="DisbPaste"
.ShowAllData
End With
End Sub

The line that the debugger is showing has issues is:

.Range("DisbData").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=.Range("Criteria"), Unique:=False

View 4 Replies View Related

Paste Method When Compiling Data Into Master Worksheet

May 6, 2014

Here is my code thus far - it seems to go through the directory and copy data (single columns of 101 data-points) from the workbooks, but does not successfully paste it into the master-workbook (error message reads: 'paste method of worksheet class failed').

VB:
Sub LoopThroughDirectory()
Dim MyFile As String
Dim ecolumn
MyFile = Dir("C:Documents and SettingskbriemMy DocumentsErindi_i_vinnsluEXCELprufa1")

[Code] ....

View 3 Replies View Related

Range Method Fails When Moving List To Other Worksheet

Oct 1, 2008

I had this code working on a Range in a Worksheet. I decided I needed to move the range to another worksheet. I moved the data to column AA in a new sheet called Audit_Pool and renamed the sheet name in ThisWorkbook.Sheets, but I get a runtime error at the With Range statement.

With ThisWorkbook.Sheets("Audit_Pool").Range("AA:AA")
With Range(.Cells(1, 1), .Cells(.Rows.Count).End(xlUp))
Randomize
With .Cells(Int(.Rows.Count * Rnd()) + 1, 1).Resize(1, 2)
.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)

what I missed when I moved my list to a different sheet?

View 9 Replies View Related

Paste Method Of Worksheet Class Failed - But Not On Step-thru Using F8

Jan 29, 2009

My spreadsheet has a grouped graphic that I need to repeat in a variable number of rows. I wrote a macro that copies the grouped graphic from above and pastes and positions it in the newly inserted row.

It works great when I use F8 to step thru, but if I try to actually run the macro, I get an "Paste Method of worksheet class failed." error.


Sub InsertEmployees()
Range("B18").Select
Selection.EntireRow.Copy
Selection.EntireRow.Insert Shift:=xlDown
ActiveSheet.Shapes("Group 129").Copy
Application.CutCopyMode = False
ActiveSheet.Paste
End Sub

View 9 Replies View Related







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