Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA to add Tool Tips
In Excel 2002 I have a column in which I would like to add
Tool Tips -- each cell's Tip being the value of the formula in that cell. (Or I could go collect the value from another sheet in the workbook if need be.) I'm trying some VBA to do this, but so far without success. Can someone help? -- dman |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA to add Tool Tips
On Aug 4, 5:55 am, Dallman Ross <dman@localhost. wrote:
In Excel 2002 I have a column in which I would like to add Tool Tips -- each cell's Tip being the value of the formula in that cell. (Or I could go collect the value from another sheet in the workbook if need be.) I'm trying some VBA to do this, but so far without success. Can someone help? -- dman try this, run the code in sheet1 Have something entered in sheet2 E1 Sub Macro3() ' Macro3 Macro ' Macro recorded 8/4/2007 by Dave Morrison Range("A1").ClearComments With Range("A1") .AddComment Text:=Sheets("Sheet2").Range("E1").Text ActiveCell.Comment.Visible = False End With End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA to add Tool Tips
Dave,
Your simple macro which you recorded gave me a needed nudge. I had also recorded some macros whilst scratching my head. But I thought about this some more and tried and tried things until I got it. Thanks. Comments are sort of okay, but I really wanted tool tips. I succeeded. I will post my code. This works fine, though comments or suggested refinements always gladly accepted. I couldn't get one part to work until I tried adding ".Activate" out of exasperation, and suddenly it worked. Wow. I have one more question, which I'll post below the code. ---------------------- Sub tipMe() ' 8/4/2007 by Dallman Ross, with a nudge by Dave Morrison Dim indie As Variant Dim myRow As Long Dim datRows As Long ThisWorkbook.Worksheets("Stagger").Activate datRows = Application.CountA(Columns("D")) For myRow = 2 To datRows + 1 ' values in Column-A cells are also datasheet names indie = Cells(myRow, "A").Value With Cells(myRow, "D") ' below works okay, but I commented it out vice stuff _ further down below ' .ClearComments ' .AddComment Text:=Sheets(indie).Range("E2").Text ' .Comment.Visible = False ' this is what I was after! -dman .Activate .Hyperlinks.Add Anchor:=Selection, Address:="", _ SubAddress:=indie & "!E2", _ ScreenTip:=Sheets(indie).Range("E2").Text End With Next 'myRow End Sub ---------------------- Okay, my followup question is, I'd prefer to have the screen tip say the value of this instead of what I have there above: =MSNStockQuote($A2,"Close") (but using the "myRow" instead of the 2 there). I can't figure out how to do it. The reason I prefer that is, the data on the other referenced worksheets may or may not be updated to yesterday's close. Also, on the weekend the MSNStockQuote function still gives me Thursday's close, but my updated data on the sheets I'm referencing give me Friday's. I want the *prior* close. (During the week what I have will be fine, so long as my sheets are updated through the previous day.) Oh, and the reason I want a tool tip instead of just looking in the cell is, the cell is formatted to 0.5 points' width and is merely fill-colored to show a trend. I've simply stuck the data there as a safe "storage" place, but now want the tool tip to show the text from the formula that's there. -- dman --------------------------- In .com, D. spake thusly: On Aug 4, 5:55 am, Dallman Ross <dman@localhost. wrote: In Excel 2002 I have a column in which I would like to add Tool Tips -- each cell's Tip being the value of the formula in that cell. (Or I could go collect the value from another sheet in the workbook if need be.) I'm trying some VBA to do this, but so far without success. Can someone help? try this, run the code in sheet1 Have something entered in sheet2 E1 Sub Macro3() ' Macro3 Macro ' Macro recorded 8/4/2007 by Dave Morrison Range("A1").ClearComments With Range("A1") .AddComment Text:=Sheets("Sheet2").Range("E1").Text ActiveCell.Comment.Visible = False End With End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA to add Tool Tips
I've managed to solve my problem here. It took me all night,
but I did it. Learned a few things. One goofy thing I learned is, the tool tip won't display right if the cell that was used to copy its contents from is not wide enough! That took me a while to understand what was happening there. I've gone with a temporary "scratch" range to copy the values into that I want to turn into tool tips. It works well. I left some of my earlier stuff in the code to do with the other sheets and hyperlinks to them, because it is still useful to me. Another thing I learned (not the first time, unfortunately) is not to type "EntireRow.Delete" when I meant to type "EntireColumn.Delete"; and especially not before saving my current work first before running the macro. :-) A third thing I learned -- is this a bug in Excel 2002? -- is that running the code .Hyperlinks.Delete causes underlying cell formatting to be lost. (!!) Thanks to Dave Morrison for a small kick in the pants to get me going on this. Now I can move on to the next thing I want to do. (Spinner coding -- I have no idea yet, and my question over in the programming group is so far unanswered.) Here's the tool-tip code. It might be useful in parts to others. ------------------------ Sub tipMe() ' ' 8/5/2007 by Dallman Ross, with a nudge by Dave Morrison ' ' Keyboard Shortcut: Ctrl+Shift+T Dim myRow, scratchCol, datRows As Long Dim rgCopy, rgScratch As Range Dim indie As Variant With Application .ScreenUpdating = False .EnableEvents = False End With ThisWorkbook.Worksheets("Stagger").Activate datRows = Application.CountA(Columns("D")) scratchCol = 19 'Column "S" -- must be empty!! Set rgCopy = Range("D2", Cells(datRows + 1, "D")) Set rgScratch = Range(Cells(2, scratchCol), _ Cells(datRows + 1, scratchCol)) rgCopy.Copy With rgScratch .PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False .NumberFormat = "#,##0.00_);(#,##0.00)" End With For myRow = 2 To datRows + 1 ' value in cell is also a sheet name indie = Cells(myRow, "A").Value With Cells(myRow, "D") .Activate .Hyperlinks.Add Anchor:=Selection, Address:="", _ SubAddress:=indie & "!E2", _ ScreenTip:=Cells(myRow, scratchCol).Text 'ScreenTip:=Sheets(indie).Range("E2").Text 'alternate End With Next 'myRow rgScratch.EntireColumn.Delete With Application .ScreenUpdating = True .EnableEvents = True End With End Sub -dman ================================================== I had written earlier: Okay, my followup question is, I'd prefer to have the screen tip say the value of [the following] instead of what I have there above: =MSNStockQuote($A2,"Close") (but using the "myRow" instead of the 2 there). I can't figure out how to do it. The reason I prefer that is, the data on the other referenced worksheets may or may not be updated to yesterday's close. Also, on the weekend the MSNStockQuote function still gives me Thursday's close, but my updated data on the sheets I'm referencing give me Friday's. I want the *prior* close. (During the week what I have will be fine, so long as my sheets are updated through the previous day.) Oh, and the reason I want a tool tip instead of just looking in the cell is, the cell is formatted to 0.5 points' width and is merely fill-colored to show a trend. I've simply stuck the data there as a safe "storage" place, but now want the tool tip to show the text from the formula that's there. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA to add Tool Tips
In , Dallman Ross <dman@localhost.
spake thusly: I've managed to solve my problem here. It took me all night, but I did it. Learned a few things. One goofy thing I learned is, the tool tip won't display right if the cell that was used to copy its contents from is not wide enough! That took me a while to understand what was happening there. I've gone with a temporary "scratch" range to copy the values One more follow-up to myself, because I've reworked it again and simplified. The code might be helpful to others. The "goofy" bug I elucidated above is the key. It was what drove me to thinking my original approach was no good. But it turns out I don't need a helper column at all, or any copying. I just widen the column, add the screen tip, and restore the column's width to the original (0.5 points). Here we go: Sub tipMe() ' ' 8/5/2007 by Dallman Ross ' Add screen tips with hyperlinks to a range ' ' Keyboard Shortcut: Ctrl+Shift+T Dim myRow, datRows As Long Dim myColWidth As Variant Dim rgIndirect As Variant With Application .ScreenUpdating = False .EnableEvents = False End With ThisWorkbook.Worksheets("Stagger").Activate datRows = Application.CountA(Columns("D")) With Columns("D") myColWidth = .ColumnWidth 'save current width Columns("D").AutoFit 'or screentip won't work End With For myRow = 2 To datRows + 1 ' value in cell is also a sheet name rgIndirect = Cells(myRow, "A").Value & "!E2" With Cells(myRow, "D") .Activate .Hyperlinks.Add Anchor:=Selection, Address:="", _ SubAddress:=rgIndirect, _ ScreenTip:=.Text 'from this cell End With Next 'myRow Columns("D").ColumnWidth = myColWidth 'restore original Range("A1").Select With Application .ScreenUpdating = True .EnableEvents = True End With End Sub A third thing I learned -- is this a bug in Excel 2002? -- is that running the code .Hyperlinks.Delete causes underlying cell formatting to be lost. (!!) -- dman |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA to add Tool Tips
On Aug 5, 7:16 am, Dallman Ross <dman@localhost. wrote:
In , Dallman Ross <dman@localhost. spake thusly: I've managed to solve my problem here. It took me all night, but I did it. Learned a few things. One goofy thing I learned is, the tool tip won't display right if the cell that was used to copy its contents from is not wide enough! That took me a while to understand what was happening there. I've gone with a temporary "scratch" range to copy the values One more follow-up to myself, because I've reworked it again and simplified. The code might be helpful to others. The "goofy" bug I elucidated above is the key. It was what drove me to thinking my original approach was no good. But it turns out I don't need a helper column at all, or any copying. I just widen the column, add the screen tip, and restore the column's width to the original (0.5 points). Here we go: Sub tipMe() ' ' 8/5/2007 by Dallman Ross ' Add screen tips with hyperlinks to a range ' ' Keyboard Shortcut: Ctrl+Shift+T Dim myRow, datRows As Long Dim myColWidth As Variant Dim rgIndirect As Variant With Application .ScreenUpdating = False .EnableEvents = False End With ThisWorkbook.Worksheets("Stagger").Activate datRows = Application.CountA(Columns("D")) With Columns("D") myColWidth = .ColumnWidth 'save current width Columns("D").AutoFit 'or screentip won't work End With For myRow = 2 To datRows + 1 ' value in cell is also a sheet name rgIndirect = Cells(myRow, "A").Value & "!E2" With Cells(myRow, "D") .Activate .Hyperlinks.Add Anchor:=Selection, Address:="", _ SubAddress:=rgIndirect, _ ScreenTip:=.Text 'from this cell End With Next 'myRow Columns("D").ColumnWidth = myColWidth 'restore original Range("A1").Select With Application .ScreenUpdating = True .EnableEvents = True End With End Sub A third thing I learned -- is this a bug in Excel 2002? -- is that running the code .Hyperlinks.Delete causes underlying cell formatting to be lost. (!!) -- dman congrads, one thing I forgot was that you can use tool tips from data validation |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA to add Tool Tips
In . com, D.
spake thusly: On Aug 5, 7:16 am, Dallman Ross <dman@localhost. wrote: The code might be helpful to others. The "goofy" bug I elucidated above is the key. congrads, one thing I forgot was that you can use tool tips from data validation Cool. I never knew that! Good to know. -- dman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
tool tips | Excel Discussion (Misc queries) | |||
Tool tips for UDF | Excel Worksheet Functions | |||
Excel Tips | Excel Worksheet Functions | |||
need some tips! | Excel Worksheet Functions | |||
I am missing view tool bar from tool menu. | New Users to Excel |