Thats works perfect thank you
Posts by CJamesUK
Also is there any way i can get column headers in lstresults box?
Everyone has to learn, my comments were only meant to be helpful.
Post back if you aren't sure about what I have changed.
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.
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.Code
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 Sub
I was a bit quick to ask, ive got it working now
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
.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