Excel VBA Data Validation

bigfilo · March 25th, 2016, 11:22 AM

New to the forum. Bought the book. I'm trying to include data validation into a macro that converts data in one worksheet to another worksheet. I've included the following code:

With Range("D1").Validation
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertInformation, _
Operator:=xlBetween, Formula1:="10250", Formula2:="10720"
.IgnoreBlank = True
.InputTitle = "Input Bank Account GL Number"
.ErrorTitle = "Input Bank Account GL Number"
.InputMessage = "Enter the bank account GL account number (e.g. 10710)."
.ErrorMessage = "You must enter 5 numbers."
.ShowInput = True
.ShowError = True
End With

But when I run the macro, nothing seems to happen with respect to this code. I don't get an input box asking me to enter the bank account GL #. And in fact, that cell/column is not filled in in the completed worksheet. It's as if it skips over the above code. What am I missing? Thanks.

Tom Urtis · March 26th, 2016, 12:44 PM

Thank you for buying the book.

Just to get things started, I revised your code with a few changes:
â¢ The Alert style is Stop (instead of Information as you had it), because you intend to only accept valid whole numbers within a range of 10250 to 10720.
â¢ I added the Delete statement in case you run the macro more than once upon cell D1 which, if it already has data validation assigned to it, cannot otherwise accept a data validation installation without its current data validation deleted. This results in a run time error otherwise, or, in long macros which yours might be, no such notice and that may have been why it looked to you that the macro was skipping over the portion you posted.
â¢ I don't know what you mean by "that cell/column is not filled in in the completed worksheet." The only cell you refer to is cell D1, so that is what I refer to in the below modification which does work on cell D1.
â¢ Regarding "I don't get an input box asking me to enter the bank account GL #.", I think you mean the Input Message (there are VBA objects called Input Boxes which I doubt you meant to refer to in this case). With the below modified code, when you select cell D1 you will see the instructions appear.
â¢ I rewrote your error message for a clearer meaning to the user.
â¢ You didn't ask about this, so just saying, because you allow for a rather lengthy possibility of whole numbers that can fall between and including 10250 and 10720, which is fine, you won't see a drop down list of allowable entries such as if your Operator was of the List type rather than the Between type. But the popup (Input) message and the error message will cover the instructions for that to the user.

Here's the modified code in a macro for you to test, which worked for me:

Code:

Sub Test()
With Range("D1").Validation
.Delete
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="10250", Formula2:="10720"
.IgnoreBlank = True
.InputTitle = "Input Bank Account GL Number"
.InputMessage = "Enter the bank account GL account number between 10250 and 10720 (e.g. 10710)."
.ErrorTitle = "Input Bank Account GL Number"
.ErrorMessage = "Please enter a 5-digit whole number" & Chr(10) & "between 10250 and 10720"
.ShowError = True
End With
End Sub

bigfilo · March 27th, 2016, 03:16 PM

Thanks for the help, Tom. I should have explained more about what I'm trying to do. I'm taking one worksheet (that we use to upload data to our bank) and reformatting it for upload to our GL. I realized after my first post that the vba code was inserting data validation into the worksheet, but what I'm trying to do is to validate data in the vba macro. So if someone inputs an invalid number, the macro will handle it. Here's what I came up with; I'm sure you can improve on it.

EnterBankAcct:
Range("D1").Value = Application.InputBox(Prompt:="Enter the bank account GL account number (10250, 10450, 10700, 10710 or 10720).", _
Title:="Input Bank Account GL Number", Type:=1)
bankinput = Range("D1")
If Not InStr(1, "|10250|10450|10700|10710|10720|", "|" & bankinput & "|") > 0 Then
MsgBox "GL bank account number must be 10250, 10450, 10700, 10710 or 10720."
GoTo EnterBankAcct
End If

Thanks,

Bigfilo

Tom Urtis · March 27th, 2016, 07:44 PM

Well, a couple things...

If you want, you can bypass VBA by manually setting up data validation for cell D1 with a drop-down list of those 5 allowable items 10250, 10450, 10700, 10710 or 10720. If you need a macro to do that, this is how:

Code:

Sub Test2()
With Range("D1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="10250,10450,10700,10710,10720"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Input Bank Account GL Number"
.InputMessage = "Enter the bank account from the" & Chr(10) & "drop-down list of numbers."
.ErrorTitle = "Invalid Bank Account GL Number!"
.ErrorMessage = "Please select a number" & Chr(10) & "from the drop-down list."
.ShowInput = True
.ShowError = True
End With
End Sub

Alternatively, if you want to monitor cell input as it happens, without native data validation, you need a Change event procedure in your worksheet module.

Step 1
Copy the below code.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$D$1" Or Target.Cells.Count > 1 Then Exit Sub
With Target.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop
.InputTitle = "Input Bank Account GL Number"
.InputMessage = "Enter the bank account from the" & Chr(10) & "drop-down list of numbers."
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$D$1" Or Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub

With Target

Select Case .Value
Case 10250, 10450, 10700, 10710, 10720
'I assume you want no action to take place if a
'valid number was entered, but here is where
'you could add code if you do want some action
'to take place.

Case Else
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
MsgBox _
"Please select a valid number" & vbCrLf & "from the drop-down list.", _
64, "Not a valid Account GL Number."
End Select
End With
End Sub

Step 2
Right-click the worksheet tab name where this code would go.

Step 3
From the right-click menu, select View Code.

Step 4
You will be taken to the VBE, where your cursor will be blinking in the worksheet module. Press Ctrl+V (or right-click your mouse and select Paste) to paste the code you copied from Step 1 into that worksheet module.

Step 5
Press Alt+Q to return to the worksheet.

Step 6
Your worksheet is now set up to monitor cell D1 for allowable entries.