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.

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

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

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

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

Yep. The placement of the code will matter.

If the list is dynamic, you'll want to repopulate it whenever that list changes.

If the list changes because of you (as the developer, not a user) change a
worksheet, then maybe you could use the workbook_open event.

Option Explicit
Private Sub Workbook_Open()
Dim myCell As Range
Dim myRng As Range
Set myRng = Worksheets("sheet2").Range("Completed_Workstreams" )

With Worksheets("sheet1").ComboBox1
.ListFillRange = ""
.Clear
For Each myCell In myRng.Cells
.AddItem myCell.Value
Next myCell
End With
End Sub

If the sheet changes more often, maybe each time that list changes (with some
worksheet_change event).
Or if the list is on one worksheet and the combobox is on a different one, then
maybe only when the user activates the worksheet with the combobox.

Or maybe when they click on that combobox--and use the _Change event to populate
the activecell.

Option Explicit
Dim BlkProc As Boolean
Private Sub ComboBox1_Change()
If BlkProc = True Then Exit Sub
ActiveCell.Value = ComboBox1.Value
End Sub
Private Sub ComboBox1_Click()
Dim myCell As Range
Dim myRng As Range

Set myRng = Worksheets("sheet2").Range("Completed_Workstreams" )

BlkProc = True
With Worksheets("sheet1").ComboBox1
.ListFillRange = ""
.Clear
For Each myCell In myRng.Cells
.AddItem myCell.Value
Next myCell
End With
BlkProc = False
End Sub



Joseph Fletcher wrote:

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


--

Dave Peterson