Adding Combobox to Column B

In your specific case, the following reference would be used:

   =$A$1:$A$3000

Once defined, you can copy the cell formatting to other cells.

Kevin

Hello Fordraiders,

if the combobox is located on another sheet then sheet2 then you might have to work with defined named ranges. And use that defined name in the ListSource property of the combobox.


the same thing (defined name) can also be sued in datavalidation: Data>>Datavalidation>>List  and use the defined name inhere.

I hope this helps you out

regards,
Jeroen

zorvek, I wll be doing a lot of deletion.
won't this formatting be deleted as i delete rows?
Thanks
fordraiders

fordraiders,
I think you posted aciidently the comment to the wrong question.
Jeroen

Yes. You can think of it as formatting the same as a font style. If you delete rows it will go wit the rows. If you insert rows the formatting will be copied from the row above.

Kevin

Sorry,
I didn't noticed Kevins posted first. Still Strange that his questions were not available when I posted my comment.
Jeroen

Well, formatting is not the correct word...
I simply want column B to be a lookup cell.. also..
I select an item from the combobox and its gets posted to the cell.
But It appears it is not that simple.

Oh...

B1=VLOOKUP(A1,Sheet2!$A$1:$A$3000,2)

Kevin

The above formula will match the value in A1 against the values in column A on Sheet2 and return the value in column B on the matching row.

Kevin

.o.k.

How about I add a combox to the worksheet.

I use

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ComboBox1.Top = ActiveCell.Top <----  PROBLEM
End Sub
But I only want the combox in Column B


I will give the combox it source from Sheet2!a2:a3000


So when I make a choice in the combox it gets posted the current cell in Column B.

Private Sub ComboBox1_Click()
    Application.Goto Reference:="Data"
    strfind = ComboBox1
    Selection.Find(What:=strfind, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate
    ActiveCell.Select
   End Sub


Maybe something like this ?

Thanks
fordraiders

Wow...talk about changing specifications ;-)

So, what you REALLY want is a way to find a specific value and then bring that value in to view. And you want the combo box to float up and down the sheet with the active cell? Hmmm, never seen that done before. Why not place the combo box in the top rows and then freeze panes so the top rows and the combo box remain visible?

Kevin

VISUALBASIC combobox

combobox ListFillrange   = Sheet2!A1:A4


Private Sub ComboBox1_Click()
If Not oldTarget Is Nothing Then oldTarget = ComboBox1.Value
ComboBox1.Height = ActiveCell.RowHeight + 4
ComboBox1.Width = 80
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ComboBox1.Top = ActiveCell.Top

ComboBox1.Top = Target.Top
    'ComboBox1 value (if changed) will be stored in same row as target, column D
Set oldTarget = Cells(Target.Row, 2)




from

https://www.experts-exchange.com/questions/20870476/Need-Combobox-on-Excel-Sheet-to-move-with-editing.html?query=Set+oldTarget&clearTAFilter=true


still checking