VBA: Referencing a table and its column in Useform[SOLVED]

[inddon]

Hello There,

The purpose of the below exercise is how to reference a table and its columns in a Userform:

I have a table defined with columns Name, Age, Amount1, Amount2, Amount3. A button is created to bring up the userform. This form contains a textbox and it needs to be filled with a value 'X'.

The requirement:
1. For a single row, if the columns Name & Age are filled and the User clicks on the button , the form should be displayed and the textbox gets a value 'X', else it should give a friendly message 'Textbox filled only when Name and Age is filled for the given row'

2. If on the table the user selects multiple rows or columns in the table, and then presses the button, it should give the above friendly message.

I have attached the file for your your reference.

Could you please advise, how this can be achieved using VBA code written in modules?

Looking forward to hearing from you.

Regards
Don

 

[Somendra Misra]

Hi Don,

See the file.

Regards,

 

[inddon]

Hi Don,

See the file.

Regards,

Hello Somendra,

Thank you, it works good. Learned something new from you today as well as about Public Function.

Instead of using column B & C as below, is there a way to use the table's column name an how can that be done?:

Public Function checkstatus() As Boolean

Dim ws As Worksheet
Dim rng As Range

Set ws = ThisWorkbook.Worksheets("Sheet1")
'ws.Range("table1").Select
Set rng = Selection
If rng.Count = 1 Then
  If Not Intersect(ws.Range("Table1"), rng) Is Nothing Then
  If Range("B" & rng.Row) <> "" And Range("c" & rng.Row) <> "" Then
  checkstatus = True
  Else
  MsgBox "Textbox filled only when Name and Age is filled for the given row"
  checkstatus = False
  End If
  End If
   
  Else
  If Not Intersect(ws.Range("Table1"), Selection) Is Nothing Then
  MsgBox "Textbox filled only when Name and Age is filled for the given row"
  checkstatus = False
  End If
  End If

End Function

Regards
Don

 

[Somendra Misra]

@inddon

See the file.

Regards,

 

[inddon]

@inddon

See the file.

Regards,

Wonderful Somendra. Thank you for your guidance.


Regards
Don

PS> When the solution is complete, how do we tag the post as "Solved"?

 

[Somendra Misra]

Glad you like it & thanks for the feedback, you have to edit the title, but I think there is some time limit to do it. I had done it for you.

Regards,