Listboxes on userform
 

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 Im 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("VrdVerP ry") _
, 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

--
HJN

Listboxes on userform
 

the textbox for the quantity - use the change event to fire the calculation

"Hennie Neuhoff" wrote in 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 Im 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("VrdVerP ry") _
, 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

--
HJN

Listboxes on userform
 

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" wrote:

the textbox for the quantity - use the change event to fire the calculation

"Hennie Neuhoff" wrote in 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 Im 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("VrdVerP ry") _
, 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

--
HJN

Listboxes on userform
 

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" wrote in message
...
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" wrote:

the textbox for the quantity - use the change event to fire the
calculation

"Hennie Neuhoff" wrote in
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 Im 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("VrdVerP ry") _
, 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

--
HJN