Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unload User Form
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unload User Form
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. -- Regards, Tom Ogilvy "Partho" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unload User Form
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" wrote: 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. -- Regards, Tom Ogilvy "Partho" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unload User Form
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" wrote in message ... 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" wrote: 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. -- Regards, Tom Ogilvy "Partho" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unload User Form
Tom,
AppActivate fixes my problem! Thanks for your help! Partho "Tom Ogilvy" wrote: 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" wrote in message ... 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" wrote: 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. -- Regards, Tom Ogilvy "Partho" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to unload a form in excel vba? | Excel Programming | |||
Unload the form on esc key | Excel Programming | |||
Load and Unload Form commands | Excel Programming | |||
form won't unload | Excel Programming | |||
Form Unload | Excel Programming |