Select Case and If...Then Statements in UserForm not Calculating

[RyanH]

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"), 4, False)
If 4 < MaxL <= 6 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL505", Range("A"), 4, False)

Case "Clear .150 Polycarbonate"

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

Case "White .150 High Impact Modified Acrylic"

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

Case "White .150 Polycarbonate"

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

Case "Clear .177 High Impact Modified Acrylic"

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

Case "White .177 High Impact Modified Acrylic"

If MaxL <= 8 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL504", Range("A"), 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"), 4, False)

Sheets("Quote").Activate

End Sub

 

[Joel]

text boxes contain text and won't comparre to number values on the worksheet.
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))

 

[Bob Phillips]

Not tested , but tere were syntax errors in the code

Private Sub cmbCalculate_Click()

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

With WorksheetFunction

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

MaxL = .Max(Height, Width)
MinL = .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) * _
.VLookup("PL509", Range("A"), 4, False)
ElseIf MaxL <= 6 Then
material = (MinL + 0.5) * _
.VLookup("PL505", Range("A"), 4, False)

Case "Clear .150 Polycarbonate"

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

Case "White .150 High Impact Modified Acrylic"

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

Case "White .150 Polycarbonate"

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

Case "Clear .177 High Impact Modified Acrylic"

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

Case "White .177 High Impact Modified Acrylic"

If MaxL <= 8 Then material = (MinL + 0.5) * _
.VLookup("PL504", Range("A"), 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) * _
.VLookup("PL509", Range("A"), 4, False)
End If

Sheets("Quote").Activate
End With

End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

Your problem is with the second If-Then test you are performing in the Case
block for your material...

If 4 < MaxL <= 6 Then ....

The problem with the above test is that it is written incorrectly... it will
evaluate **always** evaluate to True. The two operators you are using have
the same precedence, so they are evaluated left-to-right. No matter what
value is assigned to MaxL, 4<MaxL will be either True or False which, in the
VB world, is either -1 or 0, both of which are always going to be less than
6; hence, the above test will always be True and **always** overwrite any
value assigned to the materials variable by the previous statement. Since
you don't perform this same second test when making your assignment to
tbxQuotePrice near the end of your code, the values you think should be the
same will always be different when MaxL is equal to, or less than, 4 (at
least for the code snippet you have shown us). Anyway, the proper way to
write the above If-Then test is this...

If 4 < MaxL And MaxL <=6 Then ...

Now, there may be another problem with your code. You have declared each of
your variables as type Long, but some of your calculations will result in
floating point value being assigned to them. When that assignment takes
place, the fractional values will be lost and, I presume, cause your
calculations to be performed with values other than what you intended. For
example, this assignment...

Height = tbxDimHft1 + (tbxDimHins1 / 12)

will result in a whole number, not a floating point value, being assigned to
the Height variable. The same is true for the Width variable and any
subsequent calculations derived from them. Your materials variable, also
declared as Long is another place where any fractional values generated as
part of its assignment calculation will be lost. You might want to consider
declaring these (and perhaps other) variables as Double instead.

Rick

 

[RyanH]

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.

 

[RyanH]

IN WORKS!!!!!!!

I spent many hours of a day trying to solve this issue. Thanks so much, you
are the man! I have another posting about this so I will have to let them
know what solution is and of course give you credit!

Thanks Again!

 

[RyanH]

I knew it had something to do with that 4 < MaxL <= 6, but didn't know how to
fix it. Plus Rick Rothstein (MVP) suggested to declare my variables as
Double instead of Long so I could incorporate my decimal places.

Thanks for your help!