Command Buttons and worksheet Change Events

[stats1]

Hi,

I was really hoping someone could help me. I have only delved into the world of VBA the past month or so and whilst I seem to be grasping the concepts fairly well, or with the support of Google and these forums if I'm honest, I have come across a situation which has bme stumped. Let me set the scene....

I have 10 command buttons on my excel sheet. If cell A1 is empty then I want CommandButton1 to be disabled. However, once there is a value in cell A1 I want CommandButton1 to be enabled and allow the user to then click and run the next macro. Now, I have successfully implemented a worksheet change event that allows me to do this.

However, the complexity occurs when I consider my other CommandButtons. For example, when cell B1 is empty I want CommandButton2 to be disabled but once it has a value to be enabled. When cell C1 is empty CommandButton3 to be disabled and when it has a value enabled. It could be that A! and B1 will have a balue and therefore CommandButtons 1 and 2 will need to be enabled whilst CommandButton3 is left disabled.

When I try to add the code underneath for the other buttons, it still only works for CommandButton1 but none of the other buttons. I'm struggling to find a way to have all these worksheet change events under the one input.

Can anyone please help me? Pretty please!!!!

 

[dmt32]

Hi,
Give this a try & see if helps you:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:J1")) Is Nothing Then
        Me.OLEObjects("CommandButton" & Target.Column).Object.Enabled = Len(Target.Value) > 0
    End If
End Sub

Solution assumes that your values are in A1:J1 & are updated by user (not formula)
Also, that your Buttons are CommandButtons (Active X) with their default names CommandButton1, CommandButton2 etc.

Hope Helpful

Dave