Array for ActiveX Controls

[D_Marcel]

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]

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]

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]

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]

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]

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]

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]

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!