Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to disallow a zero value in an array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to disallow a zero value in an array
I neglected to show the sub I'm using to do the calculations.
Private Sub cmdCalc_Click() 'From: Dave Peterson '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").Ra nge("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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Disallow duplicates in different worksheets | Excel Discussion (Misc queries) | |||
Disallow CTRL+ALT+DEL | Excel Programming | |||
Disallow cell entries | Excel Discussion (Misc queries) | |||
Disallow Paste | Excel Programming | |||
Disallow sorting of data. | Excel Programming |