Excel cell to display summary of columns

[Danielle]

I have a Excel sheet that has 71 rows and 140 columns. In the colums are
training courses the employees have taken and in the rows are my employees. I
want to be able to click on the cell that has an employees name and a window
to pop up with the summary of the courses they have taken.....like the
comment box. I do not want to make a comment and have to edit each comment
when an employee completes a training course. I want the cell pop up window
with the employees name to automatically know and list the courses taken, to
pull it from the spreadsheet.
Any ideas? I am not sure if this is even possible.

 

[Thomas [PBD]]

Danielle,

In the data, are there X's or something where the names and courses meet?
For example:

Name Course 1 Course 2 Course 3
Name1 x
Name2 x x
Name3 x x

 

[Danielle]

Exactly.

Danielle,

In the data, are there X's or something where the names and courses meet?
For example:

Name Course 1 Course 2 Course 3
Name1 x
Name2 x x
Name3 x x

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.

I have a Excel sheet that has 71 rows and 140 columns. In the colums are
training courses the employees have taken and in the rows are my employees. I
want to be able to click on the cell that has an employees name and a window
to pop up with the summary of the courses they have taken.....like the
comment box. I do not want to make a comment and have to edit each comment
when an employee completes a training course. I want the cell pop up window
with the employees name to automatically know and list the courses taken, to
pull it from the spreadsheet.
Any ideas? I am not sure if this is even possible.

 

[Thomas [PBD]]

Alright Danielle, try this:

Sub Add_Course_Comment()
Dim CommentValue As String, sPayer As String, cell As Range
Dim wks1 As Worksheet
Dim sReportName As String

Dim i As Integer, iRow As Long
Dim c As Object

'' WHENEVER THERE'S A COLUMN OR ROW CHANGE TO THIS REPORT, THESE CONSTANTS
HAVE TO CHANGE
Const cStartRange = 2
Const cEndRange = 140
Const rStartRange = 2
Const rEndRange = 71

Set wks1 = ActiveSheet

With wks1

For iRow = rStartRange To rEndRange
If Range("A" & iRow).Value = "" Then
GoTo EndRows
Else

For i = cStartRange To cEndRange

If Range("A1").Cells(iRow, i).Value <> "" Then
sPayer = CStr(Range("A1").Cells(1, i).Value)
Else
sPayer = ""
End If
If sPayer <> "" Then
If i = cStartRange Then
CommentValue = sPayer
Else
If CommentValue = "" Then
CommentValue = sPayer
Else
CommentValue = CommentValue & Chr(10) & sPayer
End If
End If
End If
Next i
End If

If CommentValue = "" Then
CommentValue = "No Courses Taken"
End If

Range(Cells(iRow, 1), _
Cells(iRow, 1)).NoteText _
Text:=CommentValue
CommentValue = ""

Next iRow

EndRows:
End With

End Sub

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.

Exactly.

Danielle,

In the data, are there X's or something where the names and courses meet?
For example:

Name Course 1 Course 2 Course 3
Name1 x
Name2 x x
Name3 x x

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.

I have a Excel sheet that has 71 rows and 140 columns. In the colums are
training courses the employees have taken and in the rows are my employees. I
want to be able to click on the cell that has an employees name and a window
to pop up with the summary of the courses they have taken.....like the
comment box. I do not want to make a comment and have to edit each comment
when an employee completes a training course. I want the cell pop up window
with the employees name to automatically know and list the courses taken, to
pull it from the spreadsheet.
Any ideas? I am not sure if this is even possible.

 

[Danielle]

Thomas,

Thanks for the info. It works except it only lists my header in the pop up
window. My employees start in row A6 and my course info starts in B3. It
pulls the header info I have in B1 and places it in the pop up window.

Thanks,
Danielle

Alright Danielle, try this:

Sub Add_Course_Comment()
Dim CommentValue As String, sPayer As String, cell As Range
Dim wks1 As Worksheet
Dim sReportName As String

Dim i As Integer, iRow As Long
Dim c As Object

'' WHENEVER THERE'S A COLUMN OR ROW CHANGE TO THIS REPORT, THESE CONSTANTS
HAVE TO CHANGE
Const cStartRange = 2
Const cEndRange = 140
Const rStartRange = 2
Const rEndRange = 71

Set wks1 = ActiveSheet

With wks1

For iRow = rStartRange To rEndRange
If Range("A" & iRow).Value = "" Then
GoTo EndRows
Else

For i = cStartRange To cEndRange

If Range("A1").Cells(iRow, i).Value <> "" Then
sPayer = CStr(Range("A1").Cells(1, i).Value)
Else
sPayer = ""
End If
If sPayer <> "" Then
If i = cStartRange Then
CommentValue = sPayer
Else
If CommentValue = "" Then
CommentValue = sPayer
Else
CommentValue = CommentValue & Chr(10) & sPayer
End If
End If
End If
Next i
End If

If CommentValue = "" Then
CommentValue = "No Courses Taken"
End If

Range(Cells(iRow, 1), _
Cells(iRow, 1)).NoteText _
Text:=CommentValue
CommentValue = ""

Next iRow

EndRows:
End With

End Sub

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.

Exactly.

Danielle,

In the data, are there X's or something where the names and courses meet?
For example:

Name Course 1 Course 2 Course 3
Name1 x
Name2 x x
Name3 x x

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.


:

I have a Excel sheet that has 71 rows and 140 columns. In the colums are
training courses the employees have taken and in the rows are my employees. I
want to be able to click on the cell that has an employees name and a window
to pop up with the summary of the courses they have taken.....like the
comment box. I do not want to make a comment and have to edit each comment
when an employee completes a training course. I want the cell pop up window
with the employees name to automatically know and list the courses taken, to
pull it from the spreadsheet.
Any ideas? I am not sure if this is even possible.

 

[Thomas [PBD]]

Danielle,

Only semi-confused. I was under the impression that Row 1 were the headers
(course names) and starting in row 2 were the employees and the X's. I have
changed the coding, which seemed to have worked, however without knowing the
exact layout of the table, it is only a guess as to what you would want
displayed.

Line 12: Const rStartRange = 6
Determines the start row of your employees, I changed it to 6.

Line 27: sPayer = CStr(Range("A1").Cells(3, i).Value)
Returns the header/course name, I changed the Cells(3,i) part to pull from
row 3.

If you could make those changes in the code, please do so. If you wish,
please place an example of A18 for more clarification on what you would
like the comment to show.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.

Thomas,

Thanks for the info. It works except it only lists my header in the pop up
window. My employees start in row A6 and my course info starts in B3. It
pulls the header info I have in B1 and places it in the pop up window.

Thanks,
Danielle

Alright Danielle, try this:

Sub Add_Course_Comment()
Dim CommentValue As String, sPayer As String, cell As Range
Dim wks1 As Worksheet
Dim sReportName As String

Dim i As Integer, iRow As Long
Dim c As Object

'' WHENEVER THERE'S A COLUMN OR ROW CHANGE TO THIS REPORT, THESE CONSTANTS
HAVE TO CHANGE
Const cStartRange = 2
Const cEndRange = 140
Const rStartRange = 2
Const rEndRange = 71

Set wks1 = ActiveSheet

With wks1

For iRow = rStartRange To rEndRange
If Range("A" & iRow).Value = "" Then
GoTo EndRows
Else

For i = cStartRange To cEndRange

If Range("A1").Cells(iRow, i).Value <> "" Then
sPayer = CStr(Range("A1").Cells(1, i).Value)
Else
sPayer = ""
End If
If sPayer <> "" Then
If i = cStartRange Then
CommentValue = sPayer
Else
If CommentValue = "" Then
CommentValue = sPayer
Else
CommentValue = CommentValue & Chr(10) & sPayer
End If
End If
End If
Next i
End If

If CommentValue = "" Then
CommentValue = "No Courses Taken"
End If

Range(Cells(iRow, 1), _
Cells(iRow, 1)).NoteText _
Text:=CommentValue
CommentValue = ""

Next iRow

EndRows:
End With

End Sub

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.

Exactly.

:

Danielle,

In the data, are there X's or something where the names and courses meet?
For example:

Name Course 1 Course 2 Course 3
Name1 x
Name2 x x
Name3 x x

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.


:

I have a Excel sheet that has 71 rows and 140 columns. In the colums are
training courses the employees have taken and in the rows are my employees. I
want to be able to click on the cell that has an employees name and a window
to pop up with the summary of the courses they have taken.....like the
comment box. I do not want to make a comment and have to edit each comment
when an employee completes a training course. I want the cell pop up window
with the employees name to automatically know and list the courses taken, to
pull it from the spreadsheet.
Any ideas? I am not sure if this is even possible.