Making control invisible crashes Excel

[James Duncan]

Hi there,

I'm trying to make a combobox act like a normal Excel
cell. It's default property is set to Visible = False and
the control is located directly over cell D2. The
LinkedCell property is set to D2. When the user tabs (or
uses the right arrow) onto this cell the ComboBox becomes
visible and is set to active. I have also writen some
code that detects when Tab and Shift-Tab is pressed,
which moves the cursor onto the next or previous Excel
cell.

However, I also want the ComboBox control at this point
to become invisible. However, all my attempts to achieve
this have resulted in failure and worse than that it
crashes Excel. I assume this is because I'm trying to set
ComboBox1.Visible = False whilst the ComboBox is still
active/executing code?

I've tried every possible way I can think of to achieve
this, including Lost_Focus, etc. All result in Excel
crashing.

Please see my code below to see what I have achieved to
date, which isn't ideal. The control becomes invisible if
the user uses the mouse to click on any other cell or if
they press tab twice, etc.

Any help would be greatly appreciated.

Many thanks

James

Code follows:

Private Sub ComboBox1_KeyDown(ByVal KeyCode As
MSForms.ReturnInteger, ByVal Shift As Integer)
' required to prevent SelectChange from running,
which sets ComboBox1 to Visible = False,
' which crashes Excel
Application.EnableEvents = False
If KeyCode = 9 And Shift = 1 Then
ActiveCell.Offset(0, -1).Select
'ComboBox1.Visible = False
Else
If KeyCode = 9 Then
ActiveCell.Offset(0, 1).Select
'ComboBox1.Visible = False
End If
End If
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
If Application.Intersect(Target, Range("D22")) Is
Nothing Then
If ComboBox1.Visible = True Then
ComboBox1.Visible = False
Else
ComboBox1.Visible = True
ComboBox1.Activate
If ComboBox1.ListCount > 0 And ComboBox1.Text
= "" Then
' select first item in the list - if i can
remember how!
End If
End If
End Sub

 

[Stephen Bye]

It sounds like you are going to an awful lot of trouble to simulate what the
Data Validation facility already does. Why not just dump the code and use
Data Validation instead?

 

[James Duncan]

Mainly because I didn't know that Data Validation allowed
you to use a list

However, I have just played with Data Validation and I
prefer my approach for several reasons. Firstly, you can
paste into the Data Validation cell and the data
validation isn't applied. Secondly, unlike my combobox
you have to click on the list with the mouse to select an
item or type it in full, whereas the combobox allows you
to press the first character and matches the entry, etc.
The Combobox also looks better

Shame the Data Validation doesn't allow you to use a
ComboBox rather than just a list.

I'd still like to solve this one so I can use my Combobox
solution

Thanks

James