show Userform when Activecell in table Listcolumn range

Boffa

New Member
Joined
May 8, 2019
Messages
27
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...
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Use code like this:
VBA Code:
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
 
Upvote 0
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
 
Upvote 0
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 ..
 
Upvote 0
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

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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