No value in my listbox - Why?

[Neville Bentley]

Hi out there

I have a bit of code for a user form I am doing but I cannot seem to get the mathematics to work. When I step through the code I get the correct values for Tbox 5 & 8 but I cannot get the final part (the LISTBOX to pass its value and)to work. Can some one look at the code and let me know what I am doing wrong.


Private Sub TextBox9_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo InvalidTypes:
TextBox9.Value = CStr(CDbl(TextBox5.Text) * CDbl(TextBox8.Text) * CDbl(lstUnitP.Text))
InvalidTypes:
TextBox9.Value = "Non-Numerics in Either Textbox 5 or Textbox 8"
End Sub

Any help would be appreciated.

Cheers

Neville

 

[Richard Schollar]

Neville

You are multiplying (?) a string by a double - that's always going to go wrong.

 

[DonkeyOte]

Richard, the Cstr is after the calculation I think.

You will need to use .value rather than .text for the ListBox control.

Neville, as I recall it was a single select listbox correct ?

 

[Neville Bentley]

Thanks Richard,

Do you know what the answer is? I was hoping to get the snippet of code.

Cheers

Neville

 

[Richard Schollar]

Depends on what result you are hoping to get from this piece of code ie what is the desired output?

 

[DonkeyOte]

TextBox9.Value = CStr(CDbl(TextBox5.Text) * CDbl(TextBox8.Text) * CDbl(lstUnitP.Value))

 

[Kenneth Hobson]

It works but too fast for you to see. You need an Exit Sub for the case where it does work right.

Private Sub TextBox9_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  On Error GoTo InvalidTypes:
  TextBox9.Value = CStr(CDbl(TextBox5.Text) * CDbl(TextBox8.Text) * CDbl(lstUnitP.Text))
  Exit Sub
InvalidTypes:
  TextBox9.Value = "Non-Numerics in Either Textbox 5 or Textbox 8"
End Sub

 

[Neville Bentley]

Thanks Richard & Lasw10,

Sorry - The end result is a £ value something like £620

Word for the formula are:

The price per single(£) Textbox5
X
The number of packs per cbm Textbox8
X
The number of Unit packs lstUnitP

This should give me the value of the goods per cubic meter.

It works fine in the spreadsheet - I just need to get it to work in the userform.

Again thanks for your help.

Neville

 

[DonkeyOte]

Neville, I have to confess I'm still unsure regards this approach -- essentially you're populating textbox9 with this formula but this formula only fires after you've left textbox9, per the example the other day this formula should really fire in the exit / selection routines of those controls you're using in the formula to populate textbox9, no ?

 

[DonkeyOte]

Neville, based on your calculation I believe this is the code you should have in your UserForm to do what you want... this is a revised version of the code in the other post (http://www.mrexcel.com/forum/newreply.php?do=newreply&noquote=1&p=1612886)

This is tailored to your controls so you don't need to revise it.

Private Sub lstUnitP_Click()
Call TB9_Value
End Sub

Private Sub TextBox5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call TB9_Value
End Sub

Private Sub TextBox8_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call TB9_Value
End Sub

Sub TB9_Value(Ctrl As String)
On Error GoTo InvalidTypes:
TextBox9.Text = CStr(CDbl(TextBox5.Text) * CDbl(TextBox8.Text) * CDbl(lstUnitP.Value))
Exit Sub
InvalidTypes:
TextBox9.Value = "Non-Numerics in Either Textbox 5, Textbox 8 or Listbox lstUnitP "
End Sub

(I set you off on the wrong foot anyway as in my prior post I inadvertently coded textbox9.value = ... which would have failed regardless of the rest anyway)