Displaying a user form when cell is selected

LadJets

New Member
Joined
Jul 21, 2002
Messages
13
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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>
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
<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>
 
Upvote 0

Forum statistics

Threads
1,223,566
Messages
6,173,097
Members
452,502
Latest member
glindanarvilla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top