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:

[VBA]TextBox1.Enabled = True[/VBA]

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:

[VBA]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[/VBA]

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:

[VBA]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[/VBA]

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.

Thanks again!