Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comment Box to include a range of cells
Hi,
I need to make put a comment box on a cell but the actual comment comes from a range of cells on a different sheet. The range will always be ("B74:J80") but the sheet name will change. The sheet name will always be in cell J3 of the sheet. So i need the comment box to be in cell J13 with the comment being from B74:J80 on another sheet, the sheets name will be in cell J3. Will I need to create a variable for the range? Any help on this will be hugely appreciated. N1KO |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comment Box to include a range of cells
Exactly how did you want to get the comment from B74:J80... that is some 63
cells... did you want to concatenate **all** of them into a single piece of text (and, if so, how... column by column then row by row)? -- Rick (MVP - Excel) "N1KO" wrote in message ... Hi, I need to make put a comment box on a cell but the actual comment comes from a range of cells on a different sheet. The range will always be ("B74:J80") but the sheet name will change. The sheet name will always be in cell J3 of the sheet. So i need the comment box to be in cell J13 with the comment being from B74:J80 on another sheet, the sheets name will be in cell J3. Will I need to create a variable for the range? Any help on this will be hugely appreciated. N1KO |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comment Box to include a range of cells
Hi,
Need a little more information 1) Are cells B74:J80 merged cells? 2) Does each cell between B74:J80 have a different comment in it? 3) Do you want one cell comment (J13) to to contain the comments of 70 cells if so what do you want the information seperated by ie commer or hyphen or full stop do you wnat to know which cell the comment came from? 4) does the sheet name J3 is it on the sheet where the new comment is going or is it on the sheet where the comments are comming from? On recept of information we should be able to set up some kind of module that will do the job for you. -- Trev B "N1KO" wrote: Hi, I need to make put a comment box on a cell but the actual comment comes from a range of cells on a different sheet. The range will always be ("B74:J80") but the sheet name will change. The sheet name will always be in cell J3 of the sheet. So i need the comment box to be in cell J13 with the comment being from B74:J80 on another sheet, the sheets name will be in cell J3. Will I need to create a variable for the range? Any help on this will be hugely appreciated. N1KO |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comment Box to include a range of cells
In a Perfect world I'd like it to be in exactly the same format in the
comment box as it is on the sheet. Front ofhouse hrs1.00 NODS1 NODS per FOH hr1.00 Back ofhouse hrs1.00 NOMS1 NOMS per BOH hr1.00 TotalRota cost 1 Cash Limit 1 NODS:NOMS ratio1.00 Rota basedon sales 1 against target sales1 Flex against cash limit 1 Rota Flex 1.00% I realise the data above looks rubbish but i can't paste a picture onto here. I could do with if possible it being concatenated. The 9 columns and then drop down a line and the next 9 columns so eventually the comment box will be 9 columns wide and 7 rows high (obviously the text size would be small so it'd fit on the screen). Obvioulsy there isn't actually any columns or rows on a comment box so I'm guessing the formatting would be difficult but if I could get all of the data from the 63 cells (some of them are actually blank) into a comment box that'd be brilliant. Hope this remotely made sense. N1KO "Rick Rothstein" wrote: Exactly how did you want to get the comment from B74:J80... that is some 63 cells... did you want to concatenate **all** of them into a single piece of text (and, if so, how... column by column then row by row)? -- Rick (MVP - Excel) "N1KO" wrote in message ... Hi, I need to make put a comment box on a cell but the actual comment comes from a range of cells on a different sheet. The range will always be ("B74:J80") but the sheet name will change. The sheet name will always be in cell J3 of the sheet. So i need the comment box to be in cell J13 with the comment being from B74:J80 on another sheet, the sheets name will be in cell J3. Will I need to create a variable for the range? Any help on this will be hugely appreciated. N1KO |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comment Box to include a range of cells
Hi Trev,
None of the cells with in the range are merged. The values in each of the cells are different, there isn't any comments in the cells. I'd like the 1 comment box for cell J13 on the main sheet to have all values from the range of 54 cells (B74:J81) from the other sheet. (I've managed to get rid of a row) I don't need to know where the cell values have came from. The only way i need the info seperated is by row. (New row from the range = new line in the comment like below). Row 76 Row 77 Row 78 Row 79 Row 80 Row 81 Cell J3 is on the main sheet - Sheet's name Cell J13 is on the main sheet - Cell where the comment needs to be inserted. The range of cells are on the sheet who's name is held in cell J3 The name in J3 on the main sheet is the name of the sheet where the range (B76:J81) is held. So that is where values will be coming from. If you need anything else please say. This is hugely appreciated. N1KO "Trev B" wrote: Hi, Need a little more information 1) Are cells B74:J80 merged cells? 2) Does each cell between B74:J80 have a different comment in it? 3) Do you want one cell comment (J13) to to contain the comments of 70 cells if so what do you want the information seperated by ie commer or hyphen or full stop do you wnat to know which cell the comment came from? 4) does the sheet name J3 is it on the sheet where the new comment is going or is it on the sheet where the comments are comming from? On recept of information we should be able to set up some kind of module that will do the job for you. -- Trev B "N1KO" wrote: Hi, I need to make put a comment box on a cell but the actual comment comes from a range of cells on a different sheet. The range will always be ("B74:J80") but the sheet name will change. The sheet name will always be in cell J3 of the sheet. So i need the comment box to be in cell J13 with the comment being from B74:J80 on another sheet, the sheets name will be in cell J3. Will I need to create a variable for the range? Any help on this will be hugely appreciated. N1KO |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comment Box to include a range of cells
Hi N1KO,
Please add a macro button to the sheet you want the comment addng then add the following module:- Public Function UpdateJ13CommentBox() Dim strMainSheet As String 'name of mainsheet Dim strSubSheet As String 'name of sub sheet Dim varJ13Comment As Variant 'contains values of comments Dim varNewValue As Variant 'Contains current value of cell Dim lngCol As Long 'current col no Dim lngRow As Long 'current row no strMainSheet = ActiveSheet.Name 'get name of main sheet strSubSheet = Worksheets(strMainSheet).Cells(3, 10).Value 'get name of sheet from cell "J3" For lngRow = 76 To 81 'for each row between 76 to 81 For lngCol = 2 To 10 'for each column between B & J (2 to 10) varNewValue = Worksheets(strSubSheet).Cells(lngRow, lngCol).Value If Len(varNewValue) 0 Then 'do not add if cell is empty varJ13Comment = varJ13Comment & varNewValue & vbCrLf 'update comment with extra cell (vbcrlf = next line) End If Next lngCol 'get next column Next lngRow 'get next row Worksheets(strMainSheet).Cells(13, 10).ClearComments 'clear existing comment if any If Len(varJ13Comment) < 1 Then 'check if no values found varJ13Comment = "Sheet '" & strSubSheet _ & "' Cells B76 to J81 contain no values" 'tell user no values found Else varJ13Comment = Left(varJ13Comment, Len(varJ13Comment) - 1) 'delete the last carriage return (Next Line) End If Worksheets(strMainSheet).Cells(13, 10).AddComment (varJ13Comment) 'add new comment End Function -- Hope that this is want you wan if so please rate my work. Thanks Trev B "N1KO" wrote: Hi Trev, None of the cells with in the range are merged. The values in each of the cells are different, there isn't any comments in the cells. I'd like the 1 comment box for cell J13 on the main sheet to have all values from the range of 54 cells (B74:J81) from the other sheet. (I've managed to get rid of a row) I don't need to know where the cell values have came from. The only way i need the info seperated is by row. (New row from the range = new line in the comment like below). Row 76 Row 77 Row 78 Row 79 Row 80 Row 81 Cell J3 is on the main sheet - Sheet's name Cell J13 is on the main sheet - Cell where the comment needs to be inserted. The range of cells are on the sheet who's name is held in cell J3 The name in J3 on the main sheet is the name of the sheet where the range (B76:J81) is held. So that is where values will be coming from. If you need anything else please say. This is hugely appreciated. N1KO "Trev B" wrote: Hi, Need a little more information 1) Are cells B74:J80 merged cells? 2) Does each cell between B74:J80 have a different comment in it? 3) Do you want one cell comment (J13) to to contain the comments of 70 cells if so what do you want the information seperated by ie commer or hyphen or full stop do you wnat to know which cell the comment came from? 4) does the sheet name J3 is it on the sheet where the new comment is going or is it on the sheet where the comments are comming from? On recept of information we should be able to set up some kind of module that will do the job for you. -- Trev B "N1KO" wrote: Hi, I need to make put a comment box on a cell but the actual comment comes from a range of cells on a different sheet. The range will always be ("B74:J80") but the sheet name will change. The sheet name will always be in cell J3 of the sheet. So i need the comment box to be in cell J13 with the comment being from B74:J80 on another sheet, the sheets name will be in cell J3. Will I need to create a variable for the range? Any help on this will be hugely appreciated. N1KO |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comment Box to include a range of cells
Trev thank you very much.
It works almost but seems to put every cell on a new line, If this is the only way it can happen then that's fine with me. Can i adapt this to include other cells on the sheet easily? These ones being merged...... Appreciate the work you've done. Thanks Nick "Trev B" wrote: Hi N1KO, Please add a macro button to the sheet you want the comment addng then add the following module:- Public Function UpdateJ13CommentBox() Dim strMainSheet As String 'name of mainsheet Dim strSubSheet As String 'name of sub sheet Dim varJ13Comment As Variant 'contains values of comments Dim varNewValue As Variant 'Contains current value of cell Dim lngCol As Long 'current col no Dim lngRow As Long 'current row no strMainSheet = ActiveSheet.Name 'get name of main sheet strSubSheet = Worksheets(strMainSheet).Cells(3, 10).Value 'get name of sheet from cell "J3" For lngRow = 76 To 81 'for each row between 76 to 81 For lngCol = 2 To 10 'for each column between B & J (2 to 10) varNewValue = Worksheets(strSubSheet).Cells(lngRow, lngCol).Value If Len(varNewValue) 0 Then 'do not add if cell is empty varJ13Comment = varJ13Comment & varNewValue & vbCrLf 'update comment with extra cell (vbcrlf = next line) End If Next lngCol 'get next column Next lngRow 'get next row Worksheets(strMainSheet).Cells(13, 10).ClearComments 'clear existing comment if any If Len(varJ13Comment) < 1 Then 'check if no values found varJ13Comment = "Sheet '" & strSubSheet _ & "' Cells B76 to J81 contain no values" 'tell user no values found Else varJ13Comment = Left(varJ13Comment, Len(varJ13Comment) - 1) 'delete the last carriage return (Next Line) End If Worksheets(strMainSheet).Cells(13, 10).AddComment (varJ13Comment) 'add new comment End Function -- Hope that this is want you wan if so please rate my work. Thanks Trev B "N1KO" wrote: Hi Trev, None of the cells with in the range are merged. The values in each of the cells are different, there isn't any comments in the cells. I'd like the 1 comment box for cell J13 on the main sheet to have all values from the range of 54 cells (B74:J81) from the other sheet. (I've managed to get rid of a row) I don't need to know where the cell values have came from. The only way i need the info seperated is by row. (New row from the range = new line in the comment like below). Row 76 Row 77 Row 78 Row 79 Row 80 Row 81 Cell J3 is on the main sheet - Sheet's name Cell J13 is on the main sheet - Cell where the comment needs to be inserted. The range of cells are on the sheet who's name is held in cell J3 The name in J3 on the main sheet is the name of the sheet where the range (B76:J81) is held. So that is where values will be coming from. If you need anything else please say. This is hugely appreciated. N1KO "Trev B" wrote: Hi, Need a little more information 1) Are cells B74:J80 merged cells? 2) Does each cell between B74:J80 have a different comment in it? 3) Do you want one cell comment (J13) to to contain the comments of 70 cells if so what do you want the information seperated by ie commer or hyphen or full stop do you wnat to know which cell the comment came from? 4) does the sheet name J3 is it on the sheet where the new comment is going or is it on the sheet where the comments are comming from? On recept of information we should be able to set up some kind of module that will do the job for you. -- Trev B "N1KO" wrote: Hi, I need to make put a comment box on a cell but the actual comment comes from a range of cells on a different sheet. The range will always be ("B74:J80") but the sheet name will change. The sheet name will always be in cell J3 of the sheet. So i need the comment box to be in cell J13 with the comment being from B74:J80 on another sheet, the sheets name will be in cell J3. Will I need to create a variable for the range? Any help on this will be hugely appreciated. N1KO |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comment Box to include a range of cells
Hi N1KO,
Glad it works how you want if you change the vbcrlf to what you want then you should be 100% happy. If still not sure then send me what you want it to look like. Yes you can alter whatever you want as long as you understand what is happening. I Suggest you take time and understand each line of the code. Try pressing (F8) button and step through each line and see for yourself. Hope you have now got the coding bug and get it to do whatever you want. Need help Just ask. Thanks Trev B "N1KO" wrote: Trev thank you very much. It works almost but seems to put every cell on a new line, If this is the only way it can happen then that's fine with me. Can i adapt this to include other cells on the sheet easily? These ones being merged...... Appreciate the work you've done. Thanks Nick "Trev B" wrote: Hi N1KO, Please add a macro button to the sheet you want the comment addng then add the following module:- Public Function UpdateJ13CommentBox() Dim strMainSheet As String 'name of mainsheet Dim strSubSheet As String 'name of sub sheet Dim varJ13Comment As Variant 'contains values of comments Dim varNewValue As Variant 'Contains current value of cell Dim lngCol As Long 'current col no Dim lngRow As Long 'current row no strMainSheet = ActiveSheet.Name 'get name of main sheet strSubSheet = Worksheets(strMainSheet).Cells(3, 10).Value 'get name of sheet from cell "J3" For lngRow = 76 To 81 'for each row between 76 to 81 For lngCol = 2 To 10 'for each column between B & J (2 to 10) varNewValue = Worksheets(strSubSheet).Cells(lngRow, lngCol).Value If Len(varNewValue) 0 Then 'do not add if cell is empty varJ13Comment = varJ13Comment & varNewValue & vbCrLf 'update comment with extra cell (vbcrlf = next line) End If Next lngCol 'get next column Next lngRow 'get next row Worksheets(strMainSheet).Cells(13, 10).ClearComments 'clear existing comment if any If Len(varJ13Comment) < 1 Then 'check if no values found varJ13Comment = "Sheet '" & strSubSheet _ & "' Cells B76 to J81 contain no values" 'tell user no values found Else varJ13Comment = Left(varJ13Comment, Len(varJ13Comment) - 1) 'delete the last carriage return (Next Line) End If Worksheets(strMainSheet).Cells(13, 10).AddComment (varJ13Comment) 'add new comment End Function -- Hope that this is want you wan if so please rate my work. Thanks Trev B "N1KO" wrote: Hi Trev, None of the cells with in the range are merged. The values in each of the cells are different, there isn't any comments in the cells. I'd like the 1 comment box for cell J13 on the main sheet to have all values from the range of 54 cells (B74:J81) from the other sheet. (I've managed to get rid of a row) I don't need to know where the cell values have came from. The only way i need the info seperated is by row. (New row from the range = new line in the comment like below). Row 76 Row 77 Row 78 Row 79 Row 80 Row 81 Cell J3 is on the main sheet - Sheet's name Cell J13 is on the main sheet - Cell where the comment needs to be inserted. The range of cells are on the sheet who's name is held in cell J3 The name in J3 on the main sheet is the name of the sheet where the range (B76:J81) is held. So that is where values will be coming from. If you need anything else please say. This is hugely appreciated. N1KO "Trev B" wrote: Hi, Need a little more information 1) Are cells B74:J80 merged cells? 2) Does each cell between B74:J80 have a different comment in it? 3) Do you want one cell comment (J13) to to contain the comments of 70 cells if so what do you want the information seperated by ie commer or hyphen or full stop do you wnat to know which cell the comment came from? 4) does the sheet name J3 is it on the sheet where the new comment is going or is it on the sheet where the comments are comming from? On recept of information we should be able to set up some kind of module that will do the job for you. -- Trev B "N1KO" wrote: Hi, I need to make put a comment box on a cell but the actual comment comes from a range of cells on a different sheet. The range will always be ("B74:J80") but the sheet name will change. The sheet name will always be in cell J3 of the sheet. So i need the comment box to be in cell J13 with the comment being from B74:J80 on another sheet, the sheets name will be in cell J3. Will I need to create a variable for the range? Any help on this will be hugely appreciated. N1KO |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comment Box to include a range of cells
Thanks for this,
Have it working now, and got it to work for other cells. All seems kosher. Cheers "Trev B" wrote: Hi N1KO, Glad it works how you want if you change the vbcrlf to what you want then you should be 100% happy. If still not sure then send me what you want it to look like. Yes you can alter whatever you want as long as you understand what is happening. I Suggest you take time and understand each line of the code. Try pressing (F8) button and step through each line and see for yourself. Hope you have now got the coding bug and get it to do whatever you want. Need help Just ask. Thanks Trev B "N1KO" wrote: Trev thank you very much. It works almost but seems to put every cell on a new line, If this is the only way it can happen then that's fine with me. Can i adapt this to include other cells on the sheet easily? These ones being merged...... Appreciate the work you've done. Thanks Nick "Trev B" wrote: Hi N1KO, Please add a macro button to the sheet you want the comment addng then add the following module:- Public Function UpdateJ13CommentBox() Dim strMainSheet As String 'name of mainsheet Dim strSubSheet As String 'name of sub sheet Dim varJ13Comment As Variant 'contains values of comments Dim varNewValue As Variant 'Contains current value of cell Dim lngCol As Long 'current col no Dim lngRow As Long 'current row no strMainSheet = ActiveSheet.Name 'get name of main sheet strSubSheet = Worksheets(strMainSheet).Cells(3, 10).Value 'get name of sheet from cell "J3" For lngRow = 76 To 81 'for each row between 76 to 81 For lngCol = 2 To 10 'for each column between B & J (2 to 10) varNewValue = Worksheets(strSubSheet).Cells(lngRow, lngCol).Value If Len(varNewValue) 0 Then 'do not add if cell is empty varJ13Comment = varJ13Comment & varNewValue & vbCrLf 'update comment with extra cell (vbcrlf = next line) End If Next lngCol 'get next column Next lngRow 'get next row Worksheets(strMainSheet).Cells(13, 10).ClearComments 'clear existing comment if any If Len(varJ13Comment) < 1 Then 'check if no values found varJ13Comment = "Sheet '" & strSubSheet _ & "' Cells B76 to J81 contain no values" 'tell user no values found Else varJ13Comment = Left(varJ13Comment, Len(varJ13Comment) - 1) 'delete the last carriage return (Next Line) End If Worksheets(strMainSheet).Cells(13, 10).AddComment (varJ13Comment) 'add new comment End Function -- Hope that this is want you wan if so please rate my work. Thanks Trev B "N1KO" wrote: Hi Trev, None of the cells with in the range are merged. The values in each of the cells are different, there isn't any comments in the cells. I'd like the 1 comment box for cell J13 on the main sheet to have all values from the range of 54 cells (B74:J81) from the other sheet. (I've managed to get rid of a row) I don't need to know where the cell values have came from. The only way i need the info seperated is by row. (New row from the range = new line in the comment like below). Row 76 Row 77 Row 78 Row 79 Row 80 Row 81 Cell J3 is on the main sheet - Sheet's name Cell J13 is on the main sheet - Cell where the comment needs to be inserted. The range of cells are on the sheet who's name is held in cell J3 The name in J3 on the main sheet is the name of the sheet where the range (B76:J81) is held. So that is where values will be coming from. If you need anything else please say. This is hugely appreciated. N1KO "Trev B" wrote: Hi, Need a little more information 1) Are cells B74:J80 merged cells? 2) Does each cell between B74:J80 have a different comment in it? 3) Do you want one cell comment (J13) to to contain the comments of 70 cells if so what do you want the information seperated by ie commer or hyphen or full stop do you wnat to know which cell the comment came from? 4) does the sheet name J3 is it on the sheet where the new comment is going or is it on the sheet where the comments are comming from? On recept of information we should be able to set up some kind of module that will do the job for you. -- Trev B "N1KO" wrote: Hi, I need to make put a comment box on a cell but the actual comment comes from a range of cells on a different sheet. The range will always be ("B74:J80") but the sheet name will change. The sheet name will always be in cell J3 of the sheet. So i need the comment box to be in cell J13 with the comment being from B74:J80 on another sheet, the sheets name will be in cell J3. Will I need to create a variable for the range? Any help on this will be hugely appreciated. N1KO |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum of a range of cells that include cells using LOOKUP | Excel Worksheet Functions | |||
Do the values in a range of cells include all members of a set? | Excel Discussion (Misc queries) | |||
How do I include only visible cells in a range? | Excel Worksheet Functions | |||
sum a range of cells that include an error | Excel Discussion (Misc queries) | |||
Moving between a cell that is selected to include a range of cells in that row | Excel Programming |