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

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

--
Regards,
Tom Ogilvy


"LaDdIe" wrote:

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

Thanks Tom

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"LaDdIe" wrote:

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

On Jun 11, 12:19 pm, LaDdIe wrote:
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

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


--
Regards,
Tom Ogilvy


"Leith Ross" wrote:

On Jun 11, 12:19 pm, LaDdIe wrote:
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

You Guys are Diamonds.

"Tom Ogilvy" wrote:

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


--
Regards,
Tom Ogilvy


"Leith Ross" wrote:

On Jun 11, 12:19 pm, LaDdIe wrote:
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

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

"Leith Ross" wrote:

On Jun 11, 12:19 pm, LaDdIe wrote:
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