RE: Select Case and If...Then Statements in UserForm not Calculati

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I tried this and I am getting the same result. There are only numbers
entered into the textboxes anyway. Is the Trim function redundant? I
thought Val() returns all numbers to the left of anything that is not a
number except a decimal point. Should I use a label to show the results
instead? Not sure if there is a better way.

The weird thing is if you look at my example posted earlier, under the
'''''''''''''''''''quote price textbox value'''''''''''''' has the same
parameters as '''''''''''''calculated price text box''''''''''''''''', just
coded slightly different. Can you not embbed If...Then statements into
Select Case? The result in tbxQuotePrice is correct ($32.76), but
tbxCalculatedPrice is Incorrect ($47.00), why?

Forgive me if I do not understand, I am still learning, lol.


"Joel" wrote:

text boxes contain text and won't comparre to number values on the work***.
You havve to convert the text bak to a number using the Val() function. I
usually add a trim to remove spaces.

numval = trim(val((texttbox_data))

"RyanH" wrote:

I am building a product quoting system in VBA for my company that
manufactures outdoor plastic sign faces. I have a Userform with 6 text
boxes, 1 combobox, command button:

tbxDimHft1 = user enters Height of sign in feet
tbxDimHins1 = user enters Height of sign in inches
tbxDimWft1 = user enters Width of sign in feet
tbxDimWins1 = user enters Width of sign in inches
tbxCalculatedPrice = shows calculated price when command button is clicked
tbxQuotePrice = shows quote price when command button is clicked
cboMaterial = contains material for signs
cmbCalculate = command button to run calculation code

In my current code tbxCalculatedPrice and tbxQuotePrice should equal the
same, but for some reason they are not equalling each other when I click the
Calculate Button. For example, if I enter the following data:

tbxDimHft1 = 4
tbxDimHins1 = 0
tbxDimWft1 = 3
tbxDimWins1 = 0
cboMaterial = "Clear .150 High Impact Modified Acrylic"

Click the command button

tbxQuotePrice = 32.76
tbxCalculatePrice = 47.00

WHY???? Any help would be greatly appreciated, because I have looked at
this forever and cannot see why the calculations are not the same. THANKS!!

Note: PL509 = $9.36

Private Sub cmbCalculate_Click()

Dim Height As Long, Width As Long
Dim MaxL As Long, MinL As Long
Dim material As Long

Height = tbxDimHft1 + (tbxDimHins1 / 12)
Width = tbxDimWft1 + (tbxDimWins1 / 12)

MaxL = WorksheetFunction.Max(Height, Width)
MinL = WorksheetFunction.Min(Height, Width)

Sheets("Parts List").Activate

''''''''''''calculated price text box''''''''''''''''''''''''
Select Case cboMaterial.Text

Case "Clear .150 High Impact Modified Acrylic"

If MaxL <= 4 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL509", Range("A:D"), 4, False)
If 4 < MaxL <= 6 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL505", Range("A:D"), 4, False)

Case "Clear .150 Polycarbonate"

If MaxL <= 4 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL522", Range("A:D"), 4, False)
If 4 < MaxL <= 6 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL524", Range("A:D"), 4, False)

Case "White .150 High Impact Modified Acrylic"

If MaxL <= 4 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL510", Range("A:D"), 4, False)
If 4 < MaxL <= 6 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL506", Range("A:D"), 4, False)

Case "White .150 Polycarbonate"

If MaxL <= 4 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL521", Range("A:D"), 4, False)
If 4 < MaxL <= 6 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL529", Range("A:D"), 4, False)

Case "Clear .177 High Impact Modified Acrylic"

If MaxL <= 8 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL503", Range("A:D"), 4, False)

Case "White .177 High Impact Modified Acrylic"

If MaxL <= 8 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL504", Range("A:D"), 4, False)

End Select

tbxCalculatedPrice = material

'''''''''''''quote price textbox value''''''''''''''''''''
If MaxL <= 4 And cboMaterial.Text = "Clear .150 High Impact Modified
Acrylic" Then tbxQuotePrice = (MinL + 0.5) *
WorksheetFunction.VLookup("PL509", Range("A:D"), 4, False)

Sheets("Quote").Activate

End Sub
.

Quantcast