Check this
CodeDisplay MorePrivate Sub cmdAddItem_Click() If txtamount.Value = "" Then MsgBox "Please enter a value" Exit Sub Else ' Code if not empty End If With Me.ListBox1 .AddItem Me.lstResults.Value .List(.ListCount - 1, 1) = Me.txtamount.Value Me.txtamount.Value = Empty End With End Sub
Thats works perfect thank you
Also is there any way i can get column headers in lstresults box?
pickform
Everyone has to learn, my comments were only meant to be helpful.
Post back if you aren't sure about what I have changed.
Hi Roy,
Only just got round to having a look at this again as im picking it up in between my normal workload.
Again thanks for helping out with this.
So a couple of things as what you have changed im having a bit of trouble understanding.
The amount is now in the next column as asked but when you add another item and amount the item lists below as required but the amount just stays with the first item and just changes it.
Also id like to get the whole line of text from above not just the first item.
Right im done for the week, i will be back on Tuesday for sure
As i have said im learning as i go along so some things are either getting missed or changed by error.
im very greatful for the help ty
so ive had no VBA training im learning as i go along, this is a brand new sheet ive been working on.
Ok the jist of what im trying to achieve at this point form PickForm
I want to be able to select a type of ironmongery from the combobox which then gives me all the options in the listbox underneath when you click on an item it will bring up a picture, in the amount box i want to be able to enter the amount of this item that i need which then will transfer down to the bottom list box which will become my pick list that i will send to a sheet within excel.
id like the bottom box to look like this
Product Code Description Per Unit Price Supplier Amount
As ive said im totally new to this kinda stuff so other parts of what im doing maybe wrong but it all seems to work so far.
Hi Roy
Unfortunately i cant as some parts are linked to my local and network
newbie to vba im having trouble with listboxs and populating them with the info i need in the correct format.
So i already have 1 listbox (lstResults) which the information in it is taken from a selected item from a combobox (cboxType)
from lstResults i click an item and them in a separate textbox i want to add an amount eg 10 then with a commandbutton add the lstResults information and textbox amount to another listbox in different columns in the same row and then be able to add more with more selections.
ive nearly got it working but it just over writes the last entry.
Private Sub cmdAddItem_Click()
If txtamount.Value = "" Then
MsgBox "Please enter a value"
Exit Sub
Else
' Code if not empty
End If
With Me.ListBox1
.AddItem Me.lstResults.Value
.AddItem Me.txtamount.Value
End With
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub cboxType_Click()
Dim X As Integer
X = cboxType.ListIndex
Select Case X
Case Is = 0
lstResults.RowSource = "Hinge"
Case Is = 1
lstResults.RowSource = "FlushBolt"
Case Is = 2
lstResults.RowSource = "Lock"
Case Is = 3
lstResults.RowSource = "IntumescentJacket"
Case Is = 4
lstResults.RowSource = "Escutcheon"
Case Is = 5
lstResults.RowSource = "Cylinder"
Case Is = 6
lstResults.RowSource = "PullHandle"
Case Is = 7
lstResults.RowSource = "RTDHandle"
Case Is = 8
lstResults.RowSource = "DoorCloser"
Case Is = 9
lstResults.RowSource = "PushPlate"
Case Is = 10
lstResults.RowSource = "KickPlate"
Case Is = 11
lstResults.RowSource = "Signage"
Case Is = 12
lstResults.RowSource = "ThumbTurnIndicator"
Case Is = 13
lstResults.RowSource = "DigitalLock"
Case Is = 14
lstResults.RowSource = "CylinderPull"
Case Is = 15
lstResults.RowSource = "FloorSocket"
Case Is = 16
lstResults.RowSource = "DoorStop"
End Select
End Sub
Private Sub lstResults_Click()
'dim the variables
Dim i As Integer
Dim fPath As String
Dim Pic As String
'set the file path
fPath = ThisWorkbook.Path & "\" & "Ironmongery Images"
i = Me.lstResults.ListIndex
On Error Resume Next
'display the picture
Me.Image1.Picture = LoadPicture(fPath & "\" & Me.lstResults.Column(0, i) & ".jpg")
'If picture is not available
If Err = 53 Then
Me.Image1.Picture = LoadPicture(fPath & "\" & "Ironmongery Images" & "\" & "NoPicture.jpg")
End If
'reset error handler
On Error GoTo 0
End Sub
Private Sub txtamount_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Trim(txtamount.Value) = "" And Me.Visible Then
MsgBox "Cannot Be Empty", vbCritical, "Error"
Cancel = True
txtamount.BackColor = vbRed
Else
txtamount.BackColor = vbWhite
End If
End Sub
Private Sub UserForm_Initialize()
cboxType.RowSource = "ProductType"
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the Close button!"
End If
End SubI was a bit quick to ask, ive got it working now
Hi All,
Be gentle newbie here! and to VBA
Im trying to get the combox value to point to a worksheet where the text in the user form can be sent.
Any help would be greatful
RowCount = Worksheets("cboxProductType.value").Range("a1").CurrentRegion.Rows.Count
With Worksheets("cboxProductType.value").Range("a1")
.Offset(RowCount, 1) = txtProduct.Value
.Offset(RowCount, 2) = txtDescription.Value
.Offset(RowCount, 3) = txtPerUnit.Value
.Offset(RowCount, 6) = txtPrice.Value
.Offset(RowCount, 7) = cboxSupplier.Value