PDA

View Full Version : Solved: VBA Excel 2003 escape listbox control on worksheet



2ManyTimes
10-25-2007, 03:26 PM
I have a listbox control on the attached worksheet with visibility set to false. I want to be able enter or change a cell's contents at which time the listbox would appear and be activated, requiring a selection to be made. If I press "ESC" or select another, I would like the listbox to disappear and the changed cell's value to revert to its original value. Sounds simple enough, but I can't get it to work. Can someone help?

See the code I have in worksheet class module and try typing a value in cell and then press "ESC".

Norie
10-25-2007, 03:57 PM
I can't find any listboxes in that workbook.:dunno

2ManyTimes
10-25-2007, 04:04 PM
The visibility is set to false for the listbox. If you're using excel 2007, click developer tab, then design mode and it will appear. If you're using excel 2003, click view, toolbars, then click, msforms or activex controls. Should put you in design mode and you can see listbox

Bob Phillips
10-26-2007, 08:05 AM
Option Explicit

Private rTarget As Range
Private strOrigValue
Private bUserChange As Boolean


Private Sub ListBox1_Change()
rTarget.Activate

End Sub

Private Sub ListBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = 27 Then
rTarget.Value = strOrigValue
End If
End Sub

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
rTarget.Activate
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit
Application.EnableEvents = False
Me.ListBox1.Visible = True
Me.ListBox1.Activate 'set focus on listbox
bUserChange = True
ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not bUserChange Then
strOrigValue = Target.Value
Set rTarget = Target
Else
bUserChange = False
End If
End Sub

Private Sub ListBox1_LostFocus()
Me.ListBox1.Visible = False
End Sub

XLGibbs
10-28-2007, 04:27 PM
2ManyTimes.

I received your private message. The code in your uploaded file was missing one critical piece in the KeyUp code which captures the esc key press

Target.value = strOrigValue


I replaced your entire code with xld's entire posted code, and it works as you describe you wanted it to in your post.