Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA 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
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 19th, 2006, 02:47 PM
Authorized User
Join Date: Apr 2006
Location: Valdosta, georgia, USA.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default works

First - thanks!!! I'm just learning VBA and you've been a great help.

K, i've got it working using parts of the code you posted. I already had figured out the "name" situation so i left that part of your code out. Now my problem is getting the validation parts to work properly. In the form, i've got different fields that are numeric only, fields that are alpha-numeric and some that are alpha only. How do i get the code to check the data before the output. Here's my current coding on the form itself:

Option Explicit
Option Base 1
Private Data() As Variant
Private RangeData As range
Private Number As Integer
Private Text As String

Public Cancelled As Boolean

Private Sub cmdNewExp_Click()

End Sub
Private Sub cmdClear_Click()
  Cancelled = True
End Sub

Private Sub ExpForm_Initialize()

End Sub

Private Sub cmdSubmit_Click()
  Cancelled = False
End Sub

Private Sub ExpForm_QueryClose(Cancel As Integer, _
  CloseMode As Integer)

  If (CloseMode = vbFormControlMenu) Then
    Cancel = True
  End If
End Sub

Private Sub SaveRecord()
'Add new record at bottom of database
  Dim RowCount As Integer
  Dim stock_num As String
  Dim Af_noun_tx As String

  With range("expenditures")
    'Add extra row to name Database
    RowCount = .Rows.Count + 1
    .Resize(RowCount).Name = "expenditures"
    Set RangeData = .Rows(RowCount)

  End With
ReDim Data(1 To 1, 1 To 8)

   'Copy values from ExpenditureForm controls to Data array
        Data(1, 1) = txtSEQ.Value
        Data(1, 2) = cboOrgShp.Value
        [u]Data(1, 3) = txtNsn.Value</u>
        'Data(1, 4) = Application.WorksheetFunction.VLookup(Data(1, 3), Range("STOCK_NOUN_CROSSREF"), 2, False)
        Data(1, 5) = txtDoc.Value
        Data(1, 6) = txtLot.Value
        Data(1, 7) = txtQty.Value
        Data(1, 8) = txtCatCode.Value

   'Assign Data array values to current record in Database
   RangeData.Value = Data

Call Unload(ExpForm)

End Sub

Sub ValidStock()
Dim oValid As Validation
Set oValid = Selection.Validation
With oValid
    .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1", Formula2:="9999999999999"
    .ShowInput = False
    .ShowError = True
    .ErrorTitle = "error"
    .ErrorMessage = "Please enter a valid Number"
End With

End Sub

Red items are numeric only, green items are alpha numeric, blue items are alpha only. The underlined item HAS to be 13-15 characters and the bold item HAS to be 14 characters. I don't know where to put the CALL function for the validation check. And, I'm pretty sure it won't do everything I need it to. I may have to have several different validation checks inside nested loops. Finally, the DATA (1, 4) line doesn't work. I get an error stating "unable to get the vlookup property of the worksheetfunction class". This whole project has turned out to be way over my head. I greatly appreciate the help.
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
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
how cache works Maxxim BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 5 October 30th, 2007 01:09 PM
how does this sample works? dsmportal ASP.NET 2.0 Professional 0 August 3rd, 2006 08:15 PM
DB works.....then it doesn't shyster1977 Classic ASP Databases 2 March 9th, 2005 02:57 PM

All times are GMT -4. The time now is 09:39 AM.

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