Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Link comment boxes based on different cell values
Help!!!
I am trying to change the data within my comment boxes dependant on the cell value and if the source changes. In my workbook I have 4 worksheets Sheets 1 - 3 are my data sheets where the values can change Sheet 4 is my working sheet where I want comment boxes added to each cell dependant on the cell value. Ie if Cell A1 = F then a comment box should be added which takes the comment from Worksheet 1 cell A1. etc etc etc However I also need any updates on my data sheets to be shown in the comments boxes on worksheet 4. Any ideas please .... ??? I hope that makes sense, if not please say and I will try to clarify it better Thanks in advance Mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Link comment boxes based on different cell values
First, I am confused about what you want.
If cell A1 is different from F, then what happens? What are the rules? But you may find that your life in excel will be much easier if you keep your data that you want in cells--not comments. You'll be able to use the builtin functions (like =vlookup()) to retrieve those values. Mike wrote: Help!!! I am trying to change the data within my comment boxes dependant on the cell value and if the source changes. In my workbook I have 4 worksheets Sheets 1 - 3 are my data sheets where the values can change Sheet 4 is my working sheet where I want comment boxes added to each cell dependant on the cell value. Ie if Cell A1 = F then a comment box should be added which takes the comment from Worksheet 1 cell A1. etc etc etc However I also need any updates on my data sheets to be shown in the comments boxes on worksheet 4. Any ideas please .... ??? I hope that makes sense, if not please say and I will try to clarify it better Thanks in advance Mike -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Link comment boxes based on different cell values
Hi Dave
Thanks for the response. Ok I will have 5 states for each cell. These will be: F, P, S, E or <blank Depending on the state I need a comment added to the cell and the data for that comment to come from cell A1 for F, A2 for P, A3 for S, A4 for E and if <blank no comment to be added. If only I could do exactly what you suggested with vlookups or even just a link to the other sheet would be fantastic, however I need to keep the state in my cell so a comment box is the best way. I know I can use: =getvalueandcomment(a1) statement in the actual cell to pull in the data and comment from the corresponding cells in the other worksheets, however - how do i get this to work in a macro that would allow me to choose which value and comment to pull across depending on the cell state? Is this possible or am i trying to do something that is beyond excel? Mike "Dave Peterson" wrote: First, I am confused about what you want. If cell A1 is different from F, then what happens? What are the rules? But you may find that your life in excel will be much easier if you keep your data that you want in cells--not comments. You'll be able to use the builtin functions (like =vlookup()) to retrieve those values. Mike wrote: Help!!! I am trying to change the data within my comment boxes dependant on the cell value and if the source changes. In my workbook I have 4 worksheets Sheets 1 - 3 are my data sheets where the values can change Sheet 4 is my working sheet where I want comment boxes added to each cell dependant on the cell value. Ie if Cell A1 = F then a comment box should be added which takes the comment from Worksheet 1 cell A1. etc etc etc However I also need any updates on my data sheets to be shown in the comments boxes on worksheet 4. Any ideas please .... ??? I hope that makes sense, if not please say and I will try to clarify it better Thanks in advance Mike -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Link comment boxes based on different cell values
I still wouldn't use a comment for this.
Why not just insert another column to the right of your indicator column: =if(a1="f",sheet1!a1,if(a1="P",sheet1!a2, ..... Mike wrote: Hi Dave Thanks for the response. Ok I will have 5 states for each cell. These will be: F, P, S, E or <blank Depending on the state I need a comment added to the cell and the data for that comment to come from cell A1 for F, A2 for P, A3 for S, A4 for E and if <blank no comment to be added. If only I could do exactly what you suggested with vlookups or even just a link to the other sheet would be fantastic, however I need to keep the state in my cell so a comment box is the best way. I know I can use: =getvalueandcomment(a1) statement in the actual cell to pull in the data and comment from the corresponding cells in the other worksheets, however - how do i get this to work in a macro that would allow me to choose which value and comment to pull across depending on the cell state? Is this possible or am i trying to do something that is beyond excel? Mike "Dave Peterson" wrote: First, I am confused about what you want. If cell A1 is different from F, then what happens? What are the rules? But you may find that your life in excel will be much easier if you keep your data that you want in cells--not comments. You'll be able to use the builtin functions (like =vlookup()) to retrieve those values. Mike wrote: Help!!! I am trying to change the data within my comment boxes dependant on the cell value and if the source changes. In my workbook I have 4 worksheets Sheets 1 - 3 are my data sheets where the values can change Sheet 4 is my working sheet where I want comment boxes added to each cell dependant on the cell value. Ie if Cell A1 = F then a comment box should be added which takes the comment from Worksheet 1 cell A1. etc etc etc However I also need any updates on my data sheets to be shown in the comments boxes on worksheet 4. Any ideas please .... ??? I hope that makes sense, if not please say and I will try to clarify it better Thanks in advance Mike -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Link comment boxes based on different cell values
i have about 65 columns and 200 rows!
was hoping to do this by a macro but if this isnt possible i will have to use an IF statement. Thanks anyway "Dave Peterson" wrote: I still wouldn't use a comment for this. Why not just insert another column to the right of your indicator column: =if(a1="f",sheet1!a1,if(a1="P",sheet1!a2, ..... Mike wrote: Hi Dave Thanks for the response. Ok I will have 5 states for each cell. These will be: F, P, S, E or <blank Depending on the state I need a comment added to the cell and the data for that comment to come from cell A1 for F, A2 for P, A3 for S, A4 for E and if <blank no comment to be added. If only I could do exactly what you suggested with vlookups or even just a link to the other sheet would be fantastic, however I need to keep the state in my cell so a comment box is the best way. I know I can use: =getvalueandcomment(a1) statement in the actual cell to pull in the data and comment from the corresponding cells in the other worksheets, however - how do i get this to work in a macro that would allow me to choose which value and comment to pull across depending on the cell state? Is this possible or am i trying to do something that is beyond excel? Mike "Dave Peterson" wrote: First, I am confused about what you want. If cell A1 is different from F, then what happens? What are the rules? But you may find that your life in excel will be much easier if you keep your data that you want in cells--not comments. You'll be able to use the builtin functions (like =vlookup()) to retrieve those values. Mike wrote: Help!!! I am trying to change the data within my comment boxes dependant on the cell value and if the source changes. In my workbook I have 4 worksheets Sheets 1 - 3 are my data sheets where the values can change Sheet 4 is my working sheet where I want comment boxes added to each cell dependant on the cell value. Ie if Cell A1 = F then a comment box should be added which takes the comment from Worksheet 1 cell A1. etc etc etc However I also need any updates on my data sheets to be shown in the comments boxes on worksheet 4. Any ideas please .... ??? I hope that makes sense, if not please say and I will try to clarify it better Thanks in advance Mike -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Link comment boxes based on different cell values
So you want to copy the value of a cell (A1, A2, A3, A4) into the comment of a
different cell based on that cell's value? If yes, maybe you could use a macro like this: Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Dim res As Variant Dim myVals As Variant Dim myCommentStartingCell As Range myVals = Array("F", "P", "S", "E") Set myCommentStartingCell = Worksheets("CommentSheetName").Range("a1") Set myRng = Selection For Each myCell In myRng.Cells With myCell If .Comment Is Nothing Then 'do nothing Else .Comment.Delete End If res = Application.Match(.Value, myVals, 0) If IsError(res) Then 'do nothing Else .AddComment _ Text:=myCommentStartingCell.Offset(res - 1, 0).Value End If End With Next myCell End Sub Mike wrote: i have about 65 columns and 200 rows! was hoping to do this by a macro but if this isnt possible i will have to use an IF statement. Thanks anyway "Dave Peterson" wrote: I still wouldn't use a comment for this. Why not just insert another column to the right of your indicator column: =if(a1="f",sheet1!a1,if(a1="P",sheet1!a2, ..... Mike wrote: Hi Dave Thanks for the response. Ok I will have 5 states for each cell. These will be: F, P, S, E or <blank Depending on the state I need a comment added to the cell and the data for that comment to come from cell A1 for F, A2 for P, A3 for S, A4 for E and if <blank no comment to be added. If only I could do exactly what you suggested with vlookups or even just a link to the other sheet would be fantastic, however I need to keep the state in my cell so a comment box is the best way. I know I can use: =getvalueandcomment(a1) statement in the actual cell to pull in the data and comment from the corresponding cells in the other worksheets, however - how do i get this to work in a macro that would allow me to choose which value and comment to pull across depending on the cell state? Is this possible or am i trying to do something that is beyond excel? Mike "Dave Peterson" wrote: First, I am confused about what you want. If cell A1 is different from F, then what happens? What are the rules? But you may find that your life in excel will be much easier if you keep your data that you want in cells--not comments. You'll be able to use the builtin functions (like =vlookup()) to retrieve those values. Mike wrote: Help!!! I am trying to change the data within my comment boxes dependant on the cell value and if the source changes. In my workbook I have 4 worksheets Sheets 1 - 3 are my data sheets where the values can change Sheet 4 is my working sheet where I want comment boxes added to each cell dependant on the cell value. Ie if Cell A1 = F then a comment box should be added which takes the comment from Worksheet 1 cell A1. etc etc etc However I also need any updates on my data sheets to be shown in the comments boxes on worksheet 4. Any ideas please .... ??? I hope that makes sense, if not please say and I will try to clarify it better Thanks in advance Mike -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Link comment boxes based on different cell values
Hi Dave
Thanks for your response so would this macro go thru my whole spreadsheet and check the values of each cell and where appropiate create a comment field of the right information pulled from my other work sheet or would it need a few alterations for that? Sorry if this is a divvy question but i had trouble following the macro and understanding how it was working! Mike "Dave Peterson" wrote: So you want to copy the value of a cell (A1, A2, A3, A4) into the comment of a different cell based on that cell's value? If yes, maybe you could use a macro like this: Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Dim res As Variant Dim myVals As Variant Dim myCommentStartingCell As Range myVals = Array("F", "P", "S", "E") Set myCommentStartingCell = Worksheets("CommentSheetName").Range("a1") Set myRng = Selection For Each myCell In myRng.Cells With myCell If .Comment Is Nothing Then 'do nothing Else .Comment.Delete End If res = Application.Match(.Value, myVals, 0) If IsError(res) Then 'do nothing Else .AddComment _ Text:=myCommentStartingCell.Offset(res - 1, 0).Value End If End With Next myCell End Sub Mike wrote: i have about 65 columns and 200 rows! was hoping to do this by a macro but if this isnt possible i will have to use an IF statement. Thanks anyway "Dave Peterson" wrote: I still wouldn't use a comment for this. Why not just insert another column to the right of your indicator column: =if(a1="f",sheet1!a1,if(a1="P",sheet1!a2, ..... Mike wrote: Hi Dave Thanks for the response. Ok I will have 5 states for each cell. These will be: F, P, S, E or <blank Depending on the state I need a comment added to the cell and the data for that comment to come from cell A1 for F, A2 for P, A3 for S, A4 for E and if <blank no comment to be added. If only I could do exactly what you suggested with vlookups or even just a link to the other sheet would be fantastic, however I need to keep the state in my cell so a comment box is the best way. I know I can use: =getvalueandcomment(a1) statement in the actual cell to pull in the data and comment from the corresponding cells in the other worksheets, however - how do i get this to work in a macro that would allow me to choose which value and comment to pull across depending on the cell state? Is this possible or am i trying to do something that is beyond excel? Mike "Dave Peterson" wrote: First, I am confused about what you want. If cell A1 is different from F, then what happens? What are the rules? But you may find that your life in excel will be much easier if you keep your data that you want in cells--not comments. You'll be able to use the builtin functions (like =vlookup()) to retrieve those values. Mike wrote: Help!!! I am trying to change the data within my comment boxes dependant on the cell value and if the source changes. In my workbook I have 4 worksheets Sheets 1 - 3 are my data sheets where the values can change Sheet 4 is my working sheet where I want comment boxes added to each cell dependant on the cell value. Ie if Cell A1 = F then a comment box should be added which takes the comment from Worksheet 1 cell A1. etc etc etc However I also need any updates on my data sheets to be shown in the comments boxes on worksheet 4. Any ideas please .... ??? I hope that makes sense, if not please say and I will try to clarify it better Thanks in advance Mike -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Link comment boxes based on different cell values
Not the whole worksheet or workbook.
It only works on the range you selected. Set myRng = Selection For Each myCell In myRng.Cells Mike wrote: Hi Dave Thanks for your response so would this macro go thru my whole spreadsheet and check the values of each cell and where appropiate create a comment field of the right information pulled from my other work sheet or would it need a few alterations for that? Sorry if this is a divvy question but i had trouble following the macro and understanding how it was working! Mike "Dave Peterson" wrote: So you want to copy the value of a cell (A1, A2, A3, A4) into the comment of a different cell based on that cell's value? If yes, maybe you could use a macro like this: Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Dim res As Variant Dim myVals As Variant Dim myCommentStartingCell As Range myVals = Array("F", "P", "S", "E") Set myCommentStartingCell = Worksheets("CommentSheetName").Range("a1") Set myRng = Selection For Each myCell In myRng.Cells With myCell If .Comment Is Nothing Then 'do nothing Else .Comment.Delete End If res = Application.Match(.Value, myVals, 0) If IsError(res) Then 'do nothing Else .AddComment _ Text:=myCommentStartingCell.Offset(res - 1, 0).Value End If End With Next myCell End Sub Mike wrote: i have about 65 columns and 200 rows! was hoping to do this by a macro but if this isnt possible i will have to use an IF statement. Thanks anyway "Dave Peterson" wrote: I still wouldn't use a comment for this. Why not just insert another column to the right of your indicator column: =if(a1="f",sheet1!a1,if(a1="P",sheet1!a2, ..... Mike wrote: Hi Dave Thanks for the response. Ok I will have 5 states for each cell. These will be: F, P, S, E or <blank Depending on the state I need a comment added to the cell and the data for that comment to come from cell A1 for F, A2 for P, A3 for S, A4 for E and if <blank no comment to be added. If only I could do exactly what you suggested with vlookups or even just a link to the other sheet would be fantastic, however I need to keep the state in my cell so a comment box is the best way. I know I can use: =getvalueandcomment(a1) statement in the actual cell to pull in the data and comment from the corresponding cells in the other worksheets, however - how do i get this to work in a macro that would allow me to choose which value and comment to pull across depending on the cell state? Is this possible or am i trying to do something that is beyond excel? Mike "Dave Peterson" wrote: First, I am confused about what you want. If cell A1 is different from F, then what happens? What are the rules? But you may find that your life in excel will be much easier if you keep your data that you want in cells--not comments. You'll be able to use the builtin functions (like =vlookup()) to retrieve those values. Mike wrote: Help!!! I am trying to change the data within my comment boxes dependant on the cell value and if the source changes. In my workbook I have 4 worksheets Sheets 1 - 3 are my data sheets where the values can change Sheet 4 is my working sheet where I want comment boxes added to each cell dependant on the cell value. Ie if Cell A1 = F then a comment box should be added which takes the comment from Worksheet 1 cell A1. etc etc etc However I also need any updates on my data sheets to be shown in the comments boxes on worksheet 4. Any ideas please .... ??? I hope that makes sense, if not please say and I will try to clarify it better Thanks in advance Mike -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Link comment boxes based on different cell values
Hi Dave
I have tried this macro but it keeps chucking up the following error: 'Run Time error "1004": Application-defined or object-defined arror Then it takes me to the following line in the code: Else .AddComment _ Text:=myCommentStartingCell.Offset(res - 1, 0).Value What am i doing wrong please Mike "Dave Peterson" wrote: Not the whole worksheet or workbook. It only works on the range you selected. Set myRng = Selection For Each myCell In myRng.Cells Mike wrote: Hi Dave Thanks for your response so would this macro go thru my whole spreadsheet and check the values of each cell and where appropiate create a comment field of the right information pulled from my other work sheet or would it need a few alterations for that? Sorry if this is a divvy question but i had trouble following the macro and understanding how it was working! Mike "Dave Peterson" wrote: So you want to copy the value of a cell (A1, A2, A3, A4) into the comment of a different cell based on that cell's value? If yes, maybe you could use a macro like this: Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Dim res As Variant Dim myVals As Variant Dim myCommentStartingCell As Range myVals = Array("F", "P", "S", "E") Set myCommentStartingCell = Worksheets("CommentSheetName").Range("a1") Set myRng = Selection For Each myCell In myRng.Cells With myCell If .Comment Is Nothing Then 'do nothing Else .Comment.Delete End If res = Application.Match(.Value, myVals, 0) If IsError(res) Then 'do nothing Else .AddComment _ Text:=myCommentStartingCell.Offset(res - 1, 0).Value End If End With Next myCell End Sub Mike wrote: i have about 65 columns and 200 rows! was hoping to do this by a macro but if this isnt possible i will have to use an IF statement. Thanks anyway "Dave Peterson" wrote: I still wouldn't use a comment for this. Why not just insert another column to the right of your indicator column: =if(a1="f",sheet1!a1,if(a1="P",sheet1!a2, ..... Mike wrote: Hi Dave Thanks for the response. Ok I will have 5 states for each cell. These will be: F, P, S, E or <blank Depending on the state I need a comment added to the cell and the data for that comment to come from cell A1 for F, A2 for P, A3 for S, A4 for E and if <blank no comment to be added. If only I could do exactly what you suggested with vlookups or even just a link to the other sheet would be fantastic, however I need to keep the state in my cell so a comment box is the best way. I know I can use: =getvalueandcomment(a1) statement in the actual cell to pull in the data and comment from the corresponding cells in the other worksheets, however - how do i get this to work in a macro that would allow me to choose which value and comment to pull across depending on the cell state? Is this possible or am i trying to do something that is beyond excel? Mike "Dave Peterson" wrote: First, I am confused about what you want. If cell A1 is different from F, then what happens? What are the rules? But you may find that your life in excel will be much easier if you keep your data that you want in cells--not comments. You'll be able to use the builtin functions (like =vlookup()) to retrieve those values. Mike wrote: Help!!! I am trying to change the data within my comment boxes dependant on the cell value and if the source changes. In my workbook I have 4 worksheets Sheets 1 - 3 are my data sheets where the values can change Sheet 4 is my working sheet where I want comment boxes added to each cell dependant on the cell value. Ie if Cell A1 = F then a comment box should be added which takes the comment from Worksheet 1 cell A1. etc etc etc However I also need any updates on my data sheets to be shown in the comments boxes on worksheet 4. Any ideas please .... ??? I hope that makes sense, if not please say and I will try to clarify it better Thanks in advance Mike -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Link comment boxes based on different cell values
It worked fine for me. Did you change anything else?
If you did, you'll want to post your current version of the code. Mike wrote: Hi Dave I have tried this macro but it keeps chucking up the following error: 'Run Time error "1004": Application-defined or object-defined arror Then it takes me to the following line in the code: Else .AddComment _ Text:=myCommentStartingCell.Offset(res - 1, 0).Value What am i doing wrong please Mike "Dave Peterson" wrote: Not the whole worksheet or workbook. It only works on the range you selected. Set myRng = Selection For Each myCell In myRng.Cells Mike wrote: Hi Dave Thanks for your response so would this macro go thru my whole spreadsheet and check the values of each cell and where appropiate create a comment field of the right information pulled from my other work sheet or would it need a few alterations for that? Sorry if this is a divvy question but i had trouble following the macro and understanding how it was working! Mike "Dave Peterson" wrote: So you want to copy the value of a cell (A1, A2, A3, A4) into the comment of a different cell based on that cell's value? If yes, maybe you could use a macro like this: Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Dim res As Variant Dim myVals As Variant Dim myCommentStartingCell As Range myVals = Array("F", "P", "S", "E") Set myCommentStartingCell = Worksheets("CommentSheetName").Range("a1") Set myRng = Selection For Each myCell In myRng.Cells With myCell If .Comment Is Nothing Then 'do nothing Else .Comment.Delete End If res = Application.Match(.Value, myVals, 0) If IsError(res) Then 'do nothing Else .AddComment _ Text:=myCommentStartingCell.Offset(res - 1, 0).Value End If End With Next myCell End Sub Mike wrote: i have about 65 columns and 200 rows! was hoping to do this by a macro but if this isnt possible i will have to use an IF statement. Thanks anyway "Dave Peterson" wrote: I still wouldn't use a comment for this. Why not just insert another column to the right of your indicator column: =if(a1="f",sheet1!a1,if(a1="P",sheet1!a2, ..... Mike wrote: Hi Dave Thanks for the response. Ok I will have 5 states for each cell. These will be: F, P, S, E or <blank Depending on the state I need a comment added to the cell and the data for that comment to come from cell A1 for F, A2 for P, A3 for S, A4 for E and if <blank no comment to be added. If only I could do exactly what you suggested with vlookups or even just a link to the other sheet would be fantastic, however I need to keep the state in my cell so a comment box is the best way. I know I can use: =getvalueandcomment(a1) statement in the actual cell to pull in the data and comment from the corresponding cells in the other worksheets, however - how do i get this to work in a macro that would allow me to choose which value and comment to pull across depending on the cell state? Is this possible or am i trying to do something that is beyond excel? Mike "Dave Peterson" wrote: First, I am confused about what you want. If cell A1 is different from F, then what happens? What are the rules? But you may find that your life in excel will be much easier if you keep your data that you want in cells--not comments. You'll be able to use the builtin functions (like =vlookup()) to retrieve those values. Mike wrote: Help!!! I am trying to change the data within my comment boxes dependant on the cell value and if the source changes. In my workbook I have 4 worksheets Sheets 1 - 3 are my data sheets where the values can change Sheet 4 is my working sheet where I want comment boxes added to each cell dependant on the cell value. Ie if Cell A1 = F then a comment box should be added which takes the comment from Worksheet 1 cell A1. etc etc etc However I also need any updates on my data sheets to be shown in the comments boxes on worksheet 4. Any ideas please .... ??? I hope that makes sense, if not please say and I will try to clarify it better Thanks in advance Mike -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I link the text in a cell into a comment box? | Excel Discussion (Misc queries) | |||
How do I create comment boxes that appear when a cell is clicked? | Excel Worksheet Functions | |||
Show one of multiple comment boxes based on value | Excel Programming | |||
Cell comment boxes shouldn't change size/shape unless changed by u | Excel Discussion (Misc queries) | |||
Transcribing cell formulas into their comment boxes | Excel Programming |