counting textbox values

methody

Well-known Member
Joined
Jun 17, 2002
Messages
841
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.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,108
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

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