Command Button should be active only when the listed criteria is fullfilled

[Rohith1324]

Hi,

I have Macro enabled excel sheet where it will loop based on A Column and takes input from the row till J Column ( A1 to J1 is one user record completely ) and triggers the action.

Can we someone help me with VBA code which will not enable the command button until all the fields are filled:

for Example :

if A1 is filled so user has to ensure the data is filled till the entire row till J1 ( 1 user record completely )
if A2 to A100 ( as an example) is filled but the entire respective row is not filled and user tries to click on command button then commnad button should be disabled.

Only when the Entire row is filled the user should have access to command button.

Please help

Regards,
Rohit

 

[mikerickson]

If A1:J1 are filled, then that row is ready to go.
If A2:G1 are filled, but H2:J2 not, that row is not ready to do.

When the user presses the button, what should happen?

Another way to ask my question is, when the user presses the button, which row should the routine look at to see if it should take action?

 

[Rick Rothstein]

Assuming you are using an ActiveX CommandButton named CommandButton1 (change the code to match its actual name), this Change event code procedure will disable the CommandButton whenever there is a blank cell within Columns A:J from Row 1 on down to the last cell within Columns A:J with data. So, if the user deletes the contents of a cell on a row that was already full between Columns A to J, the CommandButton will become disabled until that cell is given data again. Also, if the user enters a value on the row immediately under the last filled out row, the button will become disabled until that full row from Columns A to J is filled in. Note that this proposal does not require you to do anything different with the code being executed when the CommandButton is clicked. If that is the kind of functionality you are looking for, then here is the Change event procedure to accomplish it...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim LastRow As Long, Cnt As Long
  If Not Intersect(Target, Columns("A:J")) Is Nothing Then
    On Error GoTo Whoops
    LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    ActiveSheet.CommandButton1.Enabled = Evaluate("COUNTBLANK(A1:J" & LastRow & ")=0")
    On Error GoTo 0
  End If
  Exit Sub
Whoops:
  ActiveSheet.CommandButton1.Enabled = False
  On Error GoTo 0
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Rohith1324]

If A1:J1 are filled, then that row is ready to go.
If A2:G1 are filled, but H2:J2 not, that row is not ready to do.

When the user presses the button, what should happen?

Another way to ask my question is, when the user presses the button, which row should the routine look at to see if it should take action?

when A2:G2 is filled and H2:J2 is not then the user should not get an option to click on the command button

User should press the button only when the field from A1:J1 so that all the required details are captured.

 

[Rohith1324]

Assuming you are using an ActiveX CommandButton named CommandButton1 (change the code to match its actual name), this Change event code procedure will disable the CommandButton whenever there is a blank cell within Columns A:J from Row 1 on down to the last cell within Columns A:J with data. So, if the user deletes the contents of a cell on a row that was already full between Columns A to J, the CommandButton will become disabled until that cell is given data again. Also, if the user enters a value on the row immediately under the last filled out row, the button will become disabled until that full row from Columns A to J is filled in. Note that this proposal does not require you to do anything different with the code being executed when the CommandButton is clicked. If that is the kind of functionality you are looking for, then here is the Change event procedure to accomplish it...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim LastRow As Long, Cnt As Long
  If Not Intersect(Target, Columns("A:J")) Is Nothing Then
    On Error GoTo Whoops
    LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    ActiveSheet.CommandButton1.Enabled = Evaluate("COUNTBLANK(A1:J" & LastRow & ")=0")
    On Error GoTo 0
  End If
  Exit Sub
Whoops:
  ActiveSheet.CommandButton1.Enabled = False
  On Error GoTo 0
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

I will try this and give you the feedback shortly....thank you

 

[mikerickson]

when A2:G2 is filled and H2:J2 is not then the user should not get an option to click on the command button

User should press the button only when the field from A1:J1 so that all the required details are captured.

What should happen when A1:J1 is filled and A2:G2 is filled, but H2:J2 is not.
Which row should be considered when deciding whether the button works or not.
Row 1, or the last row on the sheet or ????