Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hover Messages
MS Excel 2003, Workbook name 2008 Results.
In worksheet named Inventory, Column M has alpha-numeric codes - the worksheet contains 8000+ rows. In worksheet named Function Codes, 365 a-n codes are held in column B, with their corresponding text descriptions in column C. What is needed: when the user hovers the cursor over a cell in Inventory column M, Excel finds the a-n code in worksheet Function Codes column B, and displays the corresponding text from column C the same way as in a hyperlink Screen Tip. Finally, the user must be able to click on a column M cell, make an a-n code change, and the new text description appear when hovering over the changed cell (after the correction has been entered). What would the macro code be and where would it be placed? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hover Messages
You could upload a workbook to our forum where we would be pleased to look at it for you! :) -- The Code Cage Team Regards, The Code Cage Team www.thecodecage.com ------------------------------------------------------------------------ The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=7641 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hover Messages
Tricky, not impossible but effectively not viable.
There are no directly trappable mouse over or hover events in cells. Using a timer could periodically check mouse coordinates every (say) 0.2 sec' and if same conclude the mouse is stationary (could sub class windows mouse events but not stable in VBA). Having determined the mouse is not moving next task is to relate mouse coordinates to cell, it is doable with one of three distinct approaches (not sure about xl2007) but none are straightforward. Then the final task is to display something like a tooltip, rather than a form or some other object. I'm almost sure there is no way to do that purely with VBA. Of course, could do almost anything in a selection change event (except the tool tip, temporary dynamic comment perhaps). Regards, Peter T "Phil H." wrote in message ... MS Excel 2003, Workbook name "2008 Results." In worksheet named "Inventory," Column M has alpha-numeric codes - the worksheet contains 8000+ rows. In worksheet named "Function Codes," 365 a-n codes are held in column B, with their corresponding text descriptions in column C. What is needed: when the user hovers the cursor over a cell in "Inventory" column M, Excel finds the a-n code in worksheet "Function Codes" column B, and displays the corresponding text from column C the same way as in a hyperlink "Screen Tip." Finally, the user must be able to click on a column M cell, make an a-n code change, and the new text description appear when hovering over the changed cell (after the correction has been entered). What would the macro code be and where would it be placed? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hover Messages
Thanks, Pete - not encouraging at this point. "Normal" users will not be
able to remember 365 codes. Can you think of another way to approach to the problem? Could a lookup be used somehow? Or maybe a dropdown in the header row? "Peter T" wrote: Tricky, not impossible but effectively not viable. There are no directly trappable mouse over or hover events in cells. Using a timer could periodically check mouse coordinates every (say) 0.2 sec' and if same conclude the mouse is stationary (could sub class windows mouse events but not stable in VBA). Having determined the mouse is not moving next task is to relate mouse coordinates to cell, it is doable with one of three distinct approaches (not sure about xl2007) but none are straightforward. Then the final task is to display something like a tooltip, rather than a form or some other object. I'm almost sure there is no way to do that purely with VBA. Of course, could do almost anything in a selection change event (except the tool tip, temporary dynamic comment perhaps). Regards, Peter T "Phil H." wrote in message ... MS Excel 2003, Workbook name "2008 Results." In worksheet named "Inventory," Column M has alpha-numeric codes - the worksheet contains 8000+ rows. In worksheet named "Function Codes," 365 a-n codes are held in column B, with their corresponding text descriptions in column C. What is needed: when the user hovers the cursor over a cell in "Inventory" column M, Excel finds the a-n code in worksheet "Function Codes" column B, and displays the corresponding text from column C the same way as in a hyperlink "Screen Tip." Finally, the user must be able to click on a column M cell, make an a-n code change, and the new text description appear when hovering over the changed cell (after the correction has been entered). What would the macro code be and where would it be placed? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hover Messages
Have you considered using Data Validation / List in all your input cells,
where the list refers to your codes (could be a ref or a named range). In the next column you could have a lookup formula that returns the description based on the code in the input cell (or blank if code cell is empty). Regards, Peter T "Phil H." wrote in message ... Thanks, Pete - not encouraging at this point. "Normal" users will not be able to remember 365 codes. Can you think of another way to approach to the problem? Could a lookup be used somehow? Or maybe a dropdown in the header row? "Peter T" wrote: Tricky, not impossible but effectively not viable. There are no directly trappable mouse over or hover events in cells. Using a timer could periodically check mouse coordinates every (say) 0.2 sec' and if same conclude the mouse is stationary (could sub class windows mouse events but not stable in VBA). Having determined the mouse is not moving next task is to relate mouse coordinates to cell, it is doable with one of three distinct approaches (not sure about xl2007) but none are straightforward. Then the final task is to display something like a tooltip, rather than a form or some other object. I'm almost sure there is no way to do that purely with VBA. Of course, could do almost anything in a selection change event (except the tool tip, temporary dynamic comment perhaps). Regards, Peter T "Phil H." wrote in message ... MS Excel 2003, Workbook name "2008 Results." In worksheet named "Inventory," Column M has alpha-numeric codes - the worksheet contains 8000+ rows. In worksheet named "Function Codes," 365 a-n codes are held in column B, with their corresponding text descriptions in column C. What is needed: when the user hovers the cursor over a cell in "Inventory" column M, Excel finds the a-n code in worksheet "Function Codes" column B, and displays the corresponding text from column C the same way as in a hyperlink "Screen Tip." Finally, the user must be able to click on a column M cell, make an a-n code change, and the new text description appear when hovering over the changed cell (after the correction has been entered). What would the macro code be and where would it be placed? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hover Messages
Peter, I think "List" may be the idea. I will play with the validation
idea. In the meantime, I was wondering if we can go behind the scenes and add a second column to the filter dropdown list. This would be perfect, if doable, because of the many many columns with codes. The further I get into this, the more I think I will press for converting this to Access - after the current situation passes. "Peter T" wrote: Have you considered using Data Validation / List in all your input cells, where the list refers to your codes (could be a ref or a named range). In the next column you could have a lookup formula that returns the description based on the code in the input cell (or blank if code cell is empty). Regards, Peter T "Phil H." wrote in message ... Thanks, Pete - not encouraging at this point. "Normal" users will not be able to remember 365 codes. Can you think of another way to approach to the problem? Could a lookup be used somehow? Or maybe a dropdown in the header row? "Peter T" wrote: Tricky, not impossible but effectively not viable. There are no directly trappable mouse over or hover events in cells. Using a timer could periodically check mouse coordinates every (say) 0.2 sec' and if same conclude the mouse is stationary (could sub class windows mouse events but not stable in VBA). Having determined the mouse is not moving next task is to relate mouse coordinates to cell, it is doable with one of three distinct approaches (not sure about xl2007) but none are straightforward. Then the final task is to display something like a tooltip, rather than a form or some other object. I'm almost sure there is no way to do that purely with VBA. Of course, could do almost anything in a selection change event (except the tool tip, temporary dynamic comment perhaps). Regards, Peter T "Phil H." wrote in message ... MS Excel 2003, Workbook name "2008 Results." In worksheet named "Inventory," Column M has alpha-numeric codes - the worksheet contains 8000+ rows. In worksheet named "Function Codes," 365 a-n codes are held in column B, with their corresponding text descriptions in column C. What is needed: when the user hovers the cursor over a cell in "Inventory" column M, Excel finds the a-n code in worksheet "Function Codes" column B, and displays the corresponding text from column C the same way as in a hyperlink "Screen Tip." Finally, the user must be able to click on a column M cell, make an a-n code change, and the new text description appear when hovering over the changed cell (after the correction has been entered). What would the macro code be and where would it be placed? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hover Messages
Hi Phil
Following on from Peter's idea, I have played about with adding comments to the cell. If you have a List (in my case on Sheet2 column A) for your DV items, with a list of descriptions for those items along side in column B (Sheet2), then the following event code on your sheet with the DV (Sheet1), will insert the comment to the cell depending upon what has been selected. Hovering over the cell will cause the Comment to be displayed. Private Sub Worksheet_Change(By Val Target As Range) Dim messg As String If Not Intersect(Target, Range("A:A")) Is Nothing Then messg = WorksheetFunction.Index(Sheets("Sheet2").Range("B: B"), _ WorksheetFunction.Match(Target.Value, Sheets("Sheet2"). _ Range("A:A"), 0)) On Error Resume Next Target.Cells.AddComment Target.Cells.Comment.Visible = False Target.Cells.Comment.Text Text:="" & Chr(10) & messg On Error GoTo 0 End If End Sub Obviously, change the ranges to suit your situation. -- Regards Roger Govier "Phil H." wrote in message ... Peter, I think "List" may be the idea. I will play with the validation idea. In the meantime, I was wondering if we can go behind the scenes and add a second column to the filter dropdown list. This would be perfect, if doable, because of the many many columns with codes. The further I get into this, the more I think I will press for converting this to Access - after the current situation passes. "Peter T" wrote: Have you considered using Data Validation / List in all your input cells, where the list refers to your codes (could be a ref or a named range). In the next column you could have a lookup formula that returns the description based on the code in the input cell (or blank if code cell is empty). Regards, Peter T "Phil H." wrote in message ... Thanks, Pete - not encouraging at this point. "Normal" users will not be able to remember 365 codes. Can you think of another way to approach to the problem? Could a lookup be used somehow? Or maybe a dropdown in the header row? "Peter T" wrote: Tricky, not impossible but effectively not viable. There are no directly trappable mouse over or hover events in cells. Using a timer could periodically check mouse coordinates every (say) 0.2 sec' and if same conclude the mouse is stationary (could sub class windows mouse events but not stable in VBA). Having determined the mouse is not moving next task is to relate mouse coordinates to cell, it is doable with one of three distinct approaches (not sure about xl2007) but none are straightforward. Then the final task is to display something like a tooltip, rather than a form or some other object. I'm almost sure there is no way to do that purely with VBA. Of course, could do almost anything in a selection change event (except the tool tip, temporary dynamic comment perhaps). Regards, Peter T "Phil H." wrote in message ... MS Excel 2003, Workbook name "2008 Results." In worksheet named "Inventory," Column M has alpha-numeric codes - the worksheet contains 8000+ rows. In worksheet named "Function Codes," 365 a-n codes are held in column B, with their corresponding text descriptions in column C. What is needed: when the user hovers the cursor over a cell in "Inventory" column M, Excel finds the a-n code in worksheet "Function Codes" column B, and displays the corresponding text from column C the same way as in a hyperlink "Screen Tip." Finally, the user must be able to click on a column M cell, make an a-n code change, and the new text description appear when hovering over the changed cell (after the correction has been entered). What would the macro code be and where would it be placed? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hover Messages
Hi Again Roger - we met in London 2006 at the Excel conference, I'm the fly
fisherman gave thought to staying at your place in spring 07. Will try the comment idea seems totally feasible. Is there a way to eliminate the red comment symbol in the upper right corner? Might require some sort of search macro to identify cells with comments and, say, temporarily apply a background color. These symbols could make the worksheet very busy, adding confusion to beginning users. Basically, my purpose is to give users an on-screen definition of a code (there are many columns with similar situations), without having to search elsewhere for the information. Again, I'm dealing with 89 columns and 8000+ rows (admittedly way too much), so my current task is to make the worksheet a user-friendly environment - which currently it is not. The user's task is to go through the 722,000+ cells and clean out errors - where incompatible codes exist (on a row). Im putting together a code matrix to set the rules for the exercise. Will probably be looking for help next week after I receive the latest spreadsheet. So, for now users are filtering and with a very short lead time for scrub completion, on-screen helps seem my only contribution. If you have any other ideas, let me know. Meanwhile, Ill try the comments macro. This is a neat idea for other worksheets will put it in my library. Thanks for the help, Roger. "Roger Govier" wrote: Hi Phil Following on from Peter's idea, I have played about with adding comments to the cell. If you have a List (in my case on Sheet2 column A) for your DV items, with a list of descriptions for those items along side in column B (Sheet2), then the following event code on your sheet with the DV (Sheet1), will insert the comment to the cell depending upon what has been selected. Hovering over the cell will cause the Comment to be displayed. Private Sub Worksheet_Change(By Val Target As Range) Dim messg As String If Not Intersect(Target, Range("A:A")) Is Nothing Then messg = WorksheetFunction.Index(Sheets("Sheet2").Range("B: B"), _ WorksheetFunction.Match(Target.Value, Sheets("Sheet2"). _ Range("A:A"), 0)) On Error Resume Next Target.Cells.AddComment Target.Cells.Comment.Visible = False Target.Cells.Comment.Text Text:="" & Chr(10) & messg On Error GoTo 0 End If End Sub Obviously, change the ranges to suit your situation. -- Regards Roger Govier "Phil H." wrote in message ... Peter, I think "List" may be the idea. I will play with the validation idea. In the meantime, I was wondering if we can go behind the scenes and add a second column to the filter dropdown list. This would be perfect, if doable, because of the many many columns with codes. The further I get into this, the more I think I will press for converting this to Access - after the current situation passes. "Peter T" wrote: Have you considered using Data Validation / List in all your input cells, where the list refers to your codes (could be a ref or a named range). In the next column you could have a lookup formula that returns the description based on the code in the input cell (or blank if code cell is empty). Regards, Peter T "Phil H." wrote in message ... Thanks, Pete - not encouraging at this point. "Normal" users will not be able to remember 365 codes. Can you think of another way to approach to the problem? Could a lookup be used somehow? Or maybe a dropdown in the header row? "Peter T" wrote: Tricky, not impossible but effectively not viable. There are no directly trappable mouse over or hover events in cells. Using a timer could periodically check mouse coordinates every (say) 0.2 sec' and if same conclude the mouse is stationary (could sub class windows mouse events but not stable in VBA). Having determined the mouse is not moving next task is to relate mouse coordinates to cell, it is doable with one of three distinct approaches (not sure about xl2007) but none are straightforward. Then the final task is to display something like a tooltip, rather than a form or some other object. I'm almost sure there is no way to do that purely with VBA. Of course, could do almost anything in a selection change event (except the tool tip, temporary dynamic comment perhaps). Regards, Peter T "Phil H." wrote in message ... MS Excel 2003, Workbook name "2008 Results." In worksheet named "Inventory," Column M has alpha-numeric codes - the worksheet contains 8000+ rows. In worksheet named "Function Codes," 365 a-n codes are held in column B, with their corresponding text descriptions in column C. What is needed: when the user hovers the cursor over a cell in "Inventory" column M, Excel finds the a-n code in worksheet "Function Codes" column B, and displays the corresponding text from column C the same way as in a hyperlink "Screen Tip." Finally, the user must be able to click on a column M cell, make an a-n code change, and the new text description appear when hovering over the changed cell (after the correction has been entered). What would the macro code be and where would it be placed? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hover Messages
Here is the setup, and my code modification:
Sheet 1 - FY08 Inventory Sheet 2 - Code Matrix T B C 1 Code Code Description (row 1 - headers) 2 230 200 Desc 200 3 245 201 Desc 201 * * * * 8119 Z234 Z999 Desc Z999 Sheet 1 column T is DV target column Code entered behind FY08 Inventory worksheet. Should it be elsewhere, say in Module 1? 'Enter comment from external list in target cell. Private Sub Worksheet_Change(By Val Target As Range) Dim messg As String If Not Intersect(Target, Range("T:T")) Is Nothing Then Message = WorksheetFunction.Index(Sheets("Code Matrix").Range("B:B"), _ WorksheetFunction.Match(Target.Value, _ Sheets("Code Matrix"). _ Range("C:C"), 0)) On Error Resume Next Target.Cells.AddComment Target.Cells.Comment.Visible = False Target.Cells.Comment.Text Text:="" & Chr(10) & messg On Error GoTo 0 End If End Sub Compile error: Expected: list separator or ) First line of code, Private, is red "Roger Govier" wrote: Hi Phil Following on from Peter's idea, I have played about with adding comments to the cell. If you have a List (in my case on Sheet2 column A) for your DV items, with a list of descriptions for those items along side in column B (Sheet2), then the following event code on your sheet with the DV (Sheet1), will insert the comment to the cell depending upon what has been selected. Hovering over the cell will cause the Comment to be displayed. Private Sub Worksheet_Change(By Val Target As Range) Dim messg As String If Not Intersect(Target, Range("A:A")) Is Nothing Then messg = WorksheetFunction.Index(Sheets("Sheet2").Range("B: B"), _ WorksheetFunction.Match(Target.Value, Sheets("Sheet2"). _ Range("A:A"), 0)) On Error Resume Next Target.Cells.AddComment Target.Cells.Comment.Visible = False Target.Cells.Comment.Text Text:="" & Chr(10) & messg On Error GoTo 0 End If End Sub Obviously, change the ranges to suit your situation. -- Regards Roger Govier "Phil H." wrote in message ... Peter, I think "List" may be the idea. I will play with the validation idea. In the meantime, I was wondering if we can go behind the scenes and add a second column to the filter dropdown list. This would be perfect, if doable, because of the many many columns with codes. The further I get into this, the more I think I will press for converting this to Access - after the current situation passes. "Peter T" wrote: Have you considered using Data Validation / List in all your input cells, where the list refers to your codes (could be a ref or a named range). In the next column you could have a lookup formula that returns the description based on the code in the input cell (or blank if code cell is empty). Regards, Peter T "Phil H." wrote in message ... Thanks, Pete - not encouraging at this point. "Normal" users will not be able to remember 365 codes. Can you think of another way to approach to the problem? Could a lookup be used somehow? Or maybe a dropdown in the header row? "Peter T" wrote: Tricky, not impossible but effectively not viable. There are no directly trappable mouse over or hover events in cells. Using a timer could periodically check mouse coordinates every (say) 0.2 sec' and if same conclude the mouse is stationary (could sub class windows mouse events but not stable in VBA). Having determined the mouse is not moving next task is to relate mouse coordinates to cell, it is doable with one of three distinct approaches (not sure about xl2007) but none are straightforward. Then the final task is to display something like a tooltip, rather than a form or some other object. I'm almost sure there is no way to do that purely with VBA. Of course, could do almost anything in a selection change event (except the tool tip, temporary dynamic comment perhaps). Regards, Peter T "Phil H." wrote in message ... MS Excel 2003, Workbook name "2008 Results." In worksheet named "Inventory," Column M has alpha-numeric codes - the worksheet contains 8000+ rows. In worksheet named "Function Codes," 365 a-n codes are held in column B, with their corresponding text descriptions in column C. What is needed: when the user hovers the cursor over a cell in "Inventory" column M, Excel finds the a-n code in worksheet "Function Codes" column B, and displays the corresponding text from column C the same way as in a hyperlink "Screen Tip." Finally, the user must be able to click on a column M cell, make an a-n code change, and the new text description appear when hovering over the changed cell (after the correction has been entered). What would the macro code be and where would it be placed? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hover Messages
Hi Phil
I remember you well. With just the H of your surname as your ID, I hadn't picked up that it was you. I trust all is well in Italy. Take a look at the code Debra Dalgleish has about handling comments, on her site. http://www.contextures.com/xlcomments03.html As Debra says, there is no way of switching off the indicator, but you could use her trick of drawing a triangle shape of matching size and placing over the indicator. If you set the colour to NoFill, then that may help. Debra shows code for applying and removing the shapes. If you would like, feel free to send me a sample of what you are trying to do, with some further detail, and I would be more than happy to take a look and see if I can come up with any other ideas. In case you have lost my email address it is roger at technology4u dot co dot uk -- Regards Roger Govier "Phil H." wrote in message ... Hi Again Roger - we met in London 2006 at the Excel conference, I'm the fly fisherman gave thought to staying at your place in spring 07. Will try the comment idea seems totally feasible. Is there a way to eliminate the red comment symbol in the upper right corner? Might require some sort of search macro to identify cells with comments and, say, temporarily apply a background color. These symbols could make the worksheet very busy, adding confusion to beginning users. Basically, my purpose is to give users an on-screen definition of a code (there are many columns with similar situations), without having to search elsewhere for the information. Again, I'm dealing with 89 columns and 8000+ rows (admittedly way too much), so my current task is to make the worksheet a user-friendly environment - which currently it is not. The user's task is to go through the 722,000+ cells and clean out errors - where incompatible codes exist (on a row). Im putting together a code matrix to set the rules for the exercise. Will probably be looking for help next week after I receive the latest spreadsheet. So, for now users are filtering and with a very short lead time for scrub completion, on-screen helps seem my only contribution. If you have any other ideas, let me know. Meanwhile, Ill try the comments macro. This is a neat idea for other worksheets will put it in my library. Thanks for the help, Roger. "Roger Govier" wrote: Hi Phil Following on from Peter's idea, I have played about with adding comments to the cell. If you have a List (in my case on Sheet2 column A) for your DV items, with a list of descriptions for those items along side in column B (Sheet2), then the following event code on your sheet with the DV (Sheet1), will insert the comment to the cell depending upon what has been selected. Hovering over the cell will cause the Comment to be displayed. Private Sub Worksheet_Change(By Val Target As Range) Dim messg As String If Not Intersect(Target, Range("A:A")) Is Nothing Then messg = WorksheetFunction.Index(Sheets("Sheet2").Range("B: B"), _ WorksheetFunction.Match(Target.Value, Sheets("Sheet2"). _ Range("A:A"), 0)) On Error Resume Next Target.Cells.AddComment Target.Cells.Comment.Visible = False Target.Cells.Comment.Text Text:="" & Chr(10) & messg On Error GoTo 0 End If End Sub Obviously, change the ranges to suit your situation. -- Regards Roger Govier "Phil H." wrote in message ... Peter, I think "List" may be the idea. I will play with the validation idea. In the meantime, I was wondering if we can go behind the scenes and add a second column to the filter dropdown list. This would be perfect, if doable, because of the many many columns with codes. The further I get into this, the more I think I will press for converting this to Access - after the current situation passes. "Peter T" wrote: Have you considered using Data Validation / List in all your input cells, where the list refers to your codes (could be a ref or a named range). In the next column you could have a lookup formula that returns the description based on the code in the input cell (or blank if code cell is empty). Regards, Peter T "Phil H." wrote in message ... Thanks, Pete - not encouraging at this point. "Normal" users will not be able to remember 365 codes. Can you think of another way to approach to the problem? Could a lookup be used somehow? Or maybe a dropdown in the header row? "Peter T" wrote: Tricky, not impossible but effectively not viable. There are no directly trappable mouse over or hover events in cells. Using a timer could periodically check mouse coordinates every (say) 0.2 sec' and if same conclude the mouse is stationary (could sub class windows mouse events but not stable in VBA). Having determined the mouse is not moving next task is to relate mouse coordinates to cell, it is doable with one of three distinct approaches (not sure about xl2007) but none are straightforward. Then the final task is to display something like a tooltip, rather than a form or some other object. I'm almost sure there is no way to do that purely with VBA. Of course, could do almost anything in a selection change event (except the tool tip, temporary dynamic comment perhaps). Regards, Peter T "Phil H." wrote in message ... MS Excel 2003, Workbook name "2008 Results." In worksheet named "Inventory," Column M has alpha-numeric codes - the worksheet contains 8000+ rows. In worksheet named "Function Codes," 365 a-n codes are held in column B, with their corresponding text descriptions in column C. What is needed: when the user hovers the cursor over a cell in "Inventory" column M, Excel finds the a-n code in worksheet "Function Codes" column B, and displays the corresponding text from column C the same way as in a hyperlink "Screen Tip." Finally, the user must be able to click on a column M cell, make an a-n code change, and the new text description appear when hovering over the changed cell (after the correction has been entered). What would the macro code be and where would it be placed? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hover Messages
Phil
It works fine for me here. Are you using an Italian version of Excel? Are your separators ; rather than , -- Regards Roger Govier "Phil H." wrote in message ... Here is the setup, and my code modification: Sheet 1 - FY08 Inventory Sheet 2 - Code Matrix T B C 1 Code Code Description (row 1 - headers) 2 230 200 Desc 200 3 245 201 Desc 201 * * * * 8119 Z234 Z999 Desc Z999 Sheet 1 column T is DV target column Code entered behind FY08 Inventory worksheet. Should it be elsewhere, say in Module 1? 'Enter comment from external list in target cell. Private Sub Worksheet_Change(By Val Target As Range) Dim messg As String If Not Intersect(Target, Range("T:T")) Is Nothing Then Message = WorksheetFunction.Index(Sheets("Code Matrix").Range("B:B"), _ WorksheetFunction.Match(Target.Value, _ Sheets("Code Matrix"). _ Range("C:C"), 0)) On Error Resume Next Target.Cells.AddComment Target.Cells.Comment.Visible = False Target.Cells.Comment.Text Text:="" & Chr(10) & messg On Error GoTo 0 End If End Sub Compile error: Expected: list separator or ) First line of code, Private, is red "Roger Govier" wrote: Hi Phil Following on from Peter's idea, I have played about with adding comments to the cell. If you have a List (in my case on Sheet2 column A) for your DV items, with a list of descriptions for those items along side in column B (Sheet2), then the following event code on your sheet with the DV (Sheet1), will insert the comment to the cell depending upon what has been selected. Hovering over the cell will cause the Comment to be displayed. Private Sub Worksheet_Change(By Val Target As Range) Dim messg As String If Not Intersect(Target, Range("A:A")) Is Nothing Then messg = WorksheetFunction.Index(Sheets("Sheet2").Range("B: B"), _ WorksheetFunction.Match(Target.Value, Sheets("Sheet2"). _ Range("A:A"), 0)) On Error Resume Next Target.Cells.AddComment Target.Cells.Comment.Visible = False Target.Cells.Comment.Text Text:="" & Chr(10) & messg On Error GoTo 0 End If End Sub Obviously, change the ranges to suit your situation. -- Regards Roger Govier "Phil H." wrote in message ... Peter, I think "List" may be the idea. I will play with the validation idea. In the meantime, I was wondering if we can go behind the scenes and add a second column to the filter dropdown list. This would be perfect, if doable, because of the many many columns with codes. The further I get into this, the more I think I will press for converting this to Access - after the current situation passes. "Peter T" wrote: Have you considered using Data Validation / List in all your input cells, where the list refers to your codes (could be a ref or a named range). In the next column you could have a lookup formula that returns the description based on the code in the input cell (or blank if code cell is empty). Regards, Peter T "Phil H." wrote in message ... Thanks, Pete - not encouraging at this point. "Normal" users will not be able to remember 365 codes. Can you think of another way to approach to the problem? Could a lookup be used somehow? Or maybe a dropdown in the header row? "Peter T" wrote: Tricky, not impossible but effectively not viable. There are no directly trappable mouse over or hover events in cells. Using a timer could periodically check mouse coordinates every (say) 0.2 sec' and if same conclude the mouse is stationary (could sub class windows mouse events but not stable in VBA). Having determined the mouse is not moving next task is to relate mouse coordinates to cell, it is doable with one of three distinct approaches (not sure about xl2007) but none are straightforward. Then the final task is to display something like a tooltip, rather than a form or some other object. I'm almost sure there is no way to do that purely with VBA. Of course, could do almost anything in a selection change event (except the tool tip, temporary dynamic comment perhaps). Regards, Peter T "Phil H." wrote in message ... MS Excel 2003, Workbook name "2008 Results." In worksheet named "Inventory," Column M has alpha-numeric codes - the worksheet contains 8000+ rows. In worksheet named "Function Codes," 365 a-n codes are held in column B, with their corresponding text descriptions in column C. What is needed: when the user hovers the cursor over a cell in "Inventory" column M, Excel finds the a-n code in worksheet "Function Codes" column B, and displays the corresponding text from column C the same way as in a hyperlink "Screen Tip." Finally, the user must be able to click on a column M cell, make an a-n code change, and the new text description appear when hovering over the changed cell (after the correction has been entered). What would the macro code be and where would it be placed? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hover Messages
For the benefit of the archives, I received a copy of the file direct from
Phil. I hadn't noticed in the posting, be had introduced a space into By Val instead of ByVal. That accounted for the line showing red in the VBE. Also, Phil had adjusted the wrong ranges for the Index match. In his scenario it needed to be messg = WorksheetFunction.Index(Sheets("Code Matrix").Range("C:C"), _ WorksheetFunction.Match(Target.Value, Sheets("Code Matrix"). _ Range("B:B"), 0)) -- Regards Roger Govier "Phil H." wrote in message ... Here is the setup, and my code modification: Sheet 1 - FY08 Inventory Sheet 2 - Code Matrix T B C 1 Code Code Description (row 1 - headers) 2 230 200 Desc 200 3 245 201 Desc 201 * * * * 8119 Z234 Z999 Desc Z999 Sheet 1 column T is DV target column Code entered behind FY08 Inventory worksheet. Should it be elsewhere, say in Module 1? 'Enter comment from external list in target cell. Private Sub Worksheet_Change(By Val Target As Range) Dim messg As String If Not Intersect(Target, Range("T:T")) Is Nothing Then Message = WorksheetFunction.Index(Sheets("Code Matrix").Range("B:B"), _ WorksheetFunction.Match(Target.Value, _ Sheets("Code Matrix"). _ Range("C:C"), 0)) On Error Resume Next Target.Cells.AddComment Target.Cells.Comment.Visible = False Target.Cells.Comment.Text Text:="" & Chr(10) & messg On Error GoTo 0 End If End Sub Compile error: Expected: list separator or ) First line of code, Private, is red "Roger Govier" wrote: Hi Phil Following on from Peter's idea, I have played about with adding comments to the cell. If you have a List (in my case on Sheet2 column A) for your DV items, with a list of descriptions for those items along side in column B (Sheet2), then the following event code on your sheet with the DV (Sheet1), will insert the comment to the cell depending upon what has been selected. Hovering over the cell will cause the Comment to be displayed. Private Sub Worksheet_Change(By Val Target As Range) Dim messg As String If Not Intersect(Target, Range("A:A")) Is Nothing Then messg = WorksheetFunction.Index(Sheets("Sheet2").Range("B: B"), _ WorksheetFunction.Match(Target.Value, Sheets("Sheet2"). _ Range("A:A"), 0)) On Error Resume Next Target.Cells.AddComment Target.Cells.Comment.Visible = False Target.Cells.Comment.Text Text:="" & Chr(10) & messg On Error GoTo 0 End If End Sub Obviously, change the ranges to suit your situation. -- Regards Roger Govier "Phil H." wrote in message ... Peter, I think "List" may be the idea. I will play with the validation idea. In the meantime, I was wondering if we can go behind the scenes and add a second column to the filter dropdown list. This would be perfect, if doable, because of the many many columns with codes. The further I get into this, the more I think I will press for converting this to Access - after the current situation passes. "Peter T" wrote: Have you considered using Data Validation / List in all your input cells, where the list refers to your codes (could be a ref or a named range). In the next column you could have a lookup formula that returns the description based on the code in the input cell (or blank if code cell is empty). Regards, Peter T "Phil H." wrote in message ... Thanks, Pete - not encouraging at this point. "Normal" users will not be able to remember 365 codes. Can you think of another way to approach to the problem? Could a lookup be used somehow? Or maybe a dropdown in the header row? "Peter T" wrote: Tricky, not impossible but effectively not viable. There are no directly trappable mouse over or hover events in cells. Using a timer could periodically check mouse coordinates every (say) 0.2 sec' and if same conclude the mouse is stationary (could sub class windows mouse events but not stable in VBA). Having determined the mouse is not moving next task is to relate mouse coordinates to cell, it is doable with one of three distinct approaches (not sure about xl2007) but none are straightforward. Then the final task is to display something like a tooltip, rather than a form or some other object. I'm almost sure there is no way to do that purely with VBA. Of course, could do almost anything in a selection change event (except the tool tip, temporary dynamic comment perhaps). Regards, Peter T "Phil H." wrote in message ... MS Excel 2003, Workbook name "2008 Results." In worksheet named "Inventory," Column M has alpha-numeric codes - the worksheet contains 8000+ rows. In worksheet named "Function Codes," 365 a-n codes are held in column B, with their corresponding text descriptions in column C. What is needed: when the user hovers the cursor over a cell in "Inventory" column M, Excel finds the a-n code in worksheet "Function Codes" column B, and displays the corresponding text from column C the same way as in a hyperlink "Screen Tip." Finally, the user must be able to click on a column M cell, make an a-n code change, and the new text description appear when hovering over the changed cell (after the correction has been entered). What would the macro code be and where would it be placed? |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hover Messages
Hello again,
I'm not really following what it is that needs to be displayed in something like a tool tip that can't be done similarly (well not quite the same) with a dropdown. If the dropdown approach is not viable, and the comment not ideal (or how about a callout) it should be possible to display a form next to the cell that has the mouse hovering over it (not necessarily the selected cell), with whatever info on the form as relates to the cell. The modeless form could be made to look like a tooltip, and disappear when the mouse moves again. Tricky and a bit of work but feasible. Regards, Peter T "Phil H." wrote in message ... Hi Again Roger - we met in London 2006 at the Excel conference, I'm the fly fisherman - gave thought to staying at your place in spring '07. Will try the comment idea - seems totally feasible. Is there a way to eliminate the red comment symbol in the upper right corner? Might require some sort of search macro to identify cells with comments and, say, temporarily apply a background color. These symbols could make the worksheet very "busy," adding confusion to beginning users. Basically, my purpose is to give users an on-screen definition of a code (there are many columns with similar situations), without having to search elsewhere for the information. Again, I'm dealing with 89 columns and 8000+ rows (admittedly way too much), so my current task is to make the worksheet a user-friendly environment - which currently it is not. The user's task is to go through the 722,000+ cells and clean out errors - where incompatible codes exist (on a row). I'm putting together a code matrix to set the rules for the exercise. Will probably be looking for help next week after I receive the latest spreadsheet. So, for now users are filtering and with a very short lead time for scrub completion, on-screen helps seem my only contribution. If you have any other ideas, let me know. Meanwhile, I'll try the comments macro. This is a neat idea for other worksheets - will put it in my library. Thanks for the help, Roger. "Roger Govier" wrote: Hi Phil Following on from Peter's idea, I have played about with adding comments to the cell. If you have a List (in my case on Sheet2 column A) for your DV items, with a list of descriptions for those items along side in column B (Sheet2), then the following event code on your sheet with the DV (Sheet1), will insert the comment to the cell depending upon what has been selected. Hovering over the cell will cause the Comment to be displayed. Private Sub Worksheet_Change(By Val Target As Range) Dim messg As String If Not Intersect(Target, Range("A:A")) Is Nothing Then messg = WorksheetFunction.Index(Sheets("Sheet2").Range("B: B"), _ WorksheetFunction.Match(Target.Value, Sheets("Sheet2"). _ Range("A:A"), 0)) On Error Resume Next Target.Cells.AddComment Target.Cells.Comment.Visible = False Target.Cells.Comment.Text Text:="" & Chr(10) & messg On Error GoTo 0 End If End Sub Obviously, change the ranges to suit your situation. -- Regards Roger Govier "Phil H." wrote in message ... Peter, I think "List" may be the idea. I will play with the validation idea. In the meantime, I was wondering if we can go behind the scenes and add a second column to the filter dropdown list. This would be perfect, if doable, because of the many many columns with codes. The further I get into this, the more I think I will press for converting this to Access - after the current situation passes. "Peter T" wrote: Have you considered using Data Validation / List in all your input cells, where the list refers to your codes (could be a ref or a named range). In the next column you could have a lookup formula that returns the description based on the code in the input cell (or blank if code cell is empty). Regards, Peter T "Phil H." wrote in message ... Thanks, Pete - not encouraging at this point. "Normal" users will not be able to remember 365 codes. Can you think of another way to approach to the problem? Could a lookup be used somehow? Or maybe a dropdown in the header row? "Peter T" wrote: Tricky, not impossible but effectively not viable. There are no directly trappable mouse over or hover events in cells. Using a timer could periodically check mouse coordinates every (say) 0.2 sec' and if same conclude the mouse is stationary (could sub class windows mouse events but not stable in VBA). Having determined the mouse is not moving next task is to relate mouse coordinates to cell, it is doable with one of three distinct approaches (not sure about xl2007) but none are straightforward. Then the final task is to display something like a tooltip, rather than a form or some other object. I'm almost sure there is no way to do that purely with VBA. Of course, could do almost anything in a selection change event (except the tool tip, temporary dynamic comment perhaps). Regards, Peter T "Phil H." wrote in message ... MS Excel 2003, Workbook name "2008 Results." In worksheet named "Inventory," Column M has alpha-numeric codes - the worksheet contains 8000+ rows. In worksheet named "Function Codes," 365 a-n codes are held in column B, with their corresponding text descriptions in column C. What is needed: when the user hovers the cursor over a cell in "Inventory" column M, Excel finds the a-n code in worksheet "Function Codes" column B, and displays the corresponding text from column C the same way as in a hyperlink "Screen Tip." Finally, the user must be able to click on a column M cell, make an a-n code change, and the new text description appear when hovering over the changed cell (after the correction has been entered). What would the macro code be and where would it be placed? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hover over text | Excel Programming | |||
Hover-over Function | Excel Programming | |||
a button that will hover | Excel Programming | |||
Hover change | Excel Discussion (Misc queries) | |||
Hover over variable? | Excel Programming |