Combobox in Worksheet

[backup69]

Hi
I need litle help.I have combobox in worksheet("Sheet4") and source for this in worksheet("Sheet2").
I make combobox list in propertis Listfillrange = Sheet2!A11:A19
and try make Click or Change event.
In sheet2 Column A i have only names for combobox and in Column B i have values what i want use in the sheet4.

Private Sub ComboBox1_Change()
    Dim source
    input = ComboBox1.Value
    Set source = Worksheets("Andmebaas").Range("A11:A13").Find(what:=ComboBox1.Value)
 'here i need some check information if combodbox value = find value in sheet2 then i need take information behind this value in column B and put this in to the selection in sheet4. only if my selected area is Range("L2:L30")
    
For Each c In source
    If input = source and Activesheet.Selection = Range("L2:L30") Then

Selection.Value =
    End If
    Next
End Sub

can anyone help me

 

[backup69]

Now i found something for me but need to do fev changes.

Dim x As Worksheet, i As Integer
Set x = Worksheets("Sheet2")
i = 1
With ComboBox1
.Clear
Do Until IsEmpty(x.Cells(4, i))
.AddItem (x.Cells(4, i))
i = i + 1
Loop
End With
    ComboBox1.Style = fmStyleDropDownList

this code works in userform perfectly for me but i want put this in - Private Sub Worksheet_Activate().
Second:

Private Sub ComboBox1_Change() 
    Dim rng As Range 
    Set rng = Worksheets(Left(ComboBox1.ListFillRange, 6)).Range(Right(ComboBox1.ListFillRange, 6)) 
    Selection.Value = rng(ComboBox1.ListIndex + 1).Offset(0, 1).Value 
    Set rng = Nothing 
End Sub

originaly posted:Andrew Poulsom
Previous code works in worksheet only with this:

Private Sub Worksheet_Activate() 
    ComboBox1.ListFillRange = "Sheet2!A10:A22" 
    ComboBox1.Style = fmStyleDropDownList 
End Sub

originaly posted:Andrew Poulsom
But i dont have always same amont list to add. for this i need first code in my post.
With second code in my post i have 2 problem:
1) if i activate worksheet this code run somehow automatycally, but i want run this only if i chose som input.
2) i want put some "if" in this code if Selection or target = range("named") Then

can now anyone help me

 

[backup69]

With one code i found solution:

Private Sub Worksheet_Activate()
ComboBox1.Clear
Dim x As Worksheet, i As Integer
Set x = Worksheets("Andmebaas")
i = 1
With ComboBox1
Do Until IsEmpty(x.Cells(11, i))
iv = (x.Cells(11, i))
.AddItem iv
i = i + 1
Loop
End With
End Sub

Now i need change this code but how?

Private Sub ComboBox1_Click()
Dim rng As Range
    Set rng = Worksheets(Left(ComboBox1.ListFillRange, 6)).Range(Right(ComboBox1.ListFillRange, 6))
    Selection.Value = rng(ComboBox1.ListIndex + 1).Offset(1, 0).Value
    Set rng = Nothing
End Sub

here not working this ComboBox1.ListFillRange method!
maybe anyone know what metod works here?

 

[Andrew Poulsom]

Does your list go down rows or across columns?

 

[backup69]

across columns.

 

[Andrew Poulsom]

So is the item you want to return in the next row down? Example, ComboBox list in A11:G11, values to return in A12:G12.

 

[Andrew Poulsom]

Try this (assumes an answer of yes to my previous post):

Private Sub Worksheet_Activate()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim c As Range
    Set Sh = Worksheets("Sheet2")
    With Sh
        Set Rng = .Range(.Cells(11, 1), .Cells(11, .Range("A11").End(xlToRight).Column))
    End With
    ComboBox1.Clear
    For Each c In Rng
        ComboBox1.AddItem c.Value
    Next c
End Sub

Private Sub ComboBox1_Change()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim c As Integer
    Set Sh = Worksheets("Sheet2")
    With Sh
        Set Rng = .Range(.Cells(11, 1), .Cells(11, .Range("A11").End(xlToRight).Column))
    End With
    Selection.Value = Rng(ComboBox1.ListIndex + 1).Offset(1, 0).Value
End Sub

 

[backup69]

TY Andrew
This is perfectly for me

but how i can make somethig like this (my code again not working)

Private Sub ComboBox1_Change()
sel1 = Range("aeg")
sel2 = Range("aeg2")
If Selection = sel or sel2 Then
.......
End Sub

my Combobox code can activate only if range only "aeg" or "aeg2"

 

[Andrew Poulsom]

Assuming your named ranges are single cells:

If Selection.Address = Range("aeg").Address Or Selection.Address = Range("aeg2").Address Then

 

[backup69]

sry, not single my named range is all column E and second is L.
i need use named range only for this, if user insert new column my range move in new place