List boxes in Excel - User Form

[striveforexcelence]

I tried to post this in the vba forum but it is down today apparently. Hoping someone in this forum can help.

I have a userform that has a listbox which draws the items in the list from sheet"(products").range("A1:B15") The items in column A are products and the information in column b are the corresponding unti costs.

I have tried to research this on the net and on other forum discussions but there seems to be no real direction with this. How do I go about using code to make the user's choice place onto worksheet"inventory" product = column a and unit cost = column b. After I have this I will go on to have it find the first available row but the main thing is I need the choice to go to column a and b regardless of where the cursor is. At present it places the choice wherever the cursor was last left positioned.

Any help or any direction for researching this would be greatly appreciated. I looked on microsoft and a couple of other articles but they seem to concentrate on creating the form and explaining controls rather than how to code the listbox to work.

Thank you very much!

 

[SkipVought]

Hi,

Please post the code that you are currently using for the event that is writing to the inventory sheet.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[striveforexcelence]

The one that I am using is sorta complicated to explain. That's why I am looking for a different way. It is a macro from John Archer that I downloaded. When the user makes a choice in the listbox, an index function on my products list worksheet pulls the item and its cost into "selectioncells". Then, somehow and I have never quite figured it out, it puts those two things into a column for prodcut and offests the column for the item cost.

My problem is this, I need to modify the code so that it can ONLY put the products in column b and offset so the cost is in column c. As of now, it places the product wherever the cursor was left last. The company I am contracted for is very insistent that every precaution be taken to avoid user error. I have the columns validated as well if I can just force this to go in the right column. I was trying to modify the code to look for the next availabe row so this is not exactly as it was in the original macro.

Thank yo ulooking at this.


Private Sub CommandButton1_Click()
'if the listindex of listbox equals -1 ... nothing selected
If lstSelection.ListIndex = -1 Then
MsgBox "No item selected", vbExclamation
Exit Sub
End If
Range("SelectionLink") = lstSelection.ListIndex + 1
Selection.Cells(1) = Worksheets("HipProducts").Range("D1")
Selection.Cells(1).Offset(0, 1) = Worksheets("HipProducts").Range("E1")

Sheets("Forecast").Range("B14").Select

Do

If IsEmpty Sheets(|Forecast").Range("B14") = False Then

ActiveCell.Offset(1, 0).Select

End If

Loop Until IsEmpty(ActiveCell) = True

Unload Me

 

[SkipVought]

Private Sub CommandButton1_Click()
    Dim lNextRow As Long
     'if the listindex of listbox equals -1 ... nothing selected
    If lstSelection.ListIndex = -1 Then
        MsgBox "No item selected", vbExclamation
        Exit Sub
    End If
    Range("SelectionLink") = lstSelection.ListIndex + 1
    Selection.Cells(1) = Worksheets("HipProducts").Range("D1")
    Selection.Cells(1).Offset(0, 1) = Worksheets("HipProducts").Range("E1")
                             
    With Sheets("Inventory")
        lNextRow = .Cells(.Cells(.Cells.Rows.Count, "A").End(xlUp).Row, "A").Row + 1
        .Cells(lNextRow, "A").Value = Sheets("products").Cells([selectionLink], "A").Value
        .Cells(lNextRow, "B").Value = Sheets("products").Cells([selectionLink], "B").Value
    End With
    
    Unload Me
End Sub

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[striveforexcelence]

Thanks for looking at this. I put the code in exactly and from what I remember yours is very similar to John Lacher's. It does put the product and the cost next to each other but wherever the cursor is...it doesnt limit to columns b and c and it doesnt seek out the first avaliable row it just stays on the same row and inserts over the one that was inserted before it. I am running out of ideas.

 

[SkipVought]

Would you please state the problem clearer?

        lNextRow = .Cells(.Cells(.Cells.Rows.Count, "A").End(xlUp).Row, "A").Row + 1

gets the next empty row in COLUMN A.

What do columns B & C have to do with that?

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[striveforexcelence]

The selection from the list box box (which is then in range D and # on the hipproducts list and is placed onto the main sheet .... those selections on the main sheet must appear in column A and column B respectively for product and unit cost. The user then enters information across the row pertaining to that choice.

Then, to control for user error, the next entry should go to the first available row so that it does not enter over the one already entered.

I used this program a couple of years ago and I can't find the code. It worked fine then and I don't remember it being difficult but then again I can't remember.

 

[striveforexcelence]

im sorry now i screwed it up.....the products must appear in columns b and c

 

[SkipVought]

If you examine my code carefully, the answer is as plain as the nose on your face.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[striveforexcelence]

it seems that way but its not
you try it
i have tried that
six people are then as "plain as the nose on my face"
i screwed up the verbage cuz i have been explaining this all day
i will change it and it will not be that simple
thanks tho

 

[SkipVought]

Instead of columns A & B it is rather columns B & C?

Tha seems pretty simple, as related to the code that I posted.

Hint: Look for [red]A & B[/red]

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[striveforexcelence]

yes that was my previous post that i apologized because now even i am confused yes they are supposed to be in b and c and only b anc c and not type over a previous post. It shouldnt matter where the cursor is it automatically finds the first available row in b and posts the selection to b and c.

 

[SkipVought]

It has ABSOLUTELY NOTHING AT ALL to do with the current selection or Active Cell!

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[striveforexcelence]

well that is my point. The question then remains.....what do I do .... what do I modify....what do I add......so that in the simplest explanation..... whatever the user chooses in that list box is put into the first available row in column b and the the corresponding unit price is put beside it in column c. To make a new entry then, it finds the next available row and places the choice in that row.

Right now, the user's choice does in fact appear on the correct worksheet. However, it is without regard to column or availability....it simply puts the selection of the user wherever the cursor was last left to reside.

 

[striveforexcelence]

SOLVED!!! Thanks to everyone who helped out with this. By piecing together bits from various people we were finally able to figure out the obstacle. Thank you all! It works perfectly now.