Combobox in Worksheet

backup69

Active Member
Joined
Jan 20, 2004
Messages
271
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.
Code:
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

backup69

Active Member
Joined
Jan 20, 2004
Messages
271
Now i found something for me but need to do fev changes.

Code:
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:
Code:
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:
Code:
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

Active Member
Joined
Jan 20, 2004
Messages
271
With one code i found solution:
Code:
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?
Code:
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?
 

backup69

Active Member
Joined
Jan 20, 2004
Messages
271

ADVERTISEMENT

across columns.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

Code:
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

Active Member
Joined
Jan 20, 2004
Messages
271
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Assuming your named ranges are single cells:

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

backup69

Active Member
Joined
Jan 20, 2004
Messages
271
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
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,560
Messages
5,765,100
Members
425,258
Latest member
brentmitchell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top