OLEObjects Inside UserForm

[D_Marcel]

Greetings Gurus,

I've been working in a small project that uses some ActiveX controls in a WorkSheet. As I was programming these controls, I came across with several repetitive operations, so I tried as much as I could, use Public Functions and Subs, which ones wouldn't have the desired results without the method "Me.OLEObjects". This one I learned here in the community.

However, I decided to try to use a UserForm instead of the ActiveX controls in a Worksheet, and when I was performing the first test, I realized that this method cannot be used inside the UserForm, the message "Method or Data member not found(Error 461)" is issued.

I researched about but found nothing.
Does anyone know how can I replace this feature inside the UserForm?

This is an example:

[VBA]Public Sub PopulateComboBox(ByVal ComboBoxName As String)

Dim Entry, WorkArea As Range


Set WorkArea = Plan1.Range(Cells(18, 2), Cells(Cells(Rows.Count, 2).End(xlUp).Row, 2))


For Each Entry In WorkArea
Me.OLEObjects(ComboBoxName).Object.AddItem Entry.Value
Next Entry


Set WorkArea = Nothing


End Sub[/VBA]

Thanks in advance!

Douglas

[GTO]

Maybe like:

Option Explicit
    
Private Sub UserForm_Initialize()
Dim WorkArea As Range, Entry As Range, LastCell As Range
    
    With Plan1
        
        Set LastCell = .Range("B:B").Cells(.Rows.Count).End(xlUp)
        
        If LastCell.Row >= 18 Then
            
            Set WorkArea = .Range(.Cells(18, 2), LastCell)
            
            For Each Entry In WorkArea.Cells
                
                If Not Entry.Value = vbNullString Then
                    Me.ComboBox1.AddItem Entry.Value
                End If
                
            Next
            
        End If
        
    End With
    
End Sub

[snb]

Avoid additem to populate a combobox/listbox

Private sub Userform_initialize()
   combobox1.list= Plan1.Range(Cells(18, 2), plan1.Cells(Rows.Count, 2).End(xlUp)).Value 
End Sub

You might have a look at

http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

Oleobjects are activeX-controls in a worksheet

The userformcontrols are different.

[D_Marcel]

Thanks a lot Mark and snb!

Well, in both examples, we would have to define exactly which control we're working to, right? What I mean is that in the UserForm, maybe there's no way to "abstract" the control to send it through a Function or another Sub.
Considering that I want to change a property that is applicable to all controls, the "Enabled" property, I would do this:


[VBA]Sub Anything()

Call EnableControl(MyActiveXControl, False)

End Sub

Public Sub EnableControl(ControlName as String, Control as Boolean)

Me.OLEObjects(ControlName).Enabled = Control

End Sub[/VBA]

In the UserForm, if I have to do the same to, say, 3 controls, would I have to do this?

Control1.Enabled = True
Control2.Enabled = True
Control3.Enabled = False

I'm trying to find a method which one allows me to change any control in the UserForm.

Douglas

[D_Marcel]

snb, I forgot to mention, this page that you recommended is amazing, there is dozens of valuable examples there to populate comboboxes, certainly I'll save in my favourites!

[D_Marcel]

Researching with the key words "excel vba manipulate controls userform", I found a page that contains some examples with the command "Me.Controls". I replaced all the "Me.OLEObjects" and now everything is working.


Thanks guys!


Douglas

[snb]

in any sub in the userform's codemodule:

sub M_snb()
  for each ct in controls
    ct.enabled=true
  next
End Sub

less simple

sub M_snb()
  for j=0 to controls.count-1
    controls(j).enabled=true
  next
End Sub

less simple

sub M_snb()
  for j=1 to controls.count
    Me("combobox" & j ).enabled=true
  next
End Sub

less simple

sub M_snb()
  for j=1 to controls.count
    Controls("combobox" & j ).enabled=true
  next
End Sub

[SamT]

Most UserForms have three categories of Controls; Input/Output, Command Buttons and Labels.

I will add the I/O controls to a custom Collection.

For Each Ctrl in Me.Ctrls
If Not Left(Ctrl.Name, 3) = "lbl" Then
ioControls.Add Ctrl

[snb]

@Sam


A control's name isn't a reliable source to determine it's character; typename is.
I'd prefer:

sub M_snb()
  for each ct in controls
    if typename(ct)="ComboBox" then ....
  next
End Sub

[GTO]

...A control's name isn't a reliable source to determine it's character;...I'd prefer:

'"Isn't" states as fact. While I agree that TypeName is a reliable method, .Name or .Tag, would logically seem as reliable as the form's/controls' author.

[SamT]

snb,

You are programmatically correct. The use of a custom Collection for controls means that the program designer must determine the character of the control. For example, I have used a label as an output control for informative displays. IIRC, that control was named ioRecordNumber, which would pass that little test in the above code.

Sometimes the test must check for many things to include or not. However, the "test for inclusion" was not the point I was trying to make, which is that sometimes coding a Data structure such as an Enumeration, a Collection, an Array, or a Dictionary, can lead to code that is easier to read, maintain, and use.

That is something I developed from another thing you taught me. "Data Precedes Process." So, if a column is headed with "Customer Last Name, that Range is named Cust_Last_Name and the TextBox is named tbxCust_Last_Name. However, "tbx" is a Process solution, (to a very small issue at that,) and I wanted a Data solution, so I changed "tbx", "lbx," and "cbo" to "rs," and stuck them all in the RSControls Collection.

The code to transfer the data to the sheet was similar to

For Each Ctrl in RSControls
Intersect(Row(ioRecordNumber +1 ), Range(Right(CtrlName, Len(Ctrl.Name) - 2))) = Ctrl.Value

Not as fast as an Array or Dictionary, but with today's CPUs and Memory, plenty fast enough.

If, in the future, the customer wants to change the database, then by using a structured formation of controls, the only code changes required are to change the affected Control's Name