counting textbox values

methody

Well-known Member
Joined
Jun 17, 2002
Messages
853
Hello there
I am using 10 textboxes on a userform and then have an 'OK' button to enter the data. I am looking for a need way of saying 'If any of the txtbox values are equal or if they are empty then exit sub.
Any help appreciated.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,180
Office Version
  1. 365
Platform
  1. Windows
Are these the only textbox controls on the userform? What are the names of the controls? Are they the standard TextBox1, TextBox2, etc.?

Iterate through the Controls property and checks a prefix of names or put the controls into a control Array and iterate through it if you didn't use a common prefix.

You can put the values into an array and use one of various methods to remove duplicates. A search for the work Unique and Array may find a routine easy enough. If the Ubound(arrayname) of each is the same then no duplicates were removed. Collections and a Dictionary object are other methods that would work.

See if this gets you started.
Code:
Sub myControls()
  Dim c As Control
  Dim a() As Control
  Dim i As Long
  For Each c In UserForm1.Controls
    i = i + 1
    Debug.Print c.Name
    ReDim Preserve a(i)
    Set a(i) = c
    Debug.Print "a(" & i; ")", a(i).Name
  Next c
End Sub

Here is something that I have used to fill a control with unique values from a range. You can use a similar concept.
Code:
Sub FillCtrlUnique(myRange As Range, myControl As Control)
  Dim Coll As New Collection
  Dim var As Variant
  Dim cell As Range
   
  On Error Resume Next
  For Each cell In myRange
      Coll.Add Item:=cell.Value, key:=CStr(cell.Value)
  Next cell
  On Error GoTo 0
   
  With myControl
      .Clear
      For Each var In Coll
          .AddItem var
      Next var
  End With
  Set Coll = Nothing
End Sub
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,147
Messages
5,835,667
Members
430,373
Latest member
swartzfeger

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
Top