making comments from vba

Microsoft Office access 2007 (full produ...
June 30, 2010 at 09:43:59
Specs: Windows XP
Hi all,
Just wanted to know if this is possible. I have a large excel file, some 1300 records in horizontal rows. Heres what I want to do. I want two columns, one with months, the other with 1st week, 2nd week etc. Im going to create a calendar-like overview page, with a cell for each week in a month etc. When I select say 'Sept' and '3rd week', I want vba to make a cell comment in that overview sheet on the cell that covers that week with the contents of column A.

Example: on sheet 2 i have a record. Column A is J Bloggs, Column B is anytown etc etc. Column E is month, column f is week.

When i select sept and 3rd week in their respective columns, I want it to create a comment in the cell on sheet1 representing the 3rd week in sept.

possible?
Many thanks


See More: making comments from vba

Report •


#1
June 30, 2010 at 10:21:37
re: possible?

Yes!

If you place this code in the Sheet2 module, it should get you close. It's mainly to show you that comments can indeed be added to cells on one sheet based on the selection of certain cells on another sheet.

As written, if places the comment in Sheet 1, Column A, in the same row as the cells selected in Sheet 2, Columns E & F, assuming those are the only 2 cells that are selected.

In order to place the comment in the cell representing a given month/week, we would need to know the connection between the selected cells on Sheet 2 and the "calendar cell" in Sheet 1. In other words, how would we find the corresponding cell on Sheet1 when "Sept" and "3rd week" are selected?

Try this code and see what you think.

Note: As written the code will replace an existing comment if one already exists in the cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
'Have 2 cells been selected?
 If Target.Cells.Count = 2 Then
'If yes, were the cells in Column E & F?
   If Target.Cells(1).Column = 5 And Target.Cells(2).Column = 6 Then
'If yes, add comment to cell on Sheet1 using data from Sheet2!A(same row)
     Sheets(1).Cells(Target.Row, 1).AddComment
     Sheets(1).Cells(Target.Row, 1).Comment.Text _
          Text:="user_name:" & Chr(10) & Sheets(2).Cells(Target.Row, 1)
    End If
 End If
End Sub


Report •

#2
June 30, 2010 at 12:25:58
Hi Derby,
Many thanks for your help. To throw a further cog in the mix, how is it possible to make a connection between the overview sheet cells and the sheet2 dropdowns?

Report •

#3
June 30, 2010 at 12:47:50
re: "the sheet2 dropdowns"

What sheet2 dropdowns? You didn't say anything about dropdowns in your OP.

Keep in mind that we can't see your spreadsheet from where we're sitting, nor can we read minds. You need to be very specific about the layout of your sheets and what you are trying to do.


Report •

Related Solutions

#4
June 30, 2010 at 13:00:59
Sorry derby,
by dropdowns I mean the Month and Week columns

Report •

#5
June 30, 2010 at 13:36:04
At the risk of repeating myself, I'll repeat myself...

Keep in mind that we can't see your spreadsheet from where we're sitting, nor can we read minds. You need to be very specific about the layout of your sheets and what you are trying to do.

I don't know how your drop downs work. I don't know what's in them, I don't where they are other than in "the Month and Week columns", I don't know what their purpose is, etc. etc. etc.

Even I knew how they were used in your spreadsheet, I can't tell you how to "make a connection between the overview sheet cells and the sheet2 dropdowns" because I don't know how the "overview sheet cells" are laid out or what's in them.

Do they contain the same words as the results of the drop downs so maybe a "Find" function can be used? Do they contain dates so maybe "Week 3 September" needs to be converted to a range of dates? etc. etc. etc.

These question aren't mant to be specific, they're just asked as a means to show you the type of information and detail we need before we can offer specific solutions.


Report •

#6
June 30, 2010 at 13:56:16
Derby,
Many apologies. I have four columns in sheet2. Name, Employer, Month and Week - the latter two will have data validation lists with each month and 1st week, 2 week etc etc. In Sheet1, I have a list of Departments in a business in Column A, horizontally across Row 1 in Sheet1, are months. Each months name stretches across 4 cells (representing each week). Under these four cells I want to be able to place the data in Sheet2, Column 'Name' when I select for example May and then 2nd week, I would like the code to make a comment, put it in the cell representing the relevant week, with the 'Name' of the selected row in Sheet1 Column 'Name;

I know its a pain trying to imagine these things, I can perhaps post a file if needed

Your help is very much appreciated.


Report •

#7
June 30, 2010 at 20:10:29
re: I know its a pain trying to imagine these things

We're getting close!

1 - I assume that when you say "make a comment, put it in the cell" you actually mean a Comment, such as what you would normally add via Insert...Comment in the spreadsheet. Is that correct? You don't mean that you simply want the Name to appear in a cell, as a piece of data...you really do want a Comment, right?

2 - I'm not sure how the Department column enters into this. Is it somehow related to where you want the Comment?

In other words, I guess I'm trying to find out if you want the Comment added directly to the cell that, for example, contains Week1 under January. Is that what you want?

Assuming my assumptions are right, try this code in the Sheet2 module.

Note: The validation lists for the Months and Weeks must match the contents of the Month and Week cells on Sheet1. Since the code uses the Find method, it will only find an exact match.

I suggest that you create your validation lists directly from the cells on Sheet1 so that there is no chance that the lists won't match the cell contents.

Let me know what you think....

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim theWeek, theMonth As String
Dim m, w As Range
On Error Resume Next
'Was Changed Made to Column 6 (F)?
 If Target.Column = 6 Then
'If yes, grab Week and Month
   theWeek = Target.Value
   theMonth = Target.Offset(0, -1)
'Find Month in Sheet 1, Row 1
  With Sheets(1).Rows(1)
     Set m = .Find(theMonth, _
               lookat:=xlWhole, LookIn:=xlValues)
  End With
'Find Week for that month
  With Sheets(1).Range(Sheets(1).Cells(2, m.Column), _
                       Sheets(1).Cells(2, m.Column + 3))
     Set w = .Find(theWeek, _
               lookat:=xlWhole, LookIn:=xlValues)
  End With
'Add comment to cell on Sheet1 using data from Sheet2!A(same row)
     Sheets(1).Range(w.Address).AddComment
     Sheets(1).Range(w.Address).Comment.Text _
          Text:=Sheets(2).Cells(Target.Row, 1).Value
 End If
End Sub


Report •

#8
July 1, 2010 at 07:12:36
Derby,
To answer your questions:
1. Yes, a comment like Insert > Comment.

and

2. When we talk about department, that will be selected from a dropdown (data validation again) in Sheet2 and will correspond to the same text in Column A, sheet1.

So when we select the name, department, month and week in sheet2, a comment appears in the relevant week cell alongside the department which will be going down the side.


Report •

#9
July 1, 2010 at 07:37:36
re: So when we select the name, department, month and week in sheet2, a comment appears in the relevant week cell alongside the department which will be going down the side.

Which is not something that you brought up earlier. Before we were selecting just a Month and a Week. Your words:

when I select for example May and then 2nd week I would like the code to...

Now you're telling us that you you will be selecting "the name, department, month and week in sheet2."

It makes it very hard to help you if you keep changing the information that we're trying to work with.

At this point I'm confused as to where (and why) you are selecting the department and name.


Report •

#10
July 1, 2010 at 07:45:02
Derby,
Is there a way in which I can perhaps send you a file so u can see what I mean, I know its very difficult, even for me, to explain ti all, but I really appreciate your help.

Report •

#11
July 1, 2010 at 07:52:37
Send the workbook to the email address I sent you via Private Message.

Make sure you include some instructions on how you are using the workbook. Where do you select data, in what order, etc.

Please do not share this email address with anyone.

I will take a look at it when I get chance, but it won't be before this evening, US-EST.


Report •


Ask Question