Macro for unfinished work.

[kman30us]

I'm new to Macros and this site so I hope this is the place - I need to create a macro from a spreadsheet that needs to be validated by Auditors.

This is my problem: Row K has a drop down box with three status values that need to be answered "Open, Closed, Incorrect" If Open or Incorrect has been chosen then they need to post their comments into Row L. Sometimes they forget to post their comment.

And they want me to create a macro that tells them at time of saving "that work is incomplete". Maybe activate the curser into that cell that needs to be validated. Any ideas that can tell them ROW L cell 23 "example" needs to be Validated. Don't leave blank.

Thanks

[CBrine]

Kman,
What kind of drop downs are you using? ActiveX control(From Control toolbox toolbar), Forms Control, or in cell validation?
If you are not already using validation, I would suggest this, since it will make the process possible.

[kman30us]

I am using the Validation. I just need something in my macro that tells them a "cell" needs a comment when they save it.

I can send file if anybody would like to take a look at it.

[CBrine]

Kman,
No need, I'm pretty sure I have a handle on what you are looking for.
Add this code to the Thisworkbook level of the vbe editor. Then change the
set ws = activeworkbook.sheets("SheetName")

to the name of your worksheet.


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim cell As Range, ws as worksheet
set ws = activeworkbook.sheets("SheetName")
For Each cell In ws.Range("K2", ws.Range("K" & ws.Rows.Count))
If cell <> "Closed" And cell.Offset(0, 1) = "" Then
MsgBox "Please Fill in Comments at Cell " & cell.Offset(0, 1).Address
Cancel = True
End If
Next cell
End Sub


HTH
Cal
PS-Each time they try to exit it will search for the invalided blanks and present them with a message box saying where the problem is.
(So if they have multiple blanks, they will get multiple messages). If everything is OK, it will exit normally.

[kman30us]

Thanks - I think this will help.

[CBrine]

kman,
I was just thinking you may want to add it to the Before_save Event as well. My code will prevent them from exiting without updating, but combined with this event, it will also stop a save from occurring, which was what you requested. You will need both.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
End Sub

HTH
Cal

[Charlize]

If you don' want that the auditors have a vinger that hurts (after clicking 65000 times the mousebutton) I suggest you try this one. Additional check on empty celvalue.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim cell As Range, ws As Worksheet
Set ws = ActiveWorkbook.Sheets(1)
For Each cell In ws.Range("K2", ws.Range("K" & ws.Rows.Count))
If cell <> "" Then
If cell <> "Closed" And cell.Offset(0, 1) = "" Then
MsgBox "Please Fill in Comments at Cell " & cell.Offset(0, 1).Address
Cancel = True
End If
End If
Next cell
End Sub
Charlize

[CBrine]

Charlize,
Good point, but since I only cycle through filled ranges based on column A's last filled row, it will only cycle through used data, not 65535 rows.

That would just be silly.:hi:

Cal

[CBrine]

Whoops, I guess I'm just silly. I forgot to put the end(xlup)



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim cell As Range, ws As worksheet
Set ws = activeworkbook.sheets("SheetName")
For Each cell In ws.Range("K2", ws.Range("K" & ws.Rows.Count).end(xlup))
If cell <> "Closed" And cell.Offset(0, 1) = "" Then
MsgBox "Please Fill in Comments at Cell " & cell.Offset(0, 1).Address
Cancel = True
End If
Next cell
End Sub

RE: See my tag line:rofl: . I know I wouldn't make a mistake like that, so why would I look for it.

[rbrhodes]

In the spirit of saving time, this addition will select the cell(s) requiring comments...til done. Saves writing down the address, selecting the cell, etc. Especially useful on multiple missing comments! <G>



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim cell As Range, ws As Worksheet
Set ws = ActiveWorkbook.Sheets("Sheet1")
For Each cell In ws.Range("K2", ws.Range("K" & ws.Rows.Count).End(xlUp))
If cell <> "Closed" And cell.Offset(0, 1) = "" Then

'Optional message
MsgBox "Please Fill in Comments" ' at Cell " & cell.Offset(0, 1).Address

'Select cell requiring comments
cell.Offset(0, 1).Select

Cancel = True
Exit Sub
End If
Next cell
End Sub


Cheers,

dr

[CBrine]

rbrhodes,
Great idea, but it will end up selecting just the last one without allowing any entry. Adding an input box would allow the entry of comments at that point. Maybe something like this.


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim cell As Range, ws As Worksheet
Set ws = ActiveWorkbook.Sheets("Sheet1")
For Each cell In ws.Range("K2", ws.Range("K" & ws.Rows.Count).End(xlUp))
If cell <> "Closed" And cell.Offset(0, 1) = "" Then

'Optional message
MsgBox "Please Fill in Comments" ' at Cell " & cell.Offset(0, 1).Address

'Select cell requiring comments
cell.Offset(0, 1).Select
activecell = inputbox("Please enter your comments")

Cancel = True
Exit Sub
End If
Next cell
End Sub


Cal

[rbrhodes]

Hi,

I checked before I posted and it worked for me:

For Each statement selected the _first_ cell found,
Offset(0,1) selected the cell beside it,
Cancel stopped the Close procedure,
Exit Sub stopped the macro and allowed the comment to be typed in.

I repeated this a few times and when the last comment was filled in, the file closed normally.

dr

[CBrine]

Ah,
Missed the exit sub you added in. Your right that would work.

Cal

[rbrhodes]

and I forgot to say "So would your version!" <G>

Cheers,

dr

[Charlize]

When you remove the exit sub it would still work. Maybe show the values of the current row in inputbox so you could put a decent comment.
'Select cell requiring comments
cell.Offset(0, 1).Select
ActiveCell = InputBox("a : " & cell.Offset(0, -10).Value & vbCrLf & _
"b : " & cell.Offset(0, -9).Value & vbCrLf & _
"..." & vbCrLf & "Please enter your comments")
Cancel = True

Charlize

[CBrine]

Three heads are better then one:yes

Hopefully Kman will come back and take a look at the evolved code.
:biggrin: