# No value in my listbox - Why?

#### Neville Bentley

##### Board Regular
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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Richard Schollar

##### MrExcel MVP
Neville

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

#### DonkeyOte

##### MrExcel MVP
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

##### Board Regular
Thanks Richard,

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

Cheers

Neville

#### Richard Schollar

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

#### DonkeyOte

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

#### Kenneth Hobson

##### Well-known Member
It works but too fast for you to see. You need an Exit Sub for the case where it does work right.
Code:
``````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

##### Board Regular
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

##### MrExcel MVP
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

##### MrExcel MVP
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.

Code:
``````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)

Last edited:

Replies
17
Views
1K
Replies
7
Views
4K
Replies
4
Views
319
Replies
8
Views
327
Replies
8
Views
534

1,190,783
Messages
5,982,896
Members
439,805
Latest member
IDarkstarX

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back