Required Fields

[hinesgg]

I have a workbook containing a sheet requiring data in specific fields before the user can close the workbook. However, I need for users to be able to copy the sheet to create new ones and have Excel stop the close if they don't enter data in the same cells on the new sheet.

Right now the code I'm using works, but if I copy the sheet, Excel does not stop the close if data is missing is on the new sheet.

Here's the code I'm using now. What can I use instead of specifically naming the sheet so Excel will recognize the code when the sheet is copied? Also, is there something (wild card operator to represent the sheet name) I can add to the pop-up message to tell the user which tab/sheet the data is missing from?

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Worksheets("RequestForm-Template").Range("D4").Value = "" Then
MsgBox "You must fill in D4."
Cancel = True
End If

If Worksheets("RequestForm-Template").Range("D6").Value = "" Then
MsgBox "You must fill in D6."
Cancel = True
End If
If Worksheets("RequestForm-Template").Range("D8").Value = "" Then
MsgBox "You must fill in D8."
Cancel = True
End If
If Worksheets("RequestForm-Template").Range("D11").Value = "" Then
MsgBox "You must fill in D11."
Cancel = True
End If
If Worksheets("RequestForm-Template").Range("D13").Value = "" Then
MsgBox "You must fill in D13."
Cancel = True
End If
If Worksheets("RequestForm-Template").Range("D16").Value = "" Then
MsgBox "You must fill in D16."
Cancel = True
End If

If Worksheets("RequestForm-Template").Range("D18").Value = "" Then
MsgBox "You must fill in D18."
Cancel = True
End If
If Worksheets("RequestForm-Template").Range("D20").Value = "" Then
MsgBox "You must fill in D20."
Cancel = True
End If
If Worksheets("RequestForm-Template").Range("D22").Value = "" Then
MsgBox "You must fill in D22."
Cancel = True
End If
If Worksheets("RequestForm-Template").Range("D24").Value = "" Then
MsgBox "You must fill in D24."
Cancel = True
End If
If Worksheets("RequestForm-Template").Range("D26").Value = "" Then
MsgBox "You must fill in D26."
Cancel = True
End If
If Worksheets("RequestForm-Template").Range("D30").Value = "" Then
MsgBox "You must fill in D30."
Cancel = True
End If

If Worksheets("RequestForm-Template").Range("D32").Value = "" Then
MsgBox "You must fill in D32."
Cancel = True
End If
If Worksheets("RequestForm-Template").Range("D34").Value = "" Then
MsgBox "You must fill in D34."
Cancel = True
End If
If Worksheets("RequestForm-Template").Range("D36").Value = "" Then
MsgBox "You must fill in D36."
Cancel = True
End If

If Worksheets("RequestForm-Template").Range("D38").Value = "" Then
MsgBox "You must fill in D38."
Cancel = True
End If

If Worksheets("RequestForm-Template").Range("D40").Value = "" Then
MsgBox "You must fill in D40."
Cancel = True
End If
If Worksheets("RequestForm-Template").Range("D42").Value = "" Then
MsgBox "You must fill in D42."
Cancel = True
End If

If Worksheets("RequestForm-Template").Range("D45").Value = "" Then
MsgBox "You must fill in D45."
Cancel = True
End If

If Worksheets("RequestForm-Template").Range("D47").Value = "" Then
MsgBox "You must fill in D47."
Cancel = True
End If

If Worksheets("RequestForm-Template").Range("D50").Value = "" Then
MsgBox "You must fill in D50."
Cancel = True
End If

If Worksheets("RequestForm-Template").Range("D52").Value = "" Then
MsgBox "You must fill in D52."
Cancel = True
End If

End Sub

 

[Special-K99]

Am no VBA expert so I don't know if this works, try it.

Store the sheet name in a cell on the sheet,e.g. in Z1
Then change all the Worksheets references to

Worksheets(Range("Z1")).Range("D4")

Not sure about the wild card request.

 

[hinesgg]

Thank you, but unfortunately, this didn't work. It actually broke my "don't close" code.

 

[Special-K99]

This should work though I only tested in an ordinary sub()

shtnm=Range("Z1")
Worksheets(shtnm).Range("D4")