Open window with data from other sheet

yo would need a userform with a listbox, or use data validation

Your source list of products isn't really ideal, you have sub titles. I have added a dynamic named range called products to act as the data source for the listbox. When you enter a description cell the form opens, click an item in the listbox to add it to the list

I've just checked the example & I see you have merged cells in the invoice, this must be stopping the code working.

Amend the code in the worksheet event to

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column <> 3 Then Exit Sub
    If Target.Row < 12 Or Target.Row > 13 Then Exit Sub
    UserForm1.Show
End Sub

Copy the code above
Select the worksheet in which you the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste

The price is inserted into the sheet, it's picking up the wrong price. I'll adjust this later
You create UserForms in the VB Editor

Here's the amended code to add the price

Private Sub lbxProducts_Click()
    With Me.lbxProducts
        ActiveCell.Value = .Value
        ActiveCell.Offset(0, 1).Value = .List(.ListIndex, 1)
    End With
End Sub

Just replace the existing lbxProducts_Click code in the userform.

Open the VB EDitor
Double click on the UserForm & you should see the existing code

It won't do, the ode that you have replaced displays the form.

The code that you need to replace is in the userform, see the instructions in my last post

It's no that easy, the code is totally different. It would help if you stated the exact scenario in the first post to avoid wasting time.

Private Sub UserForm_Initialize()
    Dim oWbk   As Workbook
    Dim rdata  As Range
    Dim Col1Wid As Long
    Dim Col2Wid As Long

    Col1Wid = Range("Products").Columns(1).Width
    Col2Wid = Range("Products").Columns(2).Width
    On Error GoTo exit_handler
    Application.ScreenUpdating = False
    'check if workbook containing source is open if not open it.
    If Not IsOpen("2.xlsx") Then
        Set oWbk = Workbooks.Open("C:\Users\Taurus\Desktop\[2.xlsx")
    Else: Set oWbk = Workbooks("C:\Users\Taurus\Desktop\[2.xlsx")
    End If
    'this is the data to load to combobox
    With oWbk.Worksheets(1)
        Set rdata = .Range(.Cells(1, 1).Cells(.Rows.Count, 2).End(xlUp))
    End With
    With Me.lbxProducts
        .ColumnCount = 2
        .ColumnWidths = Col1Wid & ";" & Col2Wid
        .RowSource = rdata.Address(external:=True)
        .ListIndex = 0
    End With

exit_handler:

    Set oWbk = Nothing
    Set rdata = Nothing
    Application.ScreenUpdating = True
    On Error GoTo 0
End Sub
Function IsOpen(wbName As String) As Boolean
    Dim Wb     As Workbook
    On Error Resume Next
    Set Wb = Workbooks(wbName)
    If Err = 0 Then IsOpen = True
End Function

The code checks if the file is open, if not it opens it. Check the path file name are correct

A couple of changes

Private Sub UserForm_Initialize()
    Dim oWbk   As Workbook
    Dim rdata  As Range
    Dim Col1Wid As Long
    Dim Col2Wid As Long

    Col1Wid = Range("Products").Columns(1).Width
    Col2Wid = Range("Products").Columns(2).Width
    On Error GoTo exit_handler
    Application.ScreenUpdating = False
    'check if workbook containing source is open if not open it.
    If Not IsOpen("2.xlsx") Then
        Set oWbk = Workbooks.Open("C:\Users\Taurus\Desktop\2.xlsx")
    Else: Set oWbk = Workbooks("2.xlsx")
    End If
    'this is the data to load to combobox

    With oWbk.Sheets(1)
        Set rdata = .Range(.Cells(3, 1), .Cells(.Rows.Count, 2).End(xlUp))
    End With
    With Me.lbxProducts
        .ColumnCount = 2
        .ColumnWidths = Col1Wid & ";" & Col2Wid
        .RowSource = rdata.Address(external:=True)
        .ListIndex = 0
    End With

exit_handler:
    oWbk.Close False

    Set oWbk = Nothing
    Set rdata = Nothing
    Application.ScreenUpdating = True
    On Error GoTo 0
End Sub
Function IsOpen(wbName As String) As Boolean
    Dim Wb     As Workbook
    On Error Resume Next
    Set Wb = Workbooks(wbName)
    If Err = 0 Then IsOpen = True
End Function

Just checked & I get a message about memory. Not sure why but this change stops it

Private Sub UserForm_Initialize()
    Dim oWbk   As Workbook
    Dim rdata  As Range
    Dim Col1Wid As Long
    Dim Col2Wid As Long

    Col1Wid = Range("Products").Columns(1).Width
    Col2Wid = Range("Products").Columns(2).Width
    On Error GoTo exit_handler
    Application.ScreenUpdating = False
    'check if workbook containing source is open if not open it.
    If Not IsOpen("2.xlsx") Then
        Set oWbk = Workbooks.Open("C:\Users\Taurus\Desktop\2.xlsx")
    Else: Set oWbk = Workbooks("2.xlsx")
    End If
    'this is the data to load to combobox

    With oWbk.Sheets(1)
        Set rdata = .Range(.Cells(3, 1), .Cells(.Rows.Count, 2).End(xlUp))
    End With
    With Me.lbxProducts
        .ColumnCount = 2
        .ColumnWidths = Col1Wid & ";" & Col2Wid
        .List = rdata.Value
        .ListIndex = 0
    End With

exit_handler:
    oWbk.Close False

    Set oWbk = Nothing
    Set rdata = Nothing
    Application.ScreenUpdating = True
    On Error GoTo 0
End Sub

Hey man, You know that userform where the products are listed, its 1 line, is it possible to make for each products 2 lines? so its easier to see?

I also have another idea. See the problem right now is when I click new row, the userform doesnt open for the new rows. Only opens for the first 2. Now can have a button that that I click next to the rows and that way opens then userform instead of clicking on the cell?
See attachement for what I mean.

cheers

You can't split the lines in a listbox.

If you want to learn VBA & produce projects then study the code that you are given.The ros that the form works in is set in the code.

You can't add a unit price without overwriting the formula.

I think you will find that problems will continue to crop up. The best thing is to pln your project step by step before starting, strangely in this computer age a pen & paper helps. If you do this you will then be able to check if any steps will cause problems later.

The simplest way to add rows is to et up upur template with sufficient rows & hide the extra ones. Then in the code you can unhide rows as required.

ah ok guess Ill create a macro for that. Also how can I increase the macro for the userform whenever I add a new row? If you check the file above I click on a button and it adds a new row, so wouldnt it be possible for the macro that opens the userform to be applied to that new one?

Originally Posted by philmetz

ah ok guess Ill create a macro for that. Also how can I increase the macro for the userform whenever I add a new row?

add a new row where? If you mean the sheet then change the code to this, it will work on C12 to C22

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Intersect(ActiveCell, Range("C12:C22")) Is Nothing Then UserForm1.Show
    UserForm1.Show
End Sub

Try the code above