Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Excel VBA 24-Hour Trainer 2nd edition
This is the forum to discuss the Wrox book Excel VBA 24-Hour Trainer 2nd Edition by Tom Urtis; ISBN: 978-1-118-99137-4
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Excel VBA 24-Hour Trainer 2nd edition section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 25th, 2016, 11:22 AM
Registered User
Points: 8, Level: 1
Points: 8, Level: 1 Points: 8, Level: 1 Points: 8, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2016
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Excel VBA Data Validation

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.
Reply With Quote
  #2 (permalink)  
Old March 26th, 2016, 12:44 PM
Wrox Author
Points: 213, Level: 4
Points: 213, Level: 4 Points: 213, Level: 4 Points: 213, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2015
Location: San Francisco, California, USA
Posts: 34
Thanks: 0
Thanked 5 Times in 5 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old March 27th, 2016, 03:16 PM
Registered User
Points: 8, Level: 1
Points: 8, Level: 1 Points: 8, Level: 1 Points: 8, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2016
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Excel VBA Data Validaqtion

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
Reply With Quote
  #4 (permalink)  
Old March 27th, 2016, 07:44 PM
Wrox Author
Points: 213, Level: 4
Points: 213, Level: 4 Points: 213, Level: 4 Points: 213, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2015
Location: San Francisco, California, USA
Posts: 34
Thanks: 0
Thanked 5 Times in 5 Posts
Default

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.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Draw in VBA using excel data generalgort Excel VBA 2 May 13th, 2010 12:21 PM
Excel vba: data processing snowman.hk Excel VBA 1 November 6th, 2009 04:56 AM
Need help in VBA excel on validation deepunair84296 Excel VBA 2 July 11th, 2008 04:23 PM
Converting excel data to Access using excel VBA ShaileshShinde VB Databases Basics 1 April 26th, 2006 07:57 AM
Copy data from webpage to excel using vba kg8299 Excel VBA 0 April 5th, 2006 03:51 AM



All times are GMT -4. The time now is 12:23 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.