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 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()
Private Sub cmdClear_Click()
Cancelled = True
Private Sub ExpForm_Initialize()
Private Sub cmdSubmit_Click()
Cancelled = False
Private Sub ExpForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If (CloseMode = vbFormControlMenu) Then
Cancel = True
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
'Add extra row to name Database
RowCount = .Rows.Count + 1
.Resize(RowCount).Name = "expenditures"
Set RangeData = .Rows(RowCount)
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
Dim oValid As Validation
Set oValid = Selection.Validation
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1", Formula2:="9999999999999"
.ShowInput = False
.ShowError = True
.ErrorTitle = "error"
.ErrorMessage = "Please enter a valid Number"
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.