Help with Object Code...i'm missing something simple...

[TheNoocH]

I have the following code and get the error ....Invalid use of Null and debug sets it on the line that starts If Len(Trim......

I believe the issue is in the line that starts With Me.Controls.....i checked this evaluation in the immediates window and it comes back null...

I have 9 combo boxes named cmbOption01 to cmbOption09...if i hard code cmbOption01 everything works fine but using the variables i get the issue...i need it to loop through all 9 to make sure not duplicated...any help would be great....i've been at this one and going in circles for hours....thanks...

Private Function CheckDuplicateVal(Cnt As Long) As Boolean
Dim lngTemp As Long
Dim strTemp As String
  strTemp = ","
  For lngTemp = 1 To Cnt
    With Me.Controls("cmbOption" & Format(lngTemp, "00"))
      If Len(Trim$(.Value)) > 0 Then
        If InStr(strTemp, UCase$(.Value) & ",") > 0 Then
          MsgBox "Duplicate Value: " & .Value, vbInformation
          CheckDuplicateVal = False
          Exit Function
        Else
          strTemp = strTemp & UCase$(.Value) & ","
        End If
      End If
    End With
  Next lngTemp
  CheckDuplicateVal = True
End Function 'CheckDuplicateVal

[/code]

 

[mdmilner]

Take a peek here. I realize you're trying to create control groups with your naming syntax, but you could try a slightly longer method. Open up all the form controls and look for partial matches where the first few characters are your name ("cmbOption") and then if it finds a hit, do something with it. You can ignore the numeric values at the end.

http://www.mrexcel.com/board2/viewtopic.php?t=117740&highlight=

Mike

 

[TheNoocH]

Mike, I'm trying to get the line Me.Controls.....to recognize cmbOption01, etc. as a control/object...right now it's returning NULL...i just think my syntax or something is off ....any help would be great...

 

[mdmilner]

What I meant was to use a method that completely bypasses whether you have the exact spelling of the name or the referencing. This would actually go through ALL your controls but the If statement controls whether an action is performed.

For x = 0 To Forms!frmSelect.Controls.Count - 1 
   If Left(Forms!frmSelect.Controls.Item(x).Name,9) = "CmbOption" Then
      'You've found a valid control name
      'Now do something with it
   end If
Next x

You can also do it this way:

Dim ctr As Control

For Each ctr In Forms!frmSelect.Controls
  If Left(ctr.Name,9) = "CmbOption" Then
     ' You've found a valid control to work with
     ' You can now work directly with the control properties by referencing
     ' ctr.property_name
  End If
Next

And yes, Me.Controls will substitute for Forms!frmSelect.Controls -- I had my code in a generic module when I was testing.

Mike