Listboxes on userform

H

Hennie Neuhoff

Hi. Guys

What I would like to do:
Get the user to select a product from a list [listbox1-"VrdLys" ; Userform
UcalCost]
Once his made the selection - promp to supply the quantity in a textbox
[TxtQuantity]
Calculate the cost - which is equal to the amount entered in txtQuantity
* the corresponding price of the product selected in Listbox1
Prompt the user to confirm his selection.

What I've achieved:
The list slection with the corresponding price of each product.
The calulation displayed in TboxAntw works 100%

My problem!
The prompt to supply the quantity does not work the way I want it. Only
after I've entered a number in TxtQuantity does the Inputbox appear to get
the quantity from the user - the result is corectly displayed in TxtQuantity.
I have a command button (CmnCost) when clicked it does the calculation
which is corectly displayed in TboxAntw. I would like this calculation to
be "automated" - if the user supply the quantity - the calculation should
automatically be displayed in TboxAntw without using the command button.
Finally I’m not sure how to get the user to confirm his selection on the
userform.

Tks in advance

This is my code:
Public Sub CalCost()
Dim N, TxtQuantity

'Get the product, quantity & calculate the cost
UCalCost.ListBox1.Clear

'select the item
UCalCost.ListBox1.RowSource = "VrdLys"
UCalCost.ListBox1.ListIndex = 0
UCalCost.TxtQuantity = ""
UCalCost.TboxAntw.Value = 0
UCalCost.Show
'get the number of the item selected in the box
N = UCalCost.ListBox1.ListIndex
With UCalCost.ListBox1
'Display selection & calc - sheet AanInlig
Worksheets("AanInlig").Range("tydvrd").Value = UCalCost.ListBox1.Value
Worksheets("AanInlig").Range("tydaan").Value = UCalCost.TxtQuantity.Value
Worksheets("AanInlig").Range("tydkos").Value = UCalCost.TboxAntw.Value

End With
Unload UCalCost
End Sub

This is the Userform code:
Private Sub CmnCost_Click()
If Me.TxtQuantity.Value <> "" Then
prod = Me.ListBox1.Value
Pprice = Application.WorksheetFunction.Index(Range("VrdVerPry") _
, Application.WorksheetFunction.Match(prod, Range("VrdLys"), 0))
TboxAntw = Pprice * TxtQuantity
End If
End Sub

Private Sub ListBox1_Click()

End Sub

Private Sub OkButton_Click()
ULysKoste.Hide
bContinue = True
End Sub

Private Sub TxtQuantity_Change()
TxtQuantity = Application.InputBox(prompt:="Quantity ?")
End Sub
 
H

Hennie Neuhoff

Patric, Thank you for responding. I'm not an expert in VBA - what I know I
basically learned on this forum. I've got no idea what youre talking about!!
Plaese forgive this dummy!
--
HJN


Patrick Molloy said:
the textbox for the quantity - use the change event to fire the calculation

Hennie Neuhoff said:
Hi. Guys

What I would like to do:
Get the user to select a product from a list [listbox1-"VrdLys" ; Userform
UcalCost]
Once his made the selection - promp to supply the quantity in a textbox
[TxtQuantity]
Calculate the cost - which is equal to the amount entered in txtQuantity
* the corresponding price of the product selected in Listbox1
Prompt the user to confirm his selection.

What I've achieved:
The list slection with the corresponding price of each product.
The calulation displayed in TboxAntw works 100%

My problem!
The prompt to supply the quantity does not work the way I want it. Only
after I've entered a number in TxtQuantity does the Inputbox appear to get
the quantity from the user - the result is corectly displayed in
TxtQuantity.
I have a command button (CmnCost) when clicked it does the calculation
which is corectly displayed in TboxAntw. I would like this calculation to
be "automated" - if the user supply the quantity - the calculation should
automatically be displayed in TboxAntw without using the command button.
Finally I’m not sure how to get the user to confirm his selection on the
userform.

Tks in advance

This is my code:
Public Sub CalCost()
Dim N, TxtQuantity

'Get the product, quantity & calculate the cost
UCalCost.ListBox1.Clear

'select the item
UCalCost.ListBox1.RowSource = "VrdLys"
UCalCost.ListBox1.ListIndex = 0
UCalCost.TxtQuantity = ""
UCalCost.TboxAntw.Value = 0
UCalCost.Show
'get the number of the item selected in the box
N = UCalCost.ListBox1.ListIndex
With UCalCost.ListBox1
'Display selection & calc - sheet AanInlig
Worksheets("AanInlig").Range("tydvrd").Value = UCalCost.ListBox1.Value
Worksheets("AanInlig").Range("tydaan").Value = UCalCost.TxtQuantity.Value
Worksheets("AanInlig").Range("tydkos").Value = UCalCost.TboxAntw.Value

End With
Unload UCalCost
End Sub

This is the Userform code:
Private Sub CmnCost_Click()
If Me.TxtQuantity.Value <> "" Then
prod = Me.ListBox1.Value
Pprice = Application.WorksheetFunction.Index(Range("VrdVerPry") _
, Application.WorksheetFunction.Match(prod, Range("VrdLys"), 0))
TboxAntw = Pprice * TxtQuantity
End If
End Sub

Private Sub ListBox1_Click()

End Sub

Private Sub OkButton_Click()
ULysKoste.Hide
bContinue = True
End Sub

Private Sub TxtQuantity_Change()
TxtQuantity = Application.InputBox(prompt:="Quantity ?")
End Sub
Click to expand...
Click to expand...
 
P

Patrick Molloy

1) remove this block of code from your module

'Get the product, quantity & calculate the cost
UCalCost.ListBox1.Clear
'select the item
UCalCost.ListBox1.RowSource = "VrdLys"
UCalCost.ListBox1.ListIndex = 0
UCalCost.TxtQuantity = ""
UCalCost.TboxAntw.Value = 0


2) delete or remark out this block

' Private Sub TxtQuantity_Change()
' TxtQuantity = Application.InputBox(prompt:="Quantity ?")
' End Sub

the textbox itself is already your user input.

3) in the userform, select the listbox "listbox1" and set the RowSource to
VrdLys

4) add this code:

Private Sub ListBox1_Click()
' this shouldn't be necessary either with a userform
' and can be deleted
TxtQuantity.SetFocus
MsgBox "Please enter a quantity"
End Sub

5) finally i changed the OKbutton_click event - i didn't know what you were
trying to do

Private Sub OkButton_Click()
Me.Hide
End Sub


Hennie Neuhoff said:
Patric, Thank you for responding. I'm not an expert in VBA - what I know I
basically learned on this forum. I've got no idea what youre talking
about!!
Plaese forgive this dummy!
--
HJN


Patrick Molloy said:
the textbox for the quantity - use the change event to fire the
calculation

message
Hi. Guys

What I would like to do:
Get the user to select a product from a list [listbox1-"VrdLys" ;
Userform
UcalCost]
Once his made the selection - promp to supply the quantity in a textbox
[TxtQuantity]
Calculate the cost - which is equal to the amount entered in
txtQuantity
* the corresponding price of the product selected in Listbox1
Prompt the user to confirm his selection.

What I've achieved:
The list slection with the corresponding price of each product.
The calulation displayed in TboxAntw works 100%

My problem!
The prompt to supply the quantity does not work the way I want it. Only
after I've entered a number in TxtQuantity does the Inputbox appear to
get
the quantity from the user - the result is corectly displayed in
TxtQuantity.
I have a command button (CmnCost) when clicked it does the calculation
which is corectly displayed in TboxAntw. I would like this calculation
to
be "automated" - if the user supply the quantity - the calculation
should
automatically be displayed in TboxAntw without using the command
button.
Finally I’m not sure how to get the user to confirm his selection on
the
userform.

Tks in advance

This is my code:
Public Sub CalCost()
Dim N, TxtQuantity

'Get the product, quantity & calculate the cost
UCalCost.ListBox1.Clear

'select the item
UCalCost.ListBox1.RowSource = "VrdLys"
UCalCost.ListBox1.ListIndex = 0
UCalCost.TxtQuantity = ""
UCalCost.TboxAntw.Value = 0
UCalCost.Show
'get the number of the item selected in the box
N = UCalCost.ListBox1.ListIndex
With UCalCost.ListBox1
'Display selection & calc - sheet AanInlig
Worksheets("AanInlig").Range("tydvrd").Value = UCalCost.ListBox1.Value
Worksheets("AanInlig").Range("tydaan").Value =
UCalCost.TxtQuantity.Value
Worksheets("AanInlig").Range("tydkos").Value = UCalCost.TboxAntw.Value

End With
Unload UCalCost
End Sub

This is the Userform code:
Private Sub CmnCost_Click()
If Me.TxtQuantity.Value <> "" Then
prod = Me.ListBox1.Value
Pprice = Application.WorksheetFunction.Index(Range("VrdVerPry") _
, Application.WorksheetFunction.Match(prod, Range("VrdLys"), 0))
TboxAntw = Pprice * TxtQuantity
End If
End Sub

Private Sub ListBox1_Click()

End Sub

Private Sub OkButton_Click()
ULysKoste.Hide
bContinue = True
End Sub

Private Sub TxtQuantity_Change()
TxtQuantity = Application.InputBox(prompt:="Quantity ?")
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

messagebox help 2
Userform Initialize Problem ! 1
UserForm ListBox 1
VBA userform Vlookup Excel 1
Date Field Problem in Userform 4
Dependant ComboBox on a UserForm 1
UserForm past data 7
"Update" calendar on userform? 5

Top