Unload User Form

[Partho]

I have code which displays a user form when a particular cell is activated.
The user may then select a value from a list box in the user form which then
appears in that cell. The user form then unloads when OK/cancel button in
User Form is selected.
However, the user may activate that cell and then choose not to update the
cell value. In this situation I would like the user form to unload without
the user having to click any button/control in the user form but merely by
hitting any arrow key in the keyboard.
Any help would be much appreciated.

The code currently is:

In sheet module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("xxxx").Address Then
SelectAccount
Else: Unload UserForm1
End If
End Sub

In main module:
Sub SelectAccount()
UserForm1.ListBox1.RowSource = Range("xxxxList").Address
UserForm1.ListBox1.ControlSource = Range("xxxx").Address
With UserForm1
.Show vbModeless
.Move 450, 100
End With
End Sub

The code attached to the OK/Cancel buttons is just:
Unload UserForm1

Thanks,
Partho

 

[Tom Ogilvy]

So what isn't working? If you change selection (using an arrow key or
otherwise) and it isn't to cell Range("xxxx") then it should unload
Userform1, even if it has to load it first.

 

[Partho]

Tom,

The problem is that when the cell is activated and the user form appears,
the user form is the active window. Therefore, when I hit up/down arrow, I
move between the controls in the user form rather than to the next cell in
the worksheet. ideally I would like the user form to be in the 'background'
until the user actually clicks in it.

Partho

 

[Tom Ogilvy]

You can use AppActivate to re-activate Excel after you show the
form. E.g., something like


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$C$3" Then
UserForm1.Show vbModeless
AppActivate Application.Caption
End If
End Sub


Adjust to your code.

Of course if the user wants to enter a value, then they would have to click
in the form.



--

Regards,

Tom Ogilvy

 

[Partho]

Tom,

AppActivate fixes my problem! Thanks for your help!

Partho