show Userform when Activecell in table Listcolumn range

[Boffa]

I have an excel table that a user works in and inputs data.

When the user tabs (Or Clicks) into a particular cell within a Particular Column of that Table I would like for a Userform to Automatically show and if the User Tabs (Or Clicks) away from that column for the Userform to close

Something like (Which doesn't work)

Sub Table_Test()

Dim lo As ListObject

Set lo = SheetTest.ListObjects("tblTest")

If ActiveCell = lo.ListColumns("Product2").DataBodyRange Then
f_Test.Show
Else
Unload Me
End If

End Sub

Is this Possible ? Any Ideas...

 

[jkpieterse]

Use code like this:

Option Explicit
Dim TheForm As f_test
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Me.ListObjects("tblTest").ListColumns("Product2").Range, Target) Is Nothing Then
If TheForm Is Nothing Then
Set TheForm = New f_test
End If
TheForm.Show vbModeless
Else
On Error Resume Next
Unload TheForm
Set TheForm = Nothing
End If
End Sub

 

[Boffa]

Thanks for the quick response jkpieterse the form is appearing fine using your code however is not unloading when I click away?

I'll have to add a condition I think as currently when the form shows, if I click away and I again click in a cell in the same column it loads the form again

 

[jkpieterse]

Currently it only goes away if you click on another column inside the table I expect

 

[Boffa]

No it stays shown when I click on another column inside the table - But if I click in the same column it will load the form again

So just need to ensure it unloads when I click on another column inside the table and if there is already once instance of the form showing if I click in the same column it does not load another instance ..

 

[Boffa]

Have slightly modified your code jkpieterse and have got it to do what I want for now - thanks for your help and setting me on the right path

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Me.ListObjects("tblTest").ListColumns("Product2").Range, Target) Is Nothing Then
  f_Test.Show vbModeless
Else
On Error Resume Next
  If Intersect(Me.ListObjects("tblTest").ListColumns("Product2").Range, Target) Is Nothing Then
    Unload f_Test
  End If
End If
End Sub