There is a listfillrange assigned which is where i obtain my values from.
This is using a named range ("Completed_Workstreams"), this named range is
dynamic.

I'm not sure how to use your code, I assume i need to put my named range in
somewhere to name the "listfillrange". Does it matter where the code is
inserted? Do i need to clear the listfillrange in the combobox's properties
menu once the macro is there?

Thank you again.


"Dave Peterson" wrote:

Gotcha.

But I bet you have a listfillrange assigned to that combobox. If I assigned the
list via code using .additem, it worked ok for me.

I used the worksheet_activate event, you may want to use workbook_open???

Option Explicit
Private Sub ComboBox1_Click()
ActiveCell.Value = ComboBox1.Value
End Sub

Private Sub Worksheet_Activate()
Dim iCtr As Long
With Me.ComboBox1
.ListFillRange = ""
For iCtr = 1 To 10
.AddItem "A" & iCtr
Next iCtr
End With
End Sub


Joseph Fletcher wrote:

The combobox isn't linked to a cell. I want it to populate whichever cell I
have selected at that time, so for example i can select cell a3, select the
text i want in the combobox and it puts that text into cell a3. I can then
select cell a6 select something different from the combo box and it will
populate cell a6 etc. This part works fine, the problems arise as when i
have a cell selected and press the delete key. Excel acts as if i have
clicked the current value in the combobox and populates the active cell. The
cell it populates is not fixed.

I am wondering whether there is an option that I don't know about that makes
the delete key activate the combobox?

"Dave Peterson" wrote:

Do you have that combobox linked to a cell?

If yes, drop the link. It looks like you're using code to populate the cell
anyway.

Joseph Fletcher wrote:

Hi,

I'm sure there is a very simple answer to the following problem but
unfortunately I can't see the wood for the trees!

In creating an activex combobox to add text into a cell by selecting it from
a list in the combobox I have encountered a problem. Adding the text is ok
but I cannot now delete the text in the cell (by using the delete key) and if
I select another cell anywhere else on the screen and press the delete key
then it inserts the text into the newly selected cell.

I have got the following code in the combobox:

Private Sub ComboBox1_Click()
ActiveCell.Value = ComboBox1.Value
End Sub

Also there is not a problem when i am in design mode, it appears that
somehow pressing delete activates the combobox but i have no idea how. I'm
using Excel 2002.

This is a very frustrating problem so any help would be greatly appreciated.

Thanks in advence.

--

Dave Peterson


--

Dave Peterson