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

Sorry Dave, still not working.

I have this code of yours in the sheet:

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("Fletcher, J").Range("Completed_Workstreams")

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

The list and the combobox are both in the same sheet.

Now if I just select something from the combobox then nothing happens (there
is no list), if i go to the combobox properties (under listfillrange there is
Completed_workstreams) delete Completed_workstreams from the listfillrange
box, press enter and then retype Completed_workstreams in the listfillrange
box, then everything works perfectly. Unfortunately if I now save, exit and
then reopen then the combobox stops working again despite having
completed_workstreams in the listfillrange again.

I was already out of ideas, hopefully you aren't!

Cheers


"Dave Peterson" wrote:

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

This seemed to work ok:

In the workbook_open event:

Option Explicit
Private Sub Workbook_Open()
'use the codename for the worksheet named "fletcher, j"
Call Sheet1.ComboBox1_click
End Sub


Under the "fletcher, j" worksheet:

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

Set myRng = Me.Range("Completed_Workstreams")

BlkProc = True
With Me.ComboBox1
.ListFillRange = ""
.ListRows = 8
.Clear
For Each myCell In myRng.Cells
.AddItem myCell.Value
Next myCell
End With
BlkProc = False
End Sub


Joseph Fletcher wrote:

Sorry Dave, still not working.

I have this code of yours in the sheet:

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("Fletcher, J").Range("Completed_Workstreams")

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

The list and the combobox are both in the same sheet.

Now if I just select something from the combobox then nothing happens (there
is no list), if i go to the combobox properties (under listfillrange there is
Completed_workstreams) delete Completed_workstreams from the listfillrange
box, press enter and then retype Completed_workstreams in the listfillrange
box, then everything works perfectly. Unfortunately if I now save, exit and
then reopen then the combobox stops working again despite having
completed_workstreams in the listfillrange again.

I was already out of ideas, hopefully you aren't!

Cheers

"Dave Peterson" wrote:

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


--

Dave Peterson

Brilliant, Thank you very much. Works perfectly.

One thing that would make it even better is if the combo box could always
display "Select Workstream" except for when something is being selected. At
the moment it is always blank until something is selected. Ideally it would
show "select workstream", then when clicked on the dropdownlist would
dropdown showing the list as it does at the moment then when something has
been selected it goes back to just showing select workstream. In my list I
already have the top line as "Select Workstream" so really it just needs to
show this unless there is an easier way. Do you have any idea of how to do
this?

Is it possible to highlight the commands that make the combobox display
"select workstream" as well please, just to make it easier for me to learn to
do this myself!

Many thanks, you've been a great help.

"Dave Peterson" wrote:

This seemed to work ok:

In the workbook_open event:

Option Explicit
Private Sub Workbook_Open()
'use the codename for the worksheet named "fletcher, j"
Call Sheet1.ComboBox1_click
End Sub


Under the "fletcher, j" worksheet:

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

Set myRng = Me.Range("Completed_Workstreams")

BlkProc = True
With Me.ComboBox1
.ListFillRange = ""
.ListRows = 8
.Clear
For Each myCell In myRng.Cells
.AddItem myCell.Value
Next myCell
End With
BlkProc = False
End Sub


Joseph Fletcher wrote:

Sorry Dave, still not working.

I have this code of yours in the sheet:

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("Fletcher, J").Range("Completed_Workstreams")

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

The list and the combobox are both in the same sheet.

Now if I just select something from the combobox then nothing happens (there
is no list), if i go to the combobox properties (under listfillrange there is
Completed_workstreams) delete Completed_workstreams from the listfillrange
box, press enter and then retype Completed_workstreams in the listfillrange
box, then everything works perfectly. Unfortunately if I now save, exit and
then reopen then the combobox stops working again despite having
completed_workstreams in the listfillrange again.

I was already out of ideas, hopefully you aren't!

Cheers

"Dave Peterson" wrote:

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


--

Dave Peterson

Maybe...

Option Explicit
Dim BlkProc As Boolean
Private Sub ComboBox1_Change()
If BlkProc = True Then Exit Sub
If Me.ComboBox1.ListIndex 0 Then
ActiveCell.Value = ComboBox1.Value
Else
Beep
End If
End Sub
Sub ComboBox1_click()
Dim myCell As Range
Dim myRng As Range

If BlkProc = True Then Exit Sub

Set myRng = Me.Range("Completed_Workstreams")

BlkProc = True
With Me.ComboBox1
.ListFillRange = ""
.ListRows = 8
.Clear
.AddItem "Select Workstream"
For Each myCell In myRng.Cells
.AddItem myCell.Value
Next myCell
.ListIndex = 0
End With

BlkProc = False
End Sub


(the workbook_open event stays the same.)

Joseph Fletcher wrote:

Brilliant, Thank you very much. Works perfectly.

One thing that would make it even better is if the combo box could always
display "Select Workstream" except for when something is being selected. At
the moment it is always blank until something is selected. Ideally it would
show "select workstream", then when clicked on the dropdownlist would
dropdown showing the list as it does at the moment then when something has
been selected it goes back to just showing select workstream. In my list I
already have the top line as "Select Workstream" so really it just needs to
show this unless there is an easier way. Do you have any idea of how to do
this?

Is it possible to highlight the commands that make the combobox display
"select workstream" as well please, just to make it easier for me to learn to
do this myself!

Many thanks, you've been a great help.

"Dave Peterson" wrote:

This seemed to work ok:

In the workbook_open event:

Option Explicit
Private Sub Workbook_Open()
'use the codename for the worksheet named "fletcher, j"
Call Sheet1.ComboBox1_click
End Sub


Under the "fletcher, j" worksheet:

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

Set myRng = Me.Range("Completed_Workstreams")

BlkProc = True
With Me.ComboBox1
.ListFillRange = ""
.ListRows = 8
.Clear
For Each myCell In myRng.Cells
.AddItem myCell.Value
Next myCell
End With
BlkProc = False
End Sub


Joseph Fletcher wrote:

Sorry Dave, still not working.

I have this code of yours in the sheet:

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("Fletcher, J").Range("Completed_Workstreams")

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

The list and the combobox are both in the same sheet.

Now if I just select something from the combobox then nothing happens (there
is no list), if i go to the combobox properties (under listfillrange there is
Completed_workstreams) delete Completed_workstreams from the listfillrange
box, press enter and then retype Completed_workstreams in the listfillrange
box, then everything works perfectly. Unfortunately if I now save, exit and
then reopen then the combobox stops working again despite having
completed_workstreams in the listfillrange again.

I was already out of ideas, hopefully you aren't!

Cheers

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson

Thanks for your help Dave, both of these answers work perfectly.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Dim BlkProc As Boolean
Private Sub ComboBox1_Change()
If BlkProc = True Then Exit Sub
If Me.ComboBox1.ListIndex 0 Then
ActiveCell.Value = ComboBox1.Value
Else
Beep
End If
End Sub
Sub ComboBox1_click()
Dim myCell As Range
Dim myRng As Range

If BlkProc = True Then Exit Sub

Set myRng = Me.Range("Completed_Workstreams")

BlkProc = True
With Me.ComboBox1
.ListFillRange = ""
.ListRows = 8
.Clear
.AddItem "Select Workstream"
For Each myCell In myRng.Cells
.AddItem myCell.Value
Next myCell
.ListIndex = 0
End With

BlkProc = False
End Sub


(the workbook_open event stays the same.)

Joseph Fletcher wrote:

Brilliant, Thank you very much. Works perfectly.

One thing that would make it even better is if the combo box could always
display "Select Workstream" except for when something is being selected. At
the moment it is always blank until something is selected. Ideally it would
show "select workstream", then when clicked on the dropdownlist would
dropdown showing the list as it does at the moment then when something has
been selected it goes back to just showing select workstream. In my list I
already have the top line as "Select Workstream" so really it just needs to
show this unless there is an easier way. Do you have any idea of how to do
this?

Is it possible to highlight the commands that make the combobox display
"select workstream" as well please, just to make it easier for me to learn to
do this myself!

Many thanks, you've been a great help.

"Dave Peterson" wrote:

This seemed to work ok:

In the workbook_open event:

Option Explicit
Private Sub Workbook_Open()
'use the codename for the worksheet named "fletcher, j"
Call Sheet1.ComboBox1_click
End Sub


Under the "fletcher, j" worksheet:

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

Set myRng = Me.Range("Completed_Workstreams")

BlkProc = True
With Me.ComboBox1
.ListFillRange = ""
.ListRows = 8
.Clear
For Each myCell In myRng.Cells
.AddItem myCell.Value
Next myCell
End With
BlkProc = False
End Sub


Joseph Fletcher wrote:

Sorry Dave, still not working.

I have this code of yours in the sheet:

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("Fletcher, J").Range("Completed_Workstreams")

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

The list and the combobox are both in the same sheet.

Now if I just select something from the combobox then nothing happens (there
is no list), if i go to the combobox properties (under listfillrange there is
Completed_workstreams) delete Completed_workstreams from the listfillrange
box, press enter and then retype Completed_workstreams in the listfillrange
box, then everything works perfectly. Unfortunately if I now save, exit and
then reopen then the combobox stops working again despite having
completed_workstreams in the listfillrange again.

I was already out of ideas, hopefully you aren't!

Cheers

"Dave Peterson" wrote:

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


--

Another alternative that you may want to try (and discard). But try it against
a copy of your workbook--just in case. Even if you don't use it in this
workbook, you may find it useful in another application.



Option Explicit
Const myToolBarName = "FletcherToolBar"
Sub auto_open()

Dim myCB As CommandBar
Dim myCtrl As CommandBarControl

On Error Resume Next
Application.CommandBars(myToolBarName).Delete
On Error GoTo 0

Set myCB = Application.CommandBars.Add(Name:=myToolBarName, _
Position:=msoBarFloating, _
temporary:=True)

With myCB
.Visible = True
Set myCtrl = .Controls.Add(Type:=msoControlDropdown)
End With

Call LoadCombobox

With myCtrl
.Width = 200
.Style = msoComboLabel
.Caption = "Select Workstream"
.OnAction = "'" & ThisWorkbook.Name & "'!addtocell"
End With

Set myCtrl = myCB.Controls.Add(Type:=msoControlButton)
With myCtrl
.OnAction = "'" & ThisWorkbook.Name & "'!LoadCombobox"
.Caption = "Refresh Dropdown"
.Style = msoButtonIconAndCaption
End With

End Sub
Sub auto_close()
On Error Resume Next
Application.CommandBars(myToolBarName).Delete
On Error GoTo 0
End Sub
Sub AddtoCell()
With CommandBars.ActionControl
If .ListIndex = 0 Then
'do nothing
Else
ActiveCell.Value = .List(.ListIndex)
Application.CommandBars(myToolBarName).Controls(1) .ListIndex = 0
End If
End With
End Sub
Sub LoadCombobox()

Dim myCtrl As CommandBarControl
Dim myCell As Range
Dim myRng As Range

Set myCtrl = Application.CommandBars(myToolBarName).Controls(1)

Set myRng = ThisWorkbook.Worksheets("Fletcher J") _
.Range("Completed_Workstreams")

With myCtrl
.Clear
For Each myCell In myRng.Cells
.AddItem myCell.Value
Next myCell
.ListIndex = 0
End With

End Sub


Joseph Fletcher wrote:

Thanks for your help Dave, both of these answers work perfectly.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Dim BlkProc As Boolean
Private Sub ComboBox1_Change()
If BlkProc = True Then Exit Sub
If Me.ComboBox1.ListIndex 0 Then
ActiveCell.Value = ComboBox1.Value
Else
Beep
End If
End Sub
Sub ComboBox1_click()
Dim myCell As Range
Dim myRng As Range

If BlkProc = True Then Exit Sub

Set myRng = Me.Range("Completed_Workstreams")

BlkProc = True
With Me.ComboBox1
.ListFillRange = ""
.ListRows = 8
.Clear
.AddItem "Select Workstream"
For Each myCell In myRng.Cells
.AddItem myCell.Value
Next myCell
.ListIndex = 0
End With

BlkProc = False
End Sub


(the workbook_open event stays the same.)

Joseph Fletcher wrote:

Brilliant, Thank you very much. Works perfectly.

One thing that would make it even better is if the combo box could always
display "Select Workstream" except for when something is being selected. At
the moment it is always blank until something is selected. Ideally it would
show "select workstream", then when clicked on the dropdownlist would
dropdown showing the list as it does at the moment then when something has
been selected it goes back to just showing select workstream. In my list I
already have the top line as "Select Workstream" so really it just needs to
show this unless there is an easier way. Do you have any idea of how to do
this?

Is it possible to highlight the commands that make the combobox display
"select workstream" as well please, just to make it easier for me to learn to
do this myself!

Many thanks, you've been a great help.

"Dave Peterson" wrote:

This seemed to work ok:

In the workbook_open event:

Option Explicit
Private Sub Workbook_Open()
'use the codename for the worksheet named "fletcher, j"
Call Sheet1.ComboBox1_click
End Sub


Under the "fletcher, j" worksheet:

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

Set myRng = Me.Range("Completed_Workstreams")

BlkProc = True
With Me.ComboBox1
.ListFillRange = ""
.ListRows = 8
.Clear
For Each myCell In myRng.Cells
.AddItem myCell.Value
Next myCell
End With
BlkProc = False
End Sub


Joseph Fletcher wrote:

Sorry Dave, still not working.

I have this code of yours in the sheet:

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("Fletcher, J").Range("Completed_Workstreams")

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

The list and the combobox are both in the same sheet.

Now if I just select something from the combobox then nothing happens (there
is no list), if i go to the combobox properties (under listfillrange there is
Completed_workstreams) delete Completed_workstreams from the listfillrange
box, press enter and then retype Completed_workstreams in the listfillrange
box, then everything works perfectly. Unfortunately if I now save, exit and
then reopen then the combobox stops working again despite having
completed_workstreams in the listfillrange again.

I was already out of ideas, hopefully you aren't!

Cheers

"Dave Peterson" wrote:

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


--

--

Dave Peterson