Re: Button - Enable/Disable...

[Dhimit]

Re: Button - Enable/Disable...

Hi,

Is it possble to 'enable' or 'disable' a Button on a worksheet depending on some business logic ?

Dhimit

 

[Datsmart]

You don't say if this is a Forms button or a Controls Toolbar button.
Code would be different depending on which you are using.

Forms Button:

    ActiveSheet.Shapes("Button 1").Visible = False

This code will make the button named Button 1 invisible.
Control Toolbar Button:

   CommandButton1.Enabled = False

This code will "grey out" the button named CommandButton 1.

You could run code similar to this dependant on your "bussiness logic".

 

[Dhimit]

RE:

Thanks.

Some qtns:

1. My business logic:
IF Check-box is checked THEN 'Enable' button
ELSE 'Disable' button
END IF
2. Where do i put the code ?

The check-box i'm referring to is the one that i will drag from the toolbar & place on my worksheet.

Dhimit

 

[Datsmart]

You started this thread asking about buttons, now you say it is checkboxes. And still you do not say weather they are Forms or Controls objects.
Take a look at the code on this thread. It has code that references both styles of checkboxes. You can build upon it's ideas.
http://www.mrexcel.com/board2/viewtopic.php?p=1208594

By the way, if you setup code to "Disable" a checkbox when un-checked, how are you going to check it later?

 

[Dhimit]

Re:

Hi John,

The 'check-box' is what is going to dictate on whether to Enable or Disable the Button !
the button is a Form button that i've place on the worksheet.

I'll see what i can achieve with what i have.

Dhimit

 

[Datsmart]

This code will cycle Forms Button 2 visible or invisible as Forms CheckBox 1 is checked or unchecked.

Sub CheckBox1Test()
    If ActiveSheet.CheckBoxes("Check Box 1").Value = xlOn Then
        ActiveSheet.Shapes("Button 2").Visible = True
    Else
        ActiveSheet.Shapes("Button 2").Visible = False
    End If
End Sub

Change checkbox and button names as needed.

 

[Dhimit]

RE:

Hi Datsmart

Pls. have a look @ the following syntax below:

I want to ENABLE a button when A1 has no value & DISABLE the button when A1 has a value.

=IF(A1="",ActiveSheet.Shapes("Create Cash Entry").Visible =True,
ActiveSheet.Shapes("Create Cash Entry").Visible =False)


'Create Cash Entry' is the title of the button, but it's complaining about 'Visible' part of the syntax

Dhimit

 

[Datsmart]

You are trying to use the "Caption" of your button in place of the "Name" of the button. They are two different things.
To see the name of your button, Ctrl-click the button.
The objects name can be seen in the Name Box. (Left of the formula bar)

The below code will Enable the button named "Button 1" when cell A1 is empty. If A1 is not empty, the button will be in-visible.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A1").Value = "" Then
        ActiveSheet.Shapes("Button 1").Visible = True
    Else
        ActiveSheet.Shapes("Button 1").Visible = False
    End If
End Sub

Right click sheet tab
Click "View Code"
Paste the code into the panel that opens. ("WorkSheet" Module)
Alt-Q to quit the VBA Editor.

 

[Dhimit]

You are trying to use the "Caption" of your button in place of the "Name" of the button. They are two different things.
To see the name of your button, Ctrl-click the button.
The objects name can be seen in the Name Box. (Left of the formula bar)

The below code will Enable the button named "Button 1" when cell A1 is empty. If A1 is not empty, the button will be in-visible.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A1").Value = "" Then
        ActiveSheet.Shapes("Button 1").Visible = True
    Else
        ActiveSheet.Shapes("Button 1").Visible = False
    End If
End Sub

Right click sheet tab
Click "View Code"
Paste the code into the panel that opens. ("WorkSheet" Module)
Alt-Q to quit the VBA Editor.

Thanks,

Can you tell me EXACTLY where to place this code ?!

I ALREADY have in place a MACRO that is assigned to the button. The code in this MACRO does some logic - do i put your piece of code in the SAME Macro ?!

Dhimit

 

[Datsmart]

The bottom part of my last post explains where to put the code.

Right click sheet tab
Click "View Code"
Paste the code into the panel that opens. ("WorkSheet" Module)
Alt-Q to quit the VBA Editor.

This is event code. Any time a change is made on that worksheet the event "fires" the code. Cell A1 is checked and the code sets the Button 1 parameters accordingly.