Interactive Table on Excel Using VB

[scottrssll]

Hello, Everyone!

I have an issue that I desperately need help with. My boss told me to do this a while ago, and I have been struggling tremendously...

I need to make an interactive spreadsheet (table) of employees. Each row will have a different employee, while each column will have a different type of technical error. I want to be able to click on any cell and input data when an employee makes an error and see any past errors made or reset them (no more than 3 allowed). Finally, when I look at the table, it should only show a color for each cell representing the number of errors. Right now my table says "Good" or "Write Up," and I don't like this.

When trying to do this myself, I started by referencing three cells on sheet 2 for each cell on sheet 1 (the table) and using the formula "=IF(AND(ISTEXT(Sheet2!B1),ISTEXT(Sheet2!B2),ISTEXT(Sheet2!B3)),"WRITE UP","GOOD")" Then I tried to write code (which I'm horrible at) that would log input data in the appropriate reference cells with an input box when you click on a cell in the table. It isn't going well.

Here's an example of my table:

[TABLE="width: 527"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Safety[/TD]
[TD]Breaks[/TD]
[TD]Phone[/TD]
[TD]FTC[/TD]
[TD]SOP[/TD]
[TD]Other[/TD]
[/TR]
[TR]
[TD]BOB JONES[/TD]
[TD]WRITE UP[/TD]
[TD]GOOD[/TD]
[TD]GOOD[/TD]
[TD]GOOD[/TD]
[TD]WRITE UP[/TD]
[TD]GOOD[/TD]
[/TR]
[TR]
[TD]KIM CARDIO[/TD]
[TD]GOOD[/TD]
[TD]GOOD[/TD]
[TD]WRITE UP[/TD]
[TD]GOOD[/TD]
[TD]GOOD[/TD]
[TD]GOOD[/TD]
[/TR]
</tbody>[/TABLE]


Here's my horrible code so far:

 Option Explicit
Sub TransgressionLog_Click(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("D4")) Is Nothing Then
            If IsText(Sheet2!D1) And IsText(Sheet2!D2) And IsText(Sheet2!D3) Then
                Response = MsgBox("Has the counseling been completed?", vbYesNo, "Reset Counseling")
                If Response = msgboxresult.yes Then
                    Sub sbClearCellsOnlyData()
                    Range("Sheet2!d1:Sheet2!d3").ClearContents
                    End Sub
                Else
                    Response = MsgBox("Councelling Must Be Completed To Reset Strikes", vbOKOnly, "Reset Counseling")
                End If
            Else
               
Dim strTransgression$
Dim sw1 As Boolean


strTransgression = InputBox("Please Describe the Infraction", , "Transgression Report")
vbOKOnly


With Sheets("2")
lngLstRow = .UsedRange.Rows.Count + .UsedRange.Row
.Range("d1:d3" & lngLstRow).Value = strUserName
End With


MsgBox "Transgression Successfully Added!", _
vbExclamation + vbOKOnly, _
"Added!"
End If


myEnd:
End Sub
End If


        End If
    End If


End Sub

Someone please save me!!!

Thanks!

 

[mole999]

Not what your asking, as I don't know how to resolve that

My thought would be a separate sheet with a list, that can select names, areas that you are interested in, the date of entry, and the result, then use some complicated index match to return the top values and display in a table, but how that would work, not sure

sheet headings
[TABLE="width: 324"]
<colgroup><col width="45"><col width="72"><col width="132"><col width="75"></colgroup><tbody>[TR]
[TD="class: xl63, width: 45"]Date[/TD]
[TD="width: 72"]List Name[/TD]
[TD="width: 132"]List Technical Error[/TD]
[TD="width: 75"]List Result[/TD]
[/TR]
</tbody>[/TABLE]

 

[scottrssll]

Not what your asking, as I don't know how to resolve that

My thought would be a separate sheet with a list, that can select names, areas that you are interested in, the date of entry, and the result, then use some complicated index match to return the top values and display in a table, but how that would work, not sure

sheet headings
[TABLE="width: 324"]
<tbody>[TR]
[TD="class: xl63, width: 45"]Date[/TD]
[TD="width: 72"]List Name[/TD]
[TD="width: 132"]List Technical Error[/TD]
[TD="width: 75"]List Result[/TD]
[/TR]
</tbody>[/TABLE]

That's a great idea. I wonder if you could set the number of times a word occurs relative to a formula... like "if 'Bob Jones Saftey' > OR = 3 Then 'Write Up'"

 

[mole999]

I would be looking for a match with the name, the technical column, and the top three dates for each one (I still don't know how to)

so exact match name and task, by each date and the result stored

your table also needs to be displayed three lines per person so you can see the last three enteries

 

[scottrssll]

I would be looking for a match with the name, the technical column, and the top three dates for each one (I still don't know how to)

so exact match name and task, by each date and the result stored

your table also needs to be displayed three lines per person so you can see the last three enteries

Yeah, or maybe I could set up a userform in VBA that assigns the employees ID Numbers... This is a great help. Though I'm still somewhat lost, lol. I'm looking at tutorials on user forms right now.

 

[mole999]

you could just create an additional list to reutilise ID numbers with a vlookup
Bob Jones 0001, thought he effect would be the same

 

[scottrssll]

you could just create an additional list to reutilise ID numbers with a vlookup
Bob Jones 0001, thought he effect would be the same

Yeah, that's a good idea. Vlookup is pretty easy to use.