|
Subject:
|
works
|
|
Posted By:
|
bryan.lugo
|
Post Date:
|
4/19/2006 2:47:28 PM
|
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
Me.Hide
End Sub
Private Sub ExpForm_Initialize()
End Sub
Private Sub cmdSubmit_Click()
Cancelled = False
SaveRecord
End Sub
Private Sub ExpForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If (CloseMode = vbFormControlMenu) Then
Cancel = True
Beep
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
range("expenditures").Select
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
Data(1, 3) = txtNsn.Value
'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
.Delete
.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.
|
|
