Note in another workbook cell

[Olbap]

Hello,

I am trying to run this macro and I get always an "Error 1004" with the select instruction. The aim is to put a note on the same cell in the same sheet in a second (NewFile) workbook and highlight it. Could someone help me?

Thanks in advance,

Here is the code:

Sub FetchNoteinNewFileActivecell()
'
' This macro highlights in red the cell in the NewFile corresponding to the selected cell and adds an empty comment
' Warning: this works only if there is a sheet in the target (NewFile defined in range in Sheet1) file with the same name as the active sheet
'
Dim NwFile As String
Dim CurrenCell As String
Dim CurrenSheet As String

CurrentCell = ActiveCell.Address
CurrentSheet = ActiveSheet.Name
NwFile = Workbooks("MyFile.xls").Worksheets("Sheet1").Range("NewFile")

Workbooks(NwFile).Worksheets(CurrentSheet).Range(CurrentCell).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.AddComment
Selection.Comment.Visible = False
Selection.Comment.Text Text:=""
'Selection.Select
End Sub

 

[Airfix9]

Have you activated the NwFile workbook? I think you need to do that first, based on your code.

 

[Olbap]

Thanks for answering, yes even if I activate the workbook I keep getting the same message...

 

[Airfix9]

OK, I have run your code (as edited below):~

Sub FetchNoteinNewFileActivecell()
'
' This macro highlights in red the cell in the NewFile corresponding to the selected cell and adds an empty comment
' Warning: this works only if there is a sheet in the target (NewFile defined in range in Sheet1) file with the same name as the active sheet
'
Dim NwFile As String
'Dim CurrenCell As String   - edited out, incorrectly spelled variable name
Dim CurrentCell As String
'Dim CurrenSheet As String  - edited out, incorrectly spelled variable name
Dim CurrentSheet As String

CurrentCell = ActiveCell.Address
CurrentSheet = ActiveSheet.Name
NwFile = Workbooks("MyFile.xls").Worksheets("Sheet1").Range("NewFile").Value
Workbooks(NwFile).Sheets(CurrentSheet).Activate '- new line added
Workbooks(NwFile).Worksheets(CurrentSheet).Range(CurrentCell).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
'.TintAndShade = 0          - this didn't work, so I have commented it out
'.PatternTintAndShade = 0   - this didn't work, so I have commented it out
End With
Selection.AddComment
Selection.Comment.Visible = False
Selection.Comment.Text Text:=""
'Selection.Select
End Sub

Please note my comments.

It requires that the file with the name held in the range named "NewFile" is already open and has a named worksheet as defined in CurrentSheet. If so, it worked fine.

 

[Andrew Poulsom]

You don't need to Select:

With Workbooks(NwFile).Worksheets(CurrentSheet).Range(CurrentCell)
    With .Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    .AddComment
    .Comment.Visible = False
    .Comment.Text Text:=""
End With

 

[Olbap]

Thanks a lot Airfix9 and Andrew! it works fine now.

Cheers

 

[Olbap]

Just another question, how could I fill the comment in the new file with text from the original cell? I tried recording that action with no result.
Thanks

 

[Airfix9]

Create a string from the data that you want and change this:~

.Comment.Text Text:=""

Place your string after the = sign.

 

[Andrew Poulsom]

If you haven't done any activating/selecting:

.Comment.Text Text:=ActiveCell.Value

 

[Olbap]

Great, that works neat. Thanks again