Checking entry of textboxes as unique values - class module

[dlee83]

Hi,

I can write a bit of code to run through the afterupdates on textboxes within a userform to check if the entry is unique among the other textboxes. But I was wondering if it's possible to put that query into a class module?

Thanks for your help

 

[saboh12617]

Hello,

Can you elaborate? What is the point of using a class if it is just for that? A simple loop test would be easier.

However, if you want to parametrize your Userform then you can of course link it to a class which is very interesting, but it will require some work. Also the guideline is that the class will do all the work, and the userform is just an interface, calling the class methods.

But all of this setup is useful when you have a structure behind, what do your textboxes represent, what is displayed on the userform etc…

 

[dlee83]

Yeah, writing the loop in the afterupdate or button command is fine, i can write that. I was just wondering if there's a simple class module that can be made so you can easily transfer that module to another workbook if i needed the same thing

 

[saboh12617]

Hello back,

I think you could make a simple function for this purpose, which iterates over the controls of the userform and returns True if the compared value is unique among the others, and false otherwise. Something like this:
It is easy to copy/paste/adapt, and less hustle than making a class

Public Function IsUnique(entry As Variant, ctrls As MSForms.Controls) As Boolean
  Dim ctrl As MSForms.Control, txtB As MSForms.TextBox
  For Each ctrl In ctrls
    ' test to get only specific controls to compare
    If (TypeOf ctrl Is MSForms.TextBox) And (ctrl.Name Like "*unique*") Then
      Set txtB = ctrl
      ' comparison
      If txtB.Value = entry Then
        IsUnique = False
        Exit Function
      End If
    End If
  Next ctrl
  IsUnique = True
End Function

 

[dlee83]

Thank you. I adapted it slightly to declare the name of the textbox so as the function doesn't look at the original textbox entry and return false

Function IsUnique(entry As Variant, entryname As Variant, ctrls As msforms.Controls) As Boolean
Dim ctrl As msforms.Control, txtB As msforms.TextBox
For Each ctrl In ctrls
' test to get only specific controls to compare
If (TypeOf ctrl Is msforms.TextBox) And (ctrl.Name <> entryname) Then
Set txtB = ctrl
' comparison
If txtB.value = entry Then
IsUnique = False
Exit Function
End If
End If
Next ctrl
IsUnique = True
End Function

 

[saboh12617]

Ottimo!
Glad to help and to see you managed to adapt it to your case. Have a good day.