I'm desperately asking for some help with this. I really struggle with VBA...

[scottrssll]

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. Maybe incorporating pull down menus or some other object might be more effective.

Please help! I'm desperate! Here's the code so far:

[/COLOR]

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


[COLOR=#333333]                 With Sheets("2")[/COLOR]
[COLOR=#333333]                     lngLstRow = .UsedRange.Rows.Count + .UsedRange.Row[/COLOR]
[COLOR=#333333]                     .Range("d1:d3" & lngLstRow).Value = strUserName[/COLOR]
[COLOR=#333333]                 End With[/COLOR]
[COLOR=#333333]             MsgBox "Transgression Successfully Added!", _[/COLOR]
[COLOR=#333333]             vbExclamation + vbOKOnly, _[/COLOR]
[COLOR=#333333]             "Added!"[/COLOR]
[COLOR=#333333]          End If[/COLOR]
[COLOR=#333333]        End Sub[/COLOR]
[COLOR=#333333]      End If[/COLOR]


[COLOR=#333333]    End If[/COLOR]
[COLOR=#333333]   End If[/COLOR]


[COLOR=#333333]End Sub[/COLOR]


[COLOR=#333333][Code/][/COLOR]

 

[scottrssll]

Is there anyone out there who can help? I've been struggling with this for nearly a week...

 

[JackDanIce]

To me, the code doesn't make sense and I don't understand what you want to do. It's not clear or precise enough or the ask is too vague or unclear and people reading this board can't see your PC monitor.

If you read other posts, you'll note how people explain their problem in a way that makes it easier to provide an answer and/or including screenshots as well; the forum guide includes details on how to include screenshots.

Of course, someone may read this again, see my reply and think "Ha, I can solve this problem" and give you an answer, thus making my reply irrelevant (which I certainly hope so for your solution)

(Incidentally, I recall reading this a week ago and not responding to it, because I didn't understand it. I imagine others had a similar reaction.)

 

[Joe4]

Duplicate: http://www.mrexcel.com/forum/excel-...amming-simple-excel-database-visualbasic.html

Please do not post the same question multiple times. All clarifications, directly-related follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #12 here: Forum Rules).

A few other tips...

Regarding bumping:
Bumping your own questions too soon is generally not a good idea, and can actually be counter-productive.

There is a listing in the MrExcel menu called “Zero Reply Posts”. These are threads that have had no replies. Many of us users use this list to look for questions that have not had replies. So when you bump your own post, your question no longer appears in that list, so it stands a greater chance of being missed by many potential helpers.

Posting your question:
You got an awful lot there. When you ask for too much at once, people can often get overwhelmed and move on to other questions. It is usually best to break apart your issues into single questions/tasks people can help with instead of trying to do it all at once.

Also, for complex issues, it is often helpful to post screen prints of what your data looks like. You can do that using the tools mentioned here: http://www.mrexcel.com/forum/about-board/508133-attachments.html

Lastly, if you are looking for help with an entire project, your best bet may be to use the Consulting Services: MrExcel.com | Excel Resources | Excel Seminars | Excel Products


I will lock this thread and leave the original one open (per forum policy). If you want to break your request down into smaller, individual questions/tasks (so you are not asking for so much in a single thread), you can go ahead and do that.