open userform and select listbox item

[silentwolf]

Hi guys,
again I need some help please...

Sub CopyWithoutProject()
    Dim lrow As Integer
    
    Tabelle7.Activate

    lrow = Cells(27, 8).End(xlUp).Row
    
    Cells(lrow, 8).Copy Destination:=Cells(lrow + 1, 7)
    Cells(lrow, 2).Copy Destination:=Cells(lrow + 1, 2)
    
    Cells(lrow + 1, 3) = [B]"Can I open a userform here and select an Item?"[/B]
    Application.CutCopyMode = False

End Sub

In the above code I like as writen open a userform or just a listbox and select a item from it.
Is something like this possible??

This is due to provide the user only with a list of items.

It would be nice if someone might have a hint for me.

Thanks again for your input!

Albert

 

[Worf]

Hi Albert

See if this helps:

• Set up a data validation list at cell H55
• Run the expander code. It will show the full list, and after a value is selected, it’s transferred to another sheet.

' standard module
Sub Expander()


Sheets("Sheet1").Range("h55").Activate  ' where the list is
MsgBox "Select an item:"
Application.SendKeys ("%{down}")        ' expand dropdown
 
End Sub

' sheet module
Private Sub Worksheet_Change(ByVal Target As Range)


If Target = Me.Range("h55") Then _
Me.Range("h55").Copy Sheets("Sheet2").Range("f45")


End Sub

 

[silentwolf]

Hi Worf,

sorry for the late reply was a bit busy.Unfortunatelly I need to get some other things sorted first but it looks good and I hope I be able to find time to test it all out!


Many thanks

Albert