Disabling macros/command buttons when a cell meets a condition

[Milos]

Hi all,

I have a simple macro that is attached to an active x control command button. The macro simply deletes pre-arranged unecessary cells so that a sheet can be printed efficiently:

Sub delete_specified_columns()
Sheets("Sheet1").Range("B:C,E:F").EntireColumn.Delete
End Sub

Here is an example of the macro in action:
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Contents 1[/TD]
[TD]Unecessary 1[/TD]
[TD]U2[/TD]
[TD]Contents 2[/TD]
[TD]U3[/TD]
[TD]U4[/TD]
[TD]Contents 3[/TD]
[TD][/TD]
[TD]Contents 1[/TD]
[TD]Contents 2[/TD]
[TD]Contents 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Pepper[/TD]
[TD]ABC[/TD]
[TD]123[/TD]
[TD]Oxygen[/TD]
[TD]!@#[/TD]
[TD]ZYX[/TD]
[TD]Beech[/TD]
[TD]Command button clicked=>[/TD]
[TD]Pepper[/TD]
[TD]Oxygen[/TD]
[TD]Beech[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Mint[/TD]
[TD]DEF[/TD]
[TD]456[/TD]
[TD]Carbon[/TD]
[TD]$%^[/TD]
[TD]WVU[/TD]
[TD]Pine[/TD]
[TD][/TD]
[TD]Mint[/TD]
[TD]Carbon[/TD]
[TD]Pine[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Paprika[/TD]
[TD]GHI[/TD]
[TD]789[/TD]
[TD]Helium[/TD]
[TD]&*([/TD]
[TD]TSR[/TD]
[TD]Oak[/TD]
[TD][/TD]
[TD]Paprika[/TD]
[TD]Helium[/TD]
[TD]Oak[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



My question is can I code the macro/command button so that it will only work with one click? Could I code the macro to disable when certain cells (such as F1) meet a certain criteria (such as being blank). Furthermore where would I put this code? Apply it to the specific module or in the specific sheet?

Any help is greatly appreciated!

Thanks,

 

[gallen]

My question is can I code the macro/command button so that it will only work with one click?

I don't understand. In one click, 4 columns will be deleted. Do you wish to disable the button once it has been clicked once? if so:

[COLOR=#333333]Sub delete_specified_columns()[/COLOR]
[COLOR=#333333]    Sheets("Sheet1").Range("B:C,E:F").EntireColumn.Delete
    'you didn't provide the name of your button so I'll go with btnDelete
[/COLOR]    btnDelete.Enabled= False
    'or you could just hide it:
    btnDelete.Visible = False
[COLOR=#333333]End Sub[/COLOR]

Could I code the macro to disable when certain cells (such as F1) meet a certain criteria (such as being blank)

Check for that condition and don't run macro if condition is met

[COLOR=#333333]Sub delete_specified_columns()
   'do not delete if F1 is blank
[/COLOR]   If Range("F1") = "" Then Exit Sub
[COLOR=#333333]   Sheets("Sheet1").Range("B:C,E:F").EntireColumn.Delete
    'you didn't provide the name of your button so I'll go with btnDelete
[/COLOR]   btnDelete.Enabled= False
    'or you could just hide it:
    btnDelete.Visible = False
[COLOR=#333333]End Sub[/COLOR]

Furthermore where would I put this code? Apply it to the specific module or in the specific sheet?

This code would need to be in the codewindow of the Sheet you are trying to affect.

 

[Milos]

I don't understand. In one click, 4 columns will be deleted. Do you wish to disable the button once it has been clicked once? if so:

Yes that is what I was after. This is what I have come up with so far (it is pretty crude but it works).

Private Sub btnDelete_Click()delete_specified_columns
btnDelete.Enabled = False
[COLOR=#333333]End Sub[/COLOR]

I then need to make an additional macro to re-enable the code when necessary:

Sub Re_enable_button()Sheets("Sheet1").btnDelete.Enabled = True
[COLOR=#333333]End Sub[/COLOR]

 

[gallen]

What conditions are required to have it re-enabled? You'd need this code in the Worksheet_Change event.