What I need; I want a comment on my spreadsheet to be clickable, when it's clickt it needs to follow the path (written in cell B55) to an image.
I know the trick of making a normal hyperlink (to for example www.mrexcel.com)
But I can't get it working for an image; tried file://c:/test/test.jpg
Is there an ******* event for comments? Or is it possible to create a hyperlink in the comments to the image?
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.
I have this code that looks through my worksheet once the conditions are met it will email, and in column "M" I put a hyperlink to where the document is stored. All works as far as the email format, even grabs the hyperlink but it’s not clickable in the email.
Here is the code.
I am outlook 07 and vista 07.
Option Explicit
Const Startingrow = 11 'Data starts on row ##
Const AlarmDelay = 183 'send warning
Sub CheckTimeLeftFac()
'References needed :
'Microsoft Outlook Object Library
Dim i As Long
Dim j As Long
Dim msg As Long
Dim Lastrow As Long
Dim WhoTo As String
Dim SubjectLine As String
Dim MessageBody As String
Dim olMail As Outlook.MailItem
Dim olApp As Outlook.Application
Dim strLink As String
I have hyperlinks betwene one worksheet to another - they only only hyperlinked thourh column & rows (eg: A100) etc. How can I lock these hyperlinks but still allow users to insert new rows without losing their place?
View 2 Replies View RelatedI am having a slight problem copying hyperlinks. I have written some code that sorts data by date and then creates a simple diary. It originally placed the name of the event in the new diary sheet. What I would like to do is instead of copying the name into this new sheet copy a hyperlink that I have created to the company's website for that event.
I can create the hyperlink using hyperlink(B1,A1) where A1 is the company name and B1 is the web address but since the new sheet will not have the underlying data I need to actually copy the values and format of the hyperlink rather than the formula.
I am trying to to get it to work so that if there is a comment already in the cell then it would skip to the next macro. Here is what I am trying:
'Mgr Voids
If Range("F9").Comment.Text = True Then End
ElseIf Range("F9").Value > 50 Then
MyInput = Application.InputBox("You Must Give A Reason For The Amount Of Mgr Voids For " & Range("F6"))
ElseIf MyInput = "" Then End
ElseIf MyInput = False Then End
ActiveSheet. Unprotect ("13792468")
ActiveSheet.Range("F9").AddComment
Range("F9").Comment.Visible = False
Range("F9").Comment.Text Text:="" & Chr(10) & (MyInput) & Chr(10) & ""
ActiveSheet.Protect ("13792468")
End If
The first part (If Range("F9").Comment.Text = True Then End) obviously doesn't work because I have no clue how it supposed to be... I was just hoping to get lucky. Any help would be appreciated.
Thanks,
Shawn
I am building a tool that uses Pivot Tables (so I can't use track changes) and I have found cool code that will insert timestamp and username in the comment when a change occurs. But I need to modify the code to also take the old value and put it into the comment as well. Here is the code to register the change into the comment:
View 11 Replies View RelatedIs it possible to trigger a forced comment if a particular value is entered in a cell?
For instance, if the letter "F" is entered into a cell, I want the comment box is pop up, is this possible?
And lastly... is it possible to automatically group comments? for instance if a row has 6 comments, I'd like to combine all of them into a comment box on the same row.
comment VBA code.. I use this VBA code for my comments:
View 2 Replies View Relatedmy macro processes a lot of data and puts a comment in certain cells (several thousand of them). I would like to autosize the comments so that the text can all be read by default.
I found the following on the web:
I'm using Excel 2002 and looking for a way to get rid of the red comment indicator in entirety. While I get that it's not possible there appears to be a workaround which I took off the Contextures website.
Sub RemoveIndicatorShapes()
Dim ws As Worksheet
Dim shp As Shape
Set ws = ActiveSheet
For Each shp In ws.Shapes
If Not shp.TopLeftCell.Comment Is Nothing Then
If shp.AutoShapeType = _
msoShapeRightTriangle Then
shp.Delete
End If
End If
Next shp
End Sub
Is there a way to have a comment be a formula. I'm looking to have a comment calculate a running average.
Excel 2003
window's XP
I have some code that finds the appropriate string for a comment from another sheet. I then want to add the string to the appropriate range selection but am encountering an Object Variable or With Block Variable Not set on this section of
Range("C" & i).Select
ActiveCell.Comment.Delete <== Here Or the Next line
ActiveCell.AddComment (Commenta)
ActiveCell.Comment.Shape.TextFrame.AutoSize = True
Where Commenta is the string value I want to add.
i need to have a way to link a small picture/thumbnail to a cell. so that there is a way to preveiw it. Ideally like what happens with the comments where you hover over the cell and the comment would pop up. or failing that may be a cell linking to the image.
View 8 Replies View RelatedI have Sheet1 and Sheet2, each sheet has 2 columns.
Sheet1:
John 31
Joey 22
Josh 28
Jane 24
Sheet2:
Joey Smith 22
Josh Morgan 27
Jane White 24
John Doe 32
Lookup Sheet1's each Column A cell on Sheet2.
For example, Sheet1 A1 is John, Sheet2 A4 is John Doe, since John Doe contains John, it's a match. Then compare Sheet1 B1 and Sheet2 B4, 31 and 32 don't match. This is what i need to find, then add a comment "Inconsistent" in a new column.
For example, Joey (Sheet1 A2) and Joey Smith (Sheet 1 A1) have same value 22 (Sheet1 B2 and Sheet2 B1). Then no need to add any comment.
This is a repost, I would like to add a comment boxe in multiple sheets (same cell) in a work book. Someone reccomended a macro but then my post was removed.
View 12 Replies View Relatedmake the contents of the cell comment box dependent on the cell contents? eg if the cell contents = 2 and a seperate table says 2 is "poor" can it automatically populate the comment with "poor" ?
View 5 Replies View RelatedI'm wondering if it's possible to use a lookup formula to populate cell comments? In other words, I'd like to put a lookup table into a cell, and if the value is not FALSE, have that Lookup table then pull in certain information into the cell's comments.
View 2 Replies View RelatedWhen I insert a comment in a cell, my corporate version of Excel displays my employee number. How can Code a cell to populate this employee number.
View 3 Replies View RelatedI'm using a function at the moment to copy the comment text from cells, but would like to Change it a Little. At the Moment I have:
[Code]...
There are 3 Basic Groups that the comments refer to, eg: If a comment is a 1,4,11,14 it's Group A. 2,5,12,15 are Group B, etc...
I need a macro to get the info written in the Comment field (In the file properties field). Attached is a pic to explain a little better.
View 2 Replies View RelatedI have created vba which I want to comment out. I totally stumped how to create this action on vba. My current vba code which I want to comment out using vba
Code:
Option Explicit
Sub YearlyInput()
End Sub
'Speeding Up VBA Code
With Application
[Code] .......
I have this code tat I am using to important a picture via a comment box:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$AI$10" Then
newpic = "J:help" & Range("AQ21").Value
Target.Comment.Shape.Fill.UserPicture newpic
End If
End Sub
The problem is if you put a value in AI10 that is not AQ21 it gives and error. I would like for it to call another cell (B3) for newpic in the case of a value that is not present in AI10.
How would I trap the escape key being pushed while in a comment box?
In case user accidentally modifies comments box and they do not wish to save the changes. Pushing escape will save changes, which I do not wish to do.
This thread is closest code I found, but I do not know how to adapt it for the comment box.
Errror trapping with Application.Dialogs(xlDialogOpen).Show
how can i run a macro after a comment is inserted?
View 6 Replies View RelatedIs there a way to insert a pdf into a cell comment? I found how to insert an object but when I do that it covers the entire spreadsheet. I would like to be able to add pdf's to cell comments.
View 3 Replies View RelatedI would like a macro to search the worksheets for any empty comment boxes and delete them if they contain no text (blank box).
View 5 Replies View RelatedNeed a macro for the following:
C | D | F (Column)
10 | 100 | Yes (data)
If column F = Yes then
column G = "abc = 10, def fgh = 100"
where 'abc =' is additional text, '10' is C value.
Loop till the last row of F.
Basically, I want to autofill column G with the data derived from column C & D + some text.
I am trying to write a VBA macro that will go from one sheet and copy a cell's text, then go back to the main sheet and insert this text into a "comment-box" type of thing.
The box I'm talking about is where you move the mouse over a cell and a box pops up with the comment in it.
I was toying w/ the idea of being able to view two items in the same cell. A wholesale cost and a retail cost, the wholesale would be in the cell and the retail price would be in the comment. When mouse is placed over a particular cell, a comment would pop up and show the second view, the retail price.
This to be effective would need to be copied down like a typical formula would be. If not, it would take too long to enter each item into a comment for each cell.
I really doubt this is possible, but if not in Excel, could this be accomplished in VBA?