Enable/Disable Command Buttons from a Sheet?

[e4excel]

How do I Enable/Disable Command Buttons from a Sheet?

I am using a Command Button in a Sheet to copy and paste the Data from one Sheet to another with the help of macro..

As I am not well-versed with VBA dont know all the syntaxes of VBA.
I need help for the command button..

First and Foremost, I double click a Command Button form the Control tool-box and paste it on the Sheet, I dont know how to get the name of this command button , I mean where do i get it?

Based on a condition like a value in a cell I want it to be Enabled and Disabled?

Any ideas...please I am not able to follow even after googling a lot as I dont know what's the name of the command button control I have used.

If the value entered in a particluar cell is more than the 1000 difference between two cells then the command button should be disbaled...

The Application part:
The command button is used to transfer the data in a cell lets say $I$4 to another sheet Cell J2,J3,J4 so on so forth..by incrementing the ROW number.

Now The balance gets depleted with every new Debit Entry and we need to disallow the user from entering such an amount which will reduce the balance more than The Minimum Account Balance of a bank...

Hope I am able to explain well..



Warm Regards
e4excel.

[Palmetto]

When you first create the command button the name will appear in the Name Box to the left of the formula bar. If it doesn't, open the Control Toolbox and enter "Design Mode", then click on the button and you will see the name.

If you double-click the button while in design mode it will open the VB Editor with the sheet module in view, ready to receive code for the button.

To enable/disable the button, here is sample code using the worksheet change event to look at an entry in cell A1.

See the attached example. There are multiple ways to go about this depending on your needs/requirements.

Please Login or Register  to view this content.

[e4excel]

Hi Palmetto!,

Sorry for the late response as my net was down the entire night cudn't write to you..Your file works fabulously in the example of Yes and No ...Its a good example for people like me who are absolute novices..

Now need some help, if you could provide some explantion in your code as Im going to customise the code according to my requirement.

Some Queries!
1. Why is Worksheet_Change Event selected?
2. What's Target.Count ?
3. What's the Me in the code "Me.CommandButton1.Enabled" for ?

For confidentiality reasons I am not able to upload the actual file as it contains all company banking information...

However, the requirement goes like this ...
There are two Sheets 1. CHEQUE and 2.Record Slip..

In the Sheet "CHEQUE" Cell $I$5, I will be entering a value an amount, this amount should not be more than the Minimum Balance for an Account, and this would be compared from the other sheet "RECORD SLIP"..

I have a running balance on the Sheet "RECORD SLIP"..so I will be picking up the latest balance i.e. Balance after the last transaction. and then this (Balance - $I$5 ) should not be more than the 1001.If the difference is less than or equal to 1001 then the CommandButton can be Enabled else it should be Disabled till the value in the Cell $I$5 is changed to a lesser value.

[Palmetto]

Some Queries!
1. Why is Worksheet_Change Event selected?

It is simply the way I chose to implement the code. When using an Active-X control embedded in a sheet, the code must go into the sheet module. However, an event related to the control itself could have been used. The Worksheet_Change Event is the correct place to determine when to enable/disable the button since you will immediately change the button state when requirements are either met or not met.

2. What's Target.Count ?

This is intended to prevent the code from executing if more than one cell is selected as it it would cause an error. Not knowing the details of your requirements, I opted to use it but it is not always necessary to have this line of code.

3. What's the Me in the code "Me.CommandButton1.Enabled" for ?

Briefly, "Me" is just a way of implicitly referencing the worksheet.

For confidentiality reasons I am not able to upload the actual file as it contains all company banking information...

You can always mock up the sheet with representative structure and remove any sensitive details.

However, the requirement goes like this ...
There are two Sheets 1. CHEQUE and 2.Record Slip..

In the Sheet "CHEQUE" Cell $I$5, I will be entering a value an amount, this amount should not be more than the Minimum Balance for an Account, and this would be compared from the other sheet "RECORD SLIP"..

I have a running balance on the Sheet "RECORD SLIP"..so I will be picking up the latest balance i.e. Balance after the last transaction. and then this (Balance - $I$5 ) should not be more than the 1001.If the difference is less than or equal to 1001 then the CommandButton can be Enabled else it should be Disabled till the value in the Cell $I$5 is changed to a lesser value.

If I understand correctly, you just want to make sure then entry in cell $I$5 (on Cheque sheet) is <= 1001.

Amended code, which needs to go in the sheet module for which ever sheet contains the command button.

Please Login or Register  to view this content.

[e4excel]

You can always mock up the sheet with representative structure and remove any sensitive details.

Hey Palmetto, thnks a lot bro!
I am trying to put the actual file but it will take me some time to desensitise it, meaning removing the confidential info!

If I understand correctly, you just want to make sure then entry in cell $I$5 (on Cheque sheet) is <= 1001.

No, the entry can be more than 1001, however the difference between the CUrrent Balance which is calculated in the RECORD SLIP and the entry in $I$5 should be less than 1001..

The Command Buttons going to be in the CHEQUE sheet and the DATA from here is going to be moved from here to the RECORD SLIP with the help of the command button.

Must appreciate the fact that you put the Validation for non-numerice data on your own , true signs of a good programmer.

Hope you can change the code slightly before I upload the file which is going to take some time....

[e4excel]

hi Palmetto,

As promised here's the Demo file..for you...

[Palmetto]

No, the entry can be more than 1001, however the difference between the CUrrent Balance which is calculated in the RECORD SLIP and the entry in $I$5 should be less than 1001..

Ok. Then the logic to enable the button is Balance less Amount <=1001.

You have merged cells (I3 & I5). Merged cells often cause problems with VBA. I've unmerged them and used the "Center Across Selection" instead.

I've also added code to the sheet activate event to disable the button. Included is an optional line to clear the input cell ($I$5).

You were using a button from the Forms Tool Bar. This has been replaced with an active-X button from the Controls Tool Bar and your existing code placed into the on_click event.

Please Login or Register  to view this content.

[e4excel]

Ok. Then the logic to enable the button is Balance less Amount <=1001.

No the Logic is to ensure that the Balance does not go below 1001 which is the Minimum Account Balance or else you would be charged for Non-Maintenance of the same.

There was a slight error in my code in the Cell $I$3..

IF(ISNUMBER(INDIRECT("'"&CHEQUE!$G$5&"'!$I$"&COUNTA(INDIRECT("'"&CHEQUE!$G$5&"'!$E:$E")))),INDIRECT("'"&CHEQUE!$G$5&"'!$I$"&COUNTA(INDIRECT("'"&CHEQUE!$G$5&"'!$E:$E"))),INDIRECT("'"&CHEQUE!$G$5&"'!$J$1"))

I have made the neccessary change marked in RED to give the BALANCE.

You have merged cells (I3 & I5). Merged cells often cause problems with VBA. I've unmerged them and used the "Center Across Selection" instead.

That's fine regarding the Merging but will it cause problems if not Unmerged..

You were using a button from the Forms Tool Bar. This has been replaced with an active-X button from the Controls Tool Bar and your existing code placed into the on_click event.

Now this I did not get at all, I am not able to understand the difference , please elaborate on these different types of Objects a little more..

and Unfortunately its not working when Im entering a value of 6000.

I will explain the Outcome...

The Actual Balance is 9000, after I have used a chq for 1000 the Blance would reduce to 8000 then if Im writing a cheque of 7000 then the command button should be disabled as the difference between the current balance then i.e. 8000 - 7000 = 1000 and the Minimum Balance needed to be maintained in the account is 1001 so any amount which can take the balance below 1001 should be prevented by Disabling the Button....

Thanks for all the efforts till now...but a last one...

[e4excel]

Its working, however the balance is still going below 1001, I apologize if I was not able to epxlain you correctly , however the logic just needs to be tweaked a bit...


Please plz explain about the two different Controls...

[Palmetto]

Now that I understand you want to maintain a minimum balance of 1001 this amended code should do the job.

The only thing needed was to reverse the operators in the case statements.

Please Login or Register  to view this content.

Please plz explain about the two different Controls...

In short:
Controls from the Forms Toolbox do not have "events" and you have less flexibility to modify the properties or behavior of the button.

Controls from the Control Toolbox require and make use of events and have a number of properties you can manipulate. To see what I mean, open the Control Toolbox, click the icon for "Design Mode" and then double-click the command button. the VB Editor should open with the property sheet for command button in view. Explore from here.

It largely a matter of opinion, but, in my view, controls from the Forms Toolbox should be used whenever possible. Because a Forms command button cannot be disabled, you need to use one from the Controls Toolbox.

[e4excel]

You are the MAN Palmetto...
it worked brilliantly and sorry for the trouble incase I wasn't clear...

I just would need to enter the amount everytime or else it fails to Disable the Command Button..So I will be using the Small Line provided by you earlier to Clear Contents so that a fresh entry is made every time and then evaluated.

Thanks a lot....

[e4excel]

Just a little problem though, my earlier try in VBA was in the Form Tool Box so how can I do it in Controls or vice versa ?

CAn your code be just copied / appended and pasted in the earlier code to get the desired result.

[Palmetto]

Just a little problem though, my earlier try in VBA was in the Form Tool Box so how can I do it in Controls or vice versa ?

Like any other tool bar, the Controls Toolbox can be shown by either:
1. Menu > View > Toolsbars
2. Right-Click any visible toolbar and choose a toolbar from the pop up list.

CAn your code be just copied / appended and pasted in the earlier code to get the desired result.

Yes. Just copy everything from "Sub . . to . . End Sub" and paste it over the existing code, making sure you are only pasting it ONLY over the Worksheet_Change code.

[e4excel]

Thanks a lot brother,

With your able help I was able to use the Right Controls. I think they consume less memory compared to the other Controls..

However, there's a small problem incase of a Text entry the program goes into END DEBUG window...

Private Sub Worksheet_Change(ByVal Target As Range)
'Declare a variable
 Dim MinAmt As Long
 'Calculate the difference between Balance and Amount
  MinAmt = Range("L2") - Range("I5")

 'Make sure only one cell is selected
    If Target.Count > 1 Then Exit Sub

 'Make sure the selected cell is the input cell
    If Not Intersect(Target, Range("$I$5")) Is Nothing Then
    
 'Protect against inadvertent non-numeric typo's
    If IsNumeric(Target) Then
        
           'Test the value of MinAmt variable
            Select Case MinAmt
            
                'Enable the command button if MinAmt is greater than or equal than 1001
                Case Is >= 1001
                    Me.CommandButton1.Enabled = True
                
                'disable the command button if MinAmt is less than 1001
                Case Is < 1001
                    Me.CommandButton1.Enabled = False

            End Select
        
        Else
            'Display message if a non-numeric entry was made
            MsgBox ("Non-numeric entries are not permitted"), vbExclamation
            
            'Disable the command button until valid entry is made
            Me.CommandButton1.Enabled = False
            Exit Sub
        End If
    End If
End Sub 

I have changed the Variable Ldiff to MinAmt...
Just a little help..!

[e4excel]

Just a small hlep , how to prevent the use r from entering any non-numeric entry at the data-entry level...which does not go in the END DEBUG MOde..