Can someone help me!! Very samll doubt.

[tv9_rohith]

I have 2 Command buttons in excel sheet.

for activation of command button1 I have given the formula as :

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveSheet.Range("B10") = "" Then
ActiveSheet.CommandButton1.Enabled = False
Else
ActiveSheet.CommandButton1.Enabled = True
End If
End Sub

Now I have one more command button where I would like to give some parameters for the activation of command button that is

If from Cell D2 to K2( 1 Row ) there is value then the command button 2 should be active or else it should be inactive.

Please help me

 

[craig.penny]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
 
If ActiveSheet.Range("B10") = "" Then
   ActiveSheet.CommandButton1.Enabled = False
Else
   ActiveSheet.CommandButton1.Enabled = True
End If
 
If ActiveSheet.Range("D2") = "" And _
       ActiveSheet.Range("E2") = "" And _
       ActiveSheet.Range("F2") = "" And _
       ActiveSheet.Range("G2") = "" And _
       ActiveSheet.Range("H2") = "" And _
       ActiveSheet.Range("I2") = "" And _
       ActiveSheet.Range("J2") = "" And _
       ActiveSheet.Range("K2") = "" Then
   ActiveSheet.CommandButton2.Enabled = False
Else
   ActiveSheet.CommandButton2.Enabled = True
End If
 
End Sub

I'm sure there are 100 better ways to do it but this should work.

 

[craig.penny]

Did you give this a try?

 

[tv9_rohith]

Sorry I tried it today and I am not getting the correct result.

The result I expect from this is -

Only if Cell D2, E2,F2,G2,H2,I2,J2,K2 has values in it then command button 2 should be active or else it should be deactive.


But with the formula you have given me the result is -

If any one of the cell( D2, E2,F2,G2,H2,I2,J2,K2 ) has a value in it then the command button 2 is getting active but which should not be.

Instead it should check all the cell values and only if all the cells have value then it should get activated.

 

[VoG]

Try

ActiveSheet.CommandButton2.Enabled = WorksheetFunction.CountA(Range("E2:K2")) = 7

 

[tv9_rohith]

this is working for Command button 2.

can I give 3 Cells value in for Command button 1 then how will I do it.

B10,B13,B19 then the Command button 1 should be active.


<!-- / message --><!-- sig -->

 

[craig.penny]

Private Sub Worksheet_Change(ByVal Target As Range)
 
If ActiveSheet.Range("B10") = "" Then
   ActiveSheet.CommandButton1.Enabled = False
Else
   ActiveSheet.CommandButton1.Enabled = True
End If
 
If ActiveSheet.Range("D2") = "" Or _
       ActiveSheet.Range("E2") = "" Or _
       ActiveSheet.Range("F2") = "" Or _
       ActiveSheet.Range("G2") = "" Or _
       ActiveSheet.Range("H2") = "" Or _
       ActiveSheet.Range("I2") = "" Or _
       ActiveSheet.Range("J2") = "" Or _
       ActiveSheet.Range("K2") = "" Then
   ActiveSheet.CommandButton2.Enabled = False
Else
   ActiveSheet.CommandButton2.Enabled = True
End If
 
End Sub

 

[craig.penny]

So... did this work?