PDA

View Full Version : Array for ActiveX Controls



D_Marcel
03-19-2014, 09:07 PM
Experts,

Greetings to all!
I'm using some ActiveX controls to control the registers in a Worksheet, creating, editing and deleting them.
There are some functionalities that requires that some buttons are disabled. For example, if I press the button 'Edit', the buttons 'Save' and 'Delete' should be enabled. If I press 'Edit' again, then disable 'Save' and 'Delete'.

The problem is that I have many routines that change the value of the property 'Enabled', and instead of writing to each button:

TextBox1.Enabled = True

I'm trying to use a Public Sub to receive the name of the fields as parameters and then change the value of this property, but it's not that simple. If I try, for example:

Private Sub CommandButton1_Click()


Dim Element As Variant
Dim Export() As Variant


Export() = Array("Button1", "Button2", "Button3")


For Each Element In Export
Element.Enabled = True 'this can't be done
Next Element


End Sub

So, I guess I need to use a class, but as I never did it, I really have no idea.

Can you please help me, giving some directions?

Thanks in advance friends.

Regards,

Douglas

GTO
03-19-2014, 11:37 PM
Hi there,

I may be reading this wrong, but if you are referring to activex command buttons on a worksheet, maybe something along the lines of:


Option Explicit

Private Sub CommandButton1_Click()
Dim obj As OLEObject

For Each obj In Me.OLEObjects
If TypeName(obj.Object) = "CommandButton" Then
If Not obj.Name = "CommandButton1" Then
obj.Object.Enabled = Not obj.Object.Enabled
End If
End If
Next

End Sub

...to dis/re enable any commandbuttons except for CommandButton1.

Hope thta helps,

Mark

snb
03-20-2014, 02:04 AM
Private sub c_Edit_Click()
M_check c_edit.name
End Sub

Private sub c_Save_Click()
M_check c_Save.name
End Sub

Private sub c_Delete_Click()
M_check c_Delete.name
End Sub

Private sub M_check(c00)
for each it in array("c_Edit","c_Save","c_Delete")
oleobjects(it).enabled=oleobjects(it).name=c00
next
End Sub

Bob Phillips
03-20-2014, 02:32 AM
I would be far more explicit, in that I would have a procedure that sets ALL controls based upon settings passed to that procedure, and anytime I wanted to set one or more I would call that procedure with the settings that I wanted for every control.

The code below shows what I am saying for just 3 buttons, Edit, Save and Delete; you would extend it to the other controls.


Public Function ControlsStatus( _
ByRef sh As Worksheet, _
ByVal EditButton As Boolean, _
ByVal SaveButton As Boolean, _
ByVal DeleteButton As Boolean)

With sh

.OLEObjects("btnEdit").Enabled = EditButton
.OLEObjects("btnSave").Enabled = SaveButton
.OLEObjects("btnDelete").Enabled = DeleteButton
'etc.
End With
End Function

Sub TestEdit()
Call ControlsStatus(ActiveSheet, True, False, False)
End Sub
Sub TestEdit2()
Call ControlsStatus(ActiveSheet, False, True, True)
End Sub

jonh
03-20-2014, 05:14 AM
Might be better to pass your controls to a handler and use select cases.


Private Sub btnEdit_Click()
ControlHandler btnEdit
End Sub

Private Sub btnDelete_Click()
ControlHandler btnDelete
End Sub

Sub ControlHandler(c As Control)
Select Case TypeName(c)
Case "CommandButton": BtnHandler c
Case "ComboBox"
Case "TextBox"
End Select
End Sub

Sub BtnHandler(c As Control)
Select Case c.Caption
Case "Edit"
c.Caption = "Save"
btnDelete.Visible = True
Case "Save"
c.Caption = "Edit"
btnDelete.Visible = False
Case "Delete"
c.Visible = False
btnEdit.Caption = "Edit"
Case "Undo","Redo"

Case "Move Next","Move Previous"

End Select
End Sub

D_Marcel
03-21-2014, 08:08 AM
Many suggestions in so little time. You all are outstanding, Mark, SNB, XLD and Jonh, thank you very much guys, helped me a lot.

I read mindfully each code in order to understand as clear as possible their logic, and then I decided to use elements from the suggestions of Mark and XLD, reaching this:

Private Sub NewButton_Click()


Dim Enable() As Variant
Dim Disable() As Variant


Application.ScreenUpdating = False


Enable = Array("Button1", "Button2", "Button3", "Button4")
Call EnableControl(Enable, True)


Disable = Array("FirstButton", "BackButton", "NextButton", "LastButton", "EditButton", "ComuAllButton")
Call EnableControl(Disable, False)


Application.ScreenUpdating = True


End Sub


Public Sub EnableControl(ByRef Import As Variant, Action As Boolean)


Dim Control As OLEObject
Dim Element As Variant


For Each Control In Me.OLEObjects
For Each Element In Import
If Control.Name = Element Then
Control.Enabled = Action
End If
Next Element
Next Control


End Sub

Using these arrays, I can filter only the buttons whose property I want to change.

Douglas

Bob Phillips
03-21-2014, 08:18 AM
That is perfect, a number of options, and you mixed and matched to come up with what works for you (and I have to say I think it is better than my suggestion, just as clear but more succinct :))

I would suggest one small change, loop the input array rather than the controls, avoiding the double loop and it will avoid testing controls you aren't interested in.


Public Sub EnableControl(ByRef Import As Variant, Action As Boolean)
Dim i As Variant

For Each i In Import

Me.OLEObjects(i).Enabled = Action
Next i
End Sub

D_Marcel
03-21-2014, 10:12 AM
Perfect XLD!

If I had many more ActiveX controls, surely this double loop would compromise the performance, but this way works much faster. :yes

Thanks again!