UserForm with ListBox
The first thing is that you do not need to use modal afte the UserForm.Show
command. Modal is the default. If you do want to use it then the correct
syntax is
vbModal which is a boolean value or 1.
I don't see why the code would continue to execute before you click the
button unless your listbox also has code attached with a click event that
unloads or hides the UserForm. So check your listbox code for a click event
code.
Again, you can just use Choisir.Show and it will be modal.
"Jean " wrote:
?B?SkxHV2hpeg==?= wrote in
:
It is difficult to tell without seeing the code. Can you copy the
UserForm/Control code and post it for us to look at.
"Jean " wrote:
Inside a module I call a userform with a listbox from which someone
have to chose an Item.
From my understanding if I put "UserForm.show Modal" the program
must stop until someone click on one Item inside the ListBox &
confirme the validation with CommandButton, but instead the program
show the listbox fill correctly but don't stop to wait for the
interaction of the user & continues to run until the end of the
module. What I do wrong?
Thank you for your help.
Jean
This Program Take compare the first 3 letter of the first item in the
sheet"potato leek" with all items in the sheet "INGREDIENT". If only one
item is found the new price is taken from the sheet "INGREDIENT" & put
on the Sheet"potato leek" & the program go to the second item in the
sheet"Potao leek". But if several items in the sheet "INGREDIENT" match
it put this item name in the list box & wait the user select the right
item & click on commandbutton to validate his selection then the program
put the new price in the sheet"potato leek" & do the same thing for the
next item in sheet"potato leek" until Is no more item in this sheet.
Private Sub CommandButton1_Click()
Dim Ltind As Integer
Dim Prrw As Integer
Dim Proz As Double
If LaList.ListIndex = -1 Then
MsgBox ("YOU MUST SELECT ONE ITEM")
Exit Sub
End If
Ltind = LaList.ListIndex + 1
Prrw = Boite(Ltind, 3)
Proz = Sheets("INGREDIENT").Cells(Boite(Ltind, 2), 9)
Sheets(Feuille).Cells(Prrw, 7) = Proz
Sheets(Feuille).Cells(Prrw, 8) = Sheets(Feuille).Cells(Prrw, 6) * Proz
LaList.Clear
Choisir.Hide
End Sub
__________________________________________________ _________________
Public Feuille As String
Public Boite(20, 3) As Variant
Public Vend(10, 2) As Integer
Public rg As Integer
Sub UpDate_FdCt()
If Vend(1, 1) = 0 Then
Dim rw As Integer
Dim I As Integer
Dim II As Integer
Dim J As Integer
Dim JJ As Integer
Dim Hh As Integer
Dim Abrev As String
Dim Depart As Integer
Dim Arret As Integer
Dim Ctcom As Integer
Dim TpCtcom As Integer
rw = 5
rw1 = 1
Vend(1, 1) = rw
'In Sheet ingredient Delimit the range of each vendor
Do Until IsEmpty(Sheets("INGREDIENT").Cells(rw, 29))
If Sheets("INGREDIENT").Cells(rw, 29) < Sheets("INGREDIENT").Cells(rw +
1, 29) Then
Vend(rw1, 2) = rw
Vend(rw1 + 1, 1) = rw + 1
rw1 = rw1 + 1
End If
rw = rw + 1
Loop
'Put the data of the active sheet up to date
Feuille = ActiveSheet.Name
rg = 7 ' row of ITEM in the sheet dish
JJ = 1 ' row
End If
Label:
'select items of the active sheet & compare with the
item in sheet ingredient
If Not IsEmpty(Sheets(Feuille).Cells(rg, 1)) Then
Abrev = Left(Sheets(Feuille).Cells(rg, 1), 3)
Select Case Sheets(Feuille).Cells(rg, 5)
Case "#"
Sheets(Feuille).Cells(rg, 6) = Sheets(Feuille).Cells(rg,
4) * 16
Case "oz"
Sheets(Feuille).Cells(rg, 6) = Sheets(Feuille).Cells(rg,
4)
Case "u"
Sheets(Feuille).Cells(rg, 6) = Sheets(Feuille).Cells(rg,
4)
'
End Select
Depart = Vend(Sheets(Feuille).Cells(rg, 2), 1)
Arret = Vend(Sheets(Feuille).Cells(rg, 2), 2)
Ctcom = 1
For K = 1 To TpCtcom
Boite(K, 1) = ""
Boite(K, 2) = ""
Boite(K, 3) = " "
Next K
For II = Depart To Arret
If Abrev = UCase(Left(Sheets("INGREDIENT").Cells(II, 15),
3)) Then
'Add In Array as many Item have the same three letters
Boite(Ctcom, 1) = Sheets("INGREDIENT").Cells(II, 4)
Boite(Ctcom, 2) = II
Boite(Ctcom, 3) = rg
If Ctcom TpCtcom Then TpCtcom = Ctcom
Ctcom = Ctcom + 1
End If
Next II
'Check if we have more as one item, If "yes" create list box &
fill it
If TpCtcom 1 Then
For Hh = 0 To TpCtcom
Choisir.LaList.AddItem Boite(Hh + 1, 1)
Next Hh
Choisir.Show Modal
Else
Sheets(Feuille).Cells(rg, 7) = Sheets("INGREDIENT").Cells
(Boite(Ctcom - 1, 2), 9)
Sheets(Feuille).Cells(rg, 8) = Sheets(Feuille).Cells(rg, 6)
* Sheets(Feuille).Cells(rg, 7)
rg = rg + 1
GoTo Label
End If
rg = rg + 1
End If
End Sub
|