|
|
|
date: Wed, 8 Oct 2008 08:59:23 -0700,
group: microsoft.public.excel.worksheet.functions
back
RE: Excel cell to display summary of columns
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.
"Danielle" wrote:
> Exactly.
>
> "Thomas [PBD]" wrote:
>
> > 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.
> >
> >
> > "Danielle" wrote:
> >
> > > 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.
> > >
date: Wed, 8 Oct 2008 09:47:01 -0700
author: Thomas [PBD]
RE: Excel cell to display summary of columns
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
"Thomas [PBD]" wrote:
> 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.
>
>
> "Danielle" wrote:
>
> > Exactly.
> >
> > "Thomas [PBD]" wrote:
> >
> > > 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.
> > >
> > >
> > > "Danielle" wrote:
> > >
> > > > 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.
> > > >
date: Wed, 8 Oct 2008 10:57:15 -0700
author: Danielle
RE: Excel cell to display summary of columns
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 A1:D8 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.
"Danielle" wrote:
> 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
>
> "Thomas [PBD]" wrote:
>
> > 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.
> >
> >
> > "Danielle" wrote:
> >
> > > Exactly.
> > >
> > > "Thomas [PBD]" wrote:
> > >
> > > > 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.
> > > >
> > > >
> > > > "Danielle" wrote:
> > > >
> > > > > 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.
> > > > >
date: Wed, 8 Oct 2008 11:16:01 -0700
author: Thomas [PBD]
|
|
