UserForm In Range Out of Range

[LaDdIe]

Hiya,

How do I automatically invoke a UserForm to appear if the user selects say
Cells C6:AG5 (Named range emp1), the UserForm I've made inputs a fixed value
into any cell in this range then moves active cell right, but also has
navigation buttons to move active cell left, I would like the UserForm to
UnLoad as soon as it reaches any cell outside of range.

Respectx

Laddie

 

[Tom Ogilvy]

Use the selectionchange event for the worksheet to show the form. (You will
need to check if it is already being shown.)

Use the code in the userform that changes the selection to determine if the
form should be dropped. Make sure you disable events before changing the
selection, then reenable them after the change is made.

http://www.cpearson.com/excel/events.htm

 

[Leith Ross]

Hiya,

How do I automatically invoke a UserForm to appear if the user selects say
Cells C6:AG5 (Named range emp1), the UserForm I've made inputs a fixed value
into any cell in this range then moves active cell right, but also has
navigation buttons to move active cell left, I would like the UserForm to
UnLoad as soon as it reaches any cell outside of range.

Respectx

Laddie

Hello Laddie,

Add this macro to the worksheet the range emp1 is on. Also be sure to
change the name of the user form in the code to the name you are using
before you install it.

Macro Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("emp1")) Is Nothing Then
UserForm1.Show
Else
On Error Resume Next
Unload UserForm1
Err.Clear
End If

End Sub

To install the Macro:
1) Change the user form name and copy the code using CTRL+C
2) Right Click on the name tab of the worksheet the macro will run on
3) Select "View Code" from the pop up menu
4) Paste the macro using CTRL+V
5) Save the macro with CTRL+S

Sincerely,
Leith Ross

 

[Tom Ogilvy]

I like Leiith's suggestion, but I need to add a bit more error ignoring to
get it to work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("emp1")) Is Nothing Then
On Error Resume Next
UserForm1.Show
On Error GoTo 0
Else
On Error Resume Next
Unload UserForm1
Err.Clear
End If

End Sub

 

[LaDdIe]

Thanks Tom

Use the selectionchange event for the worksheet to show the form. (You will
need to check if it is already being shown.)

Use the code in the userform that changes the selection to determine if the
form should be dropped. Make sure you disable events before changing the
selection, then reenable them after the change is made.

http://www.cpearson.com/excel/events.htm

 

[LaDdIe]

You Guys are Diamonds.

I like Leiith's suggestion, but I need to add a bit more error ignoring to
get it to work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("emp1")) Is Nothing Then
On Error Resume Next
UserForm1.Show
On Error GoTo 0
Else
On Error Resume Next
Unload UserForm1
Err.Clear
End If

End Sub

 

[LaDdIe]

Thanks Leith AAAAAAAAAAAAAAA++++++++++++++++

Hello Laddie,

Add this macro to the worksheet the range emp1 is on. Also be sure to
change the name of the user form in the code to the name you are using
before you install it.

Macro Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("emp1")) Is Nothing Then
UserForm1.Show
Else
On Error Resume Next
Unload UserForm1
Err.Clear
End If

End Sub

To install the Macro:
1) Change the user form name and copy the code using CTRL+C
2) Right Click on the name tab of the worksheet the macro will run on
3) Select "View Code" from the pop up menu
4) Paste the macro using CTRL+V
5) Save the macro with CTRL+S

Sincerely,
Leith Ross