Comment Box to include a range of cells

[N1KO]

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

 

[Rick Rothstein]

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)?

 

[Trev B]

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.

 

[N1KO]

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

 

[N1KO]

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]

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]

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]

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]

Thanks for this,

Have it working now, and got it to work for other cells.

All seems kosher.

Cheers