Displaying a user form when cell is selected

[LadJets]

Im looking to display a user form when any cell between row 10 and 65 in column 13 are selected.

Following is currently used



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column <> 13 And Target.Row > 9 And Target.Row < 66 Then Exit Sub
userformf50.Show

End Sub



It works for what I want but if i select any cells above row 10 or below row 65 in any column it also displays the user form.

It is also possible that when i move off the selected cell that the user form would automatically hide itself again.

Thanks for any help

Ladjets

 

[dsnbld]

Give this a try and see if it helps.

<pre>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(ActiveCell, [M10:M65]) Is Nothing Then
MsgBox "Test successfull" 'put your code here
End If
End Sub</pre>

 

[Juan Pablo González]

I imagine that you're using Excel 2000 or XP, to have non modal forms, right ?

If so, you would use the same SelectionChange event to Unload or Hide the userform, maybe even just addin the "Else" statement to your If condition.

If MyRangeCondition then
Userform1.show
Else
Unload Userform1
End If

 

[Swamp Thing]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column <> 13 Then Exit Sub

If Not(Target.Row > 9 And Target.Row < 66)Then Exit Sub

userformf50.Show

End Sub

 

[LadJets]

Thanks for the above. They all work great.

There is one more thing that you might be able to help me with.

Now that the above works the only problem is that when the user form is displayed the cell that i am on is no longer active.

It is possible to reactive the cell without clicking on it?

In other words i move to the cell i want and a user form is displayed in the top left of the screen. After getting the information i need i would like to type the info into this cell without having to click to reactive it.
When I am finished i move to the next column and the form disappears.

Many thanks one again

Ladjets

 

[dsnbld]

<pre>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(ActiveCell, [A1:A10]) Is Nothing Then
UserForm1.Show
AppActivate "Microsoft Excel"
Else
Unload UserForm1
End If
End Sub</Pre>