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

 

[wisewood]

Dhimit, give us the code you already have - it will make helping you much easier.

 

[brandesj]

I'm looking for an answer to the same problem; however I don't really want to make the button invisible just disabled or grayed out. If I use the Controls Toolbox command button I can manually set the enabled property to true or false and that works fine. However in VBA this code doesn't seem to work...
CommandButton2.Enabled = False
or this...
ThisWorksheet.Shapes("CommandButton2").Enabled = False

I get a run time error 424 "Object Required" on either of these statements.

I have checked the button name a hundred times and it is CommandButton2...

Help please???

 

[Datsmart]

See this thread on working with Control Toolbar objects.
http://www.mrexcel.com/board2/viewtopic.php?p=1320228

 

[brandesj]

Thanks, based on the examples I got it figured out. Seem you can't just call an object and expect to change it's properties, the object must be selected first.

This works...
ActiveSheet.Shapes("CommandButton2").Select
Selection.Enabled = False

Viola, one greyed out button

Something so simple kicked my butt.

 

[Dhimit]

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.

Hi,

The 'event code' worked the 1st time, but now it's not ?!
The button disappeared off the sheet the 1st time but now the 'event code' is just not working
And, i did exactly what you said...

Dhimit

 

[Datsmart]

Did you somehow turn off Events?
Type and Enter the following in the Immediate VBE Window. (Ctrl-G opens that window in VBE)

    Application.EnableEvents = True

 

[Dhimit]

Did you somehow turn off Events?
Type and Enter the following in the Immediate VBE Window. (Ctrl-G opens that window in VBE)

    Application.EnableEvents = True

Hi,

Where do I stick that piece of syntax when i do a CTRL 'G'
Is it in 'Reference' ?

Also, to cut the long story short, what can i do to the code that you've given me to make the button 'GREYED OUT' as opposed to it Disappearing ?! Remember, i'm using a 'FORM' button.

Thanks - DC

 

[Dhimit]

Did you somehow turn off Events?
Type and Enter the following in the Immediate VBE Window. (Ctrl-G opens that window in VBE)

    Application.EnableEvents = True

Hello again Datasmart,
Some more queries:
1. Where do I stick that piece of syntax when I do a CTRL 'G'
Is it in 'Reference' ?

2. Also, to cut the long story short, what can i do to the code that you've given me to make the button 'GREYED OUT' as opposed to it disappearing ?! Remember, i'm using a 'FORM' button.

3. One more thing - can the 'event' code work in respect to the position of the cursor on the worksheet, e.g
Position of the button in on B1

IF the cursor is on ROW1 - button should be disabled & if the cursor below A1 (ROW2,ROW3,ROW4, etc...) the cursor should be ENABLED.
In short, button should ONLY be DISABLED when the CURSOR is anywhere on ROW1.

Thanks - DC

 

[Datsmart]

1. Where do I stick that piece of syntax when I do a CTRL 'G'
As mentioned "In the Immediate VBE Window".
Are you in the VB editor? (Use Alt-F11 to get there from your workbook)
Then hit Ctrl-g to open the Immediate window.
Paste the code there and hit Enter to run it.

2. You can not use "Enabled" with a Forms button. I don't believe that property is available there. You would have to use a Controls Button and the earlier posted code would be entirely different.

3. Can the 'event' code work in respect to the position of the cursor on the worksheet"
To use the active cell you would change the code to use the Target.value.
Instead of something like;

  If Range("A1").Value = "" Then

- use -

  If Target.Value = "" Then

 

[Dhimit]

RE:

Hello again,

What would be the syntax if the cursor is below ROW2 (Button Disabled) ?!

Is it :

If Target.Value > ROW2 Then
ActiveSheet.Shapes("Button 1").Visible = True
Else
ActiveSheet.Shapes("Button 1").Visible = False
End If

Note, the criteria for the logic in enabling/didabling the button is the POSITION of the cursor & not the value of a Cell.

Thanks - DC