scottrssll
New Member
- Joined
- Jan 8, 2016
- Messages
- 11
Hello,
I'm currently building a disciplinary report log for work. The design of the log is simple; I have a table in Sheet 1 with rows corresponding to employee names. Each column represents a general category of possible infractions (e.g. safety; cell phone usage; failure to comply with procedures). I want to be able to click a cell to see and input specific infraction data (Date, Time, Supervisor), have it save to a database, and have the cell change format relative to the number of infractions (3 being most severe) with a message that pops up after 3 infractions asking if the employee was counselled and if that cell should be reset.
So far, I have this formula for the cells, which I don't like, to set the reference cells for the data and to offer a quick status message. I would prefer if the cells changed from Green, to Yellow, to Orange, to Red:
=IF(AND(ISTEXT(Sheet2!A1),ISTEXT(Sheet2!A2),ISTEXT(Sheet2!A3)),"WRITE UP","GOOD")
For the inputbox and msgbox aspect, I've been using Visual Basic, and I'm struggling greatly. The Code won't work at all and is missing the function to display the current infraction data. Furthermore, if I continue with the code as it is I will have to write the entire program over and over for every cell. But, since the cells have to reference a specific employee and specific category of violation, I don't know of a better way using VisualBasic.
Please help! I'm desperate! Here's the code so far:
I'm currently building a disciplinary report log for work. The design of the log is simple; I have a table in Sheet 1 with rows corresponding to employee names. Each column represents a general category of possible infractions (e.g. safety; cell phone usage; failure to comply with procedures). I want to be able to click a cell to see and input specific infraction data (Date, Time, Supervisor), have it save to a database, and have the cell change format relative to the number of infractions (3 being most severe) with a message that pops up after 3 infractions asking if the employee was counselled and if that cell should be reset.
So far, I have this formula for the cells, which I don't like, to set the reference cells for the data and to offer a quick status message. I would prefer if the cells changed from Green, to Yellow, to Orange, to Red:
=IF(AND(ISTEXT(Sheet2!A1),ISTEXT(Sheet2!A2),ISTEXT(Sheet2!A3)),"WRITE UP","GOOD")
For the inputbox and msgbox aspect, I've been using Visual Basic, and I'm struggling greatly. The Code won't work at all and is missing the function to display the current infraction data. Furthermore, if I continue with the code as it is I will have to write the entire program over and over for every cell. But, since the cells have to reference a specific employee and specific category of violation, I don't know of a better way using VisualBasic.
Please help! I'm desperate! Here's the code so far:
Code:
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
Sub cmdAddData_Click()
Dim strTransgression$
Dim sw1 As Boolean
strTransgression = InputBox("Please Describe the Infraction", , "Transgression Report")
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
[Code/]
