Textboxes not Calculating properly

[RyanH]

I'm not sure what is going on with my code but it is not calculating
properly. I am building a quoting system for plastic sign faces. I have a
UserForm where the user enters Height(feet & inches) and Width(feet & inches)
into textboxes. My code converts the Height & Width to inches. Then the
code determines which dimension is the largest. The user then chooses what
type of plastic the sign face is made out of and the code finds the part
number and returns the price for that particular size plastic. The code then
adds 6 inches to the smallest dimension and multiples that total by the
plastic price. For example, if I enter Height 4ft. - 0ins. & Width 3ft. -
0ins., and select "Clear .150 High Impact Modified Acrylic" from the combo
box, then click the Calculate Command Button I should get $32.76 instead I'm
getting $47.32 in my Label Caption. Note: Part Number PL509 = $9.36. Also
when I make a change to one of the Height textboxes ( ft. or ins.), then
click the Calculate button, nothing changes, why? Any help would greatly be
appreciated!!


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

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

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

Sheets("Parts List").Activate

Select Case cboMaterial

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

Case "White .150 Polycarbonate"
If W <= 4 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL521", Range("A"), 4, False)
If 4 < W <= 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

lblCalculatedPrice.Caption = material
lblCalculatedPrice.Caption = Format(lblCalculatedPrice.Caption, "$
#,###,###.00")
Sheets("Quote").Activate

 

[JLGWhiz]

Height = tbxDimHft1 + (tbxDimHins1 / 12) '?add .5 ft ?
Width = tbxDimWft1 + (tbxDimWins1 / 12)

If MaxL <= 4 Then material = (MinL + 0.5) '?add .5 ft?

MinL = WorksheetFunction.Min(Height, Width)

If these do what I think they do, then you are adding the extra half foot
twice. Would that give you the $47.32?

 

[JLGWhiz]

Never mind, I see that it is not adding 1/2 to Width and Height.

 

[RyanH]

I add the 6inches to the MinL in the If...Then Statements. Any idea its not
calculating correctly?

 

[JLGWhiz]

Ryan, I don't see any glaring code errors. I would suggest that you step
through the code line by line and make sure that the values you are getting
for your variables are the values you expect. If you don't know how to do
that in the VBA editor, you could use message boxes as in the example below:

Height = tbxDimHft1 + (tbxDimHins1 / 12)
Width = tbxDimWft1 + (tbxDimWins1 / 12)
MsgBox "Height - " & Height & ", Width - " & Width

MaxL = WorksheetFunction.Max(Height, Width)
MinL = WorksheetFunction.Min(Height, Width)
MsgBox "MaxL - " & MaxL & ", MinL - " & MinL

This will show you what you are actually calculating against your price list.

 

[RyanH]

I appreciate the response! I have checked each and every aspect of the code
in that way. I confirmed everything, but I think there is something wrong
with the variable "material". All calculations check out just fine but when
I assign the calculation to the variable "material" it jumps from 32.76 to
47.00. WEIRD!

Height = tbxDimHft1 + (tbxDimHins1 / 12) ''''CORRECT
Width = tbxDimWft1 + (tbxDimWins1 / 12) ''''CORRECT
MaxL = WorksheetFunction.Max(Height, Width) ''''CORRECT
MinL = WorksheetFunction.Min(Height, Width) ''''CORRECT
Select Case
Case "Clear .150 High Impact Modified Acrylic"
If MaxL <= 4 Then lblCalculatedPrice.Caption = (MinL + 0.5) *
WorksheetFunction.VLookup("PL509", Range("A"), 4, False) ''''CORRECT
End Select

lblCalculatedPrice.Caption = material ''''INCORRECT

Do I need number formatting with text boxes?
Validate textbox text?
Rearrange code lines in front of other code lines?

I really wish I could find something obvious here, but I only have some
experience with VBA. What do you think of the example?

 

[RyanH]

I made some minor changes to the code to make it easier to read and I took
your advice and checked every step of the code. I found where the problem
is, but do not know how to correct it and I hope maybe you could help.

Going back to an example where:
MaxL = 4
MinL = 3
cboMaterial = "Clear .150 High Impact Modified Acrylic"
Note: "PL509" = $9.36 and "PL505" = $13.52

If I deactivate the 4 < MaxL <= 6 If...Then Statement, tbxCalculatedPrice
shows the correct result ($33.00), because it uses the MaxL <= 4 If...Then
Statement. If I activate both If...Then Statements under Case "Clear .150
High Impact Modified Acrylic", tbxCalculatedPrice shows the result of the 4 <
MaxL <= 6 If...Then Statement which is incorrect ($47.00), Why? For some
reason the code wants to use the 4 < MaxL <= 6 If.. Then Statement even
though MaxL is equal to 4 not greater than four, Why?

Private Sub cmbCalculate_Click()

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

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

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

Select Case cboMaterial

Case "Clear .150 High Impact Modified Acrylic"

If MaxL <= 4 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL509", Sheets("Parts List").Range("A"), 4,
False)
' If MaxL < 4 Or MaxL <= 6 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL505", Sheets("Parts List").Range("A"), 4,
False)

Case "Clear .150 Polycarbonate"

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

Case "White .150 High Impact Modified Acrylic"

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

Case "White .150 Polycarbonate"

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

Case "Clear .177 High Impact Modified Acrylic"

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

Case "White .177 High Impact Modified Acrylic"

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

End Select

tbxCalculatedPrice = material
tbxCalculatedPrice = Format(tbxCalculatedPrice, "$ #,###,###.00")

End Sub

 

[RyanH]

See my other post "Select Case and If...Then Statements in UserForm not
Calculati", Rick Rothstein (MVP) and Bob Phillips have some good suggestions!

Thanks for you help!