Hi,

I have a combobox from the control toolbox which I have placed on a
worksheet. I have some code that runs from the worksheet selection
change event, (basically I am replacing the in cell data validation
that I was using, the reason I went down this route was that the sheet
is zoomed to about 70% and at this level the validation is barealy
ledgible) the code is:

' Position the combobox to the activecell
ActiveSheet.CellMenu.Top = ActiveCell.Top + 18
ActiveSheet.CellMenu.Left = ActiveCell.Left
' Make the combobox visible
ActiveSheet.CellMenu.Visible = True
' Show the dropdown list
ActiveSheet.CellMenu.DropDown
' Make the combobox not have anything preselected
ActiveSheet.CellMenu.ListIndex = -1

If the user clicks on another cell, I just make the combobox inivisible
however this is where my problem occurs as the dropdown doesn't go
away, also if the combobox list is showing and I move to another
workbook the list follows as the user hasn't clicked anything on the
dropdown. The CellMenu is just a comob box that has an onclick event
that puts the value of the combobox in the activecell.

Any suggestions would be very much appreciated, cheers,

James

Did you turn screenupdating off?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
ups.com...
Hi,

I have a combobox from the control toolbox which I have placed on a
worksheet. I have some code that runs from the worksheet selection
change event, (basically I am replacing the in cell data validation
that I was using, the reason I went down this route was that the sheet
is zoomed to about 70% and at this level the validation is barealy
ledgible) the code is:

' Position the combobox to the activecell
ActiveSheet.CellMenu.Top = ActiveCell.Top + 18
ActiveSheet.CellMenu.Left = ActiveCell.Left
' Make the combobox visible
ActiveSheet.CellMenu.Visible = True
' Show the dropdown list
ActiveSheet.CellMenu.DropDown
' Make the combobox not have anything preselected
ActiveSheet.CellMenu.ListIndex = -1

If the user clicks on another cell, I just make the combobox inivisible
however this is where my problem occurs as the dropdown doesn't go
away, also if the combobox list is showing and I move to another
workbook the list follows as the user hasn't clicked anything on the
dropdown. The CellMenu is just a comob box that has an onclick event
that puts the value of the combobox in the activecell.

Any suggestions would be very much appreciated, cheers,

James

Hi,

No, do I need to?

I think what I need to do is the opposite of the dropdown method of the
combo if no selection is made and the user has moved away from the cell
that I want the combo in. However I can't work out how to do this, from
what I have read the dropdown method is the same as the mouse down
event but I can't seem to call this to hide the list.

Any help, suggestions, anything at all...?

Thanks in advance,

James

Bob Phillips wrote:
Did you turn screenupdating off?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
ups.com...
Hi,

I have a combobox from the control toolbox which I have placed on a
worksheet. I have some code that runs from the worksheet selection
change event, (basically I am replacing the in cell data validation
that I was using, the reason I went down this route was that the sheet
is zoomed to about 70% and at this level the validation is barealy
ledgible) the code is:

' Position the combobox to the activecell
ActiveSheet.CellMenu.Top = ActiveCell.Top + 18
ActiveSheet.CellMenu.Left = ActiveCell.Left
' Make the combobox visible
ActiveSheet.CellMenu.Visible = True
' Show the dropdown list
ActiveSheet.CellMenu.DropDown
' Make the combobox not have anything preselected
ActiveSheet.CellMenu.ListIndex = -1

If the user clicks on another cell, I just make the combobox inivisible
however this is where my problem occurs as the dropdown doesn't go
away, also if the combobox list is showing and I move to another
workbook the list follows as the user hasn't clicked anything on the
dropdown. The CellMenu is just a comob box that has an onclick event
that puts the value of the combobox in the activecell.

Any suggestions would be very much appreciated, cheers,

James