Thanks Ryan, that works great. Only one problem... I can't save the
worksheet empty (with the code in it) to give back to them to use. Oh what a
pickle!
You see, they enter information into the form and then it asks them to save
it as a different name because when they open it, it is read-only.
Al
"Ryan H" wrote:
Try pasting this entire code in your ThisWorkbook Before Save Event. Hope
this helps! If so, let me know, click "YES" below.
Option Explicit
Option Base 1
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim colMyRanges As Collection
Dim myArray(11) As String
Dim i As Long
Set colMyRanges = New Collection
With colMyRanges
.Add Range("E10")
.Add Range("E11")
.Add Range("E12")
.Add Range("E13")
.Add Range("E14")
.Add Range("E15")
.Add Range("E17")
.Add Range("O10")
.Add Range("O11")
.Add Range("A23")
.Add Range("A44")
End With
myArray(1) = "Department Name"
myArray(2) = "Address"
myArray(3) = "Contract Type"
myArray(4) = "Contract Document Type"
myArray(5) = "Contractor"
myArray(6) = "Contractor Address"
myArray(7) = "Project Title"
myArray(8) = "Contact Name"
myArray(9) = "Telephone Number"
myArray(10) = "Summary"
myArray(11) = "Request for Action Description"
For i = 1 To 11
If colMyRanges(i).Value = "" Then
Cancel = True
MsgBox "Please enter a " & myArray(i), vbExclamation
Exit For
End If
Next i
End Sub
--
Cheers,
Ryan
"Al" wrote:
Sorry, I forgot to mention that the Data Validation option is grayed out and
not available.
Thanks again,
Al
"Ryan H" wrote:
I think for what you are doing, you don't need VB. You can just use Data
Validation. Just Select the Cell you want to have data validated, put in
your parameters, message Title and Input message, and other settings if you
like. Then if the user doesn't meet your specifications the message box will
pop-up.
Hope this helps! If so, let me know, click "YES" below.
--
Cheers,
Ryan
"Al" wrote:
I have tried several different things to make this work and need professional
help. I have an Excel form with several fields (11 all together) that
require user input before it can be or should be allowed to be saved.
The first code I tried, and I was new at this with Excel, was to try
multiple actions like the following (which did not work) Oh, I should say
that it worked for the very first cell but none after that:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("E10").Value = "" Then
On Error Resume Next
Application.EnableEvents = False
Range("E10").Select
Application.EnableEvents = True
MsgBox ("You must enter a value for 'Department Name'")
End If
End Sub
Private Sub Worksheet_SelectionChange1(ByVal Target As Range)
If Range("E11").Value = "" Then
On Error Resume Next
Application.EnableEvents = False
Range("E11").Select
Application.EnableEvents = True
MsgBox ("You must enter a value for 'Address'")
End If
End Sub
Private Sub Worksheet_SelectionChange2(ByVal Target As Range)
If Range("E12").Value = "" Then
On Error Resume Next
Application.EnableEvents = False
Range("E12").Select
Application.EnableEvents = True
MsgBox ("You must enter a value for 'Contract Type'")
End If
End Sub
.
.
.
Then I realized it was not working because I was changing the very name of
the action that made it work "Worksheet_SelectionChange" so I tried nesting
the If statements, which also did not work:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Excel.ActiveCell = E10 Then
If Range("E10").Value = "" Then
On Error Resume Next
Application.EnableEvents = False
Range("E10").Select
Application.EnableEvents = True
MsgBox ("Please enter a Department Name")
End If
Else
If Excel.ActiveCell = E11 Then
If Range("E11").Value = "" Then
On Error Resume Next
Application.EnableEvents = False
Range("E11").Select
Application.EnableEvents = True
MsgBox ("Please enter an Address")
End If
Else
If Excel.ActiveCell = E12 Then
If Range("E12").Value = "" Then
On Error Resume Next
Application.EnableEvents = False
Range("E12").Select
Application.EnableEvents = True
MsgBox ("Please enter a 'Contract Type'")
End If
Else
If Excel.ActiveCell = E13 Then
If Range("E12").Value = "" Then
On Error Resume Next
Application.EnableEvents = False
Range("E12").Select
Application.EnableEvents = True
MsgBox ("Please enter a 'Contract Document Type'")
End If
Else
If Excel.ActiveCell = E14 Then
If Range("E12").Value = "" Then
On Error Resume Next
Application.EnableEvents = False
Range("E12").Select
Application.EnableEvents = True
MsgBox ("Please enter a Contractor")
End If
Else
If Excel.ActiveCell = E15 Then
If Range("E12").Value = "" Then
On Error Resume Next
Application.EnableEvents = False
Range("E12").Select
Application.EnableEvents = True
MsgBox ("Please enter a Contractor Address")
End If
.
.
.
Ok, so I thought I need to simplify this and went to a Select Case
statement, which of course also does not work:
Private Sub Contract_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Select Case Excel.Range
Case "E10" = ""
MsgBox "Please enter enter a Department Name"
Cancel = True 'cancels the save event
Case "E11" = ""
MsgBox "Please enter an Address"
Cancel = True 'cancels the save event
Case "E12" = ""
MsgBox "Please enter a 'Contract Type'"
Cancel = True 'cancels the save event
Case "E13" = ""
MsgBox "Please enter a 'Contract Document Type'"
Cancel = True 'cancels the save event
Case "E14" = ""
MsgBox "Please enter a Contractor"
Cancel = True 'cancels the save event
Case "E15" = ""
MsgBox "Please enter a Contractor Address"
Cancel = True 'cancels the save event
Case "E17" = ""
MsgBox "Please enter a Project Title"
Cancel = True 'cancels the save event
Case "O10" = ""
MsgBox "Please enter a Contact Name"
Cancel = True 'cancels the save event
Case "O11" = ""
MsgBox "Please enter a Telephone Number"
Cancel = True 'cancels the save event
Case "A23" = ""
MsgBox "Please enter a Summary"
Cancel = True 'cancels the save event
Case "A44" = ""
MsgBox "Please enter a Request for Action Description"
Cancel = True 'cancels the save event
End Select
End Sub
I am hoping that someone has seen or done something like this and can tell
me that this is possible. I really appreciate any help you can provide.
Thank you in advance,
Al