PDA

View Full Version : OLEObjects Inside UserForm



D_Marcel
04-18-2014, 08:48 PM
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. :yes

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:

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

Thanks in advance!

Douglas

GTO
04-18-2014, 09:47 PM
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
04-19-2014, 03:39 AM
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
04-19-2014, 01:34 PM
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:


Sub Anything()

Call EnableControl(MyActiveXControl, False)

End Sub

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

Me.OLEObjects(ControlName).Enabled = Control

End Sub

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
04-19-2014, 01:37 PM
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
04-19-2014, 01:59 PM
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
04-19-2014, 02:25 PM
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
04-20-2014, 08:38 AM
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
04-20-2014, 11:50 AM
@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
04-20-2014, 01:27 PM
...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
04-20-2014, 03:59 PM
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