How to disallow a zero value in an array

D

dan dungan

Hi,

Using Excel 2000 and Windows XP professional, I'm using the following
code to create a variable to use in another sub.

In a quoting application I'm designing, I'm collecting all the
quantities for a part number quote into an variable named "a". I have
a userform with a textbox and a command button. See the code I'm
using below.

I have two questions:

How can I save the variable for use in another sub?
How can I make sure the user doesn't click the command button until a
quantity is entered in the text box?

Thanks,

Dan
------------------------------------------------------------------
Private Sub cmdQuantity_Click()
Dim lr As Long, sh As Worksheet
Dim aQuant As Variant
Dim cont
Static a() As String
Dim lngArrayCounter As Integer

lngArrayCounter = -1
On Error Resume Next
lngArrayCounter = UBound(a)
On Error GoTo 0
If lngArrayCounter = -1 Then
ReDim Preserve a(0)
Else
ReDim Preserve a(lngArrayCounter + 1)
End If
a(UBound(a)) = txtQuantity_1.Text


cont = MsgBox("DO YOU NEED TO MAKE ANOTHER ENTRY?", vbYesNo,
"CONTINUE?")
If cont = vbYes Then
Me.txtQuantity_1.Text = ""
Me.txtQuantity_1.SetFocus
End If
End Sub
 
D

dan dungan

I neglected to show the sub I'm using to do the calculations.

Private Sub cmdCalc_Click()
'From: Dave Peterson <[email protected]>
'Date: Thu, 25 Feb 2010 21:06:05 -0600

Dim sCoreAdapShell As String 'it's all text, right?
Dim res As Variant 'could be an error

sCoreAdapShell = txtCore.Text & txtAdap_Config.Text & txtShell.Text

res = Application.vLookup(sCoreAdapShell, _

ThisWorkbook.Worksheets("tblPriceListCorePart").Range("tblPriceListCore"),
_
5, False)

If IsError(res) Then
'like #n/a in excel
Me.txtShellEntrySum.Text = "not found!"
Else
Me.txtShellEntrySum.Value = res * Me.txtCore_Multiplier.Value
End If

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top