Conditional Button.
 

I have a list of data. If cell A <= 0 (zero) I want a button to appear
that will run a macro. Can anyone help me with this please?


--
sungen99
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=537260

Conditional Button.
 

One way:

Create a button (say, "Button 1") from the Forms toolbar and attach your
macro to it.

Does A1 have a formula in it? If so, put this in your worksheet code
module:

Private Sub Worksheet_Calculate()
With Range("A1")
If IsNumeric(.Value) Then _
Me.Shapes("Button 1").Visible = .Value 0
End With
End Sub

If instead A1 is a manual entry, use the Worksheet_Change() event
instead:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If Not Intersect(.Cells, Range("A1")) Is Nothing Then
If IsNumeric(.Cells(1).Value) Then _
Me.Shapes("Button 1").Visible = .Cells(1).Value 0
End If
End With
End Sub


In article ,
sungen99
wrote:

I have a list of data. If cell A <= 0 (zero) I want a button to appear
that will run a macro. Can anyone help me with this please?

Conditional Button.
 

I'm missing something here. Once I create button1 on the screen which
is to be visible once cell A1 updates to greater than 0 - How do I hide
it?
TIA,

"JE McGimpsey" wrote in message
:

One way:

Create a button (say, "Button 1") from the Forms toolbar and attach your
macro to it.

Does A1 have a formula in it? If so, put this in your worksheet code
module:

Private Sub Worksheet_Calculate()
With Range("A1")
If IsNumeric(.Value) Then _
Me.Shapes("Button 1").Visible = .Value 0
End With
End Sub

If instead A1 is a manual entry, use the Worksheet_Change() event
instead:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If Not Intersect(.Cells, Range("A1")) Is Nothing Then
If IsNumeric(.Cells(1).Value) Then _
Me.Shapes("Button 1").Visible = .Cells(1).Value 0
End If
End With
End Sub


In article ,
sungen99
wrote:


I have a list of data. If cell A <= 0 (zero) I want a button to appear
that will run a macro. Can anyone help me with this please?

Conditional Button.
 

If you put the macro in your worksheet code module, it updates
automatically every time the worksheet is calculated, hiding the button
is A1<=0, displaying it if A10..

In article <Git4g.16365$fG3.15781@dukeread09, "JimMay"
wrote:

I'm missing something here. Once I create button1 on the screen which
is to be visible once cell A1 updates to greater than 0 - How do I hide
it?

Conditional Button.
 

This is driving me nuts; I have reviewed it 25-30 times I have
Calculation-Automatic on;

In Sheet1 cell A1 - I enter 10; [Button 1] shows
I enter 0 in A1 [Button 1] still shows

In Sheet1 Module I have in:
Object box: Worksheet
Procedure Box: Calculate
In Code window:

Private Sub Worksheet_Calculate()
With Range("A1")
If IsNumeric(.Value) Then _
Me.Shapes("Button 1").Visible = .Value 0
End With
End Sub

From the Forms Toolbar I created a Command-button
And assigned a macro to (in a standard module) of the WB:

Sub Foo()
MsgBox "Testing"
End Sub

Confused on a Friday night....
Any help appreciated..
Jim


"JE McGimpsey" wrote in message
:

If you put the macro in your worksheet code module, it updates
automatically every time the worksheet is calculated, hiding the button
is A1<=0, displaying it if A10..

In article <Git4g.16365$fG3.15781@dukeread09, "JimMay"
wrote:


I'm missing something here. Once I create button1 on the screen which
is to be visible once cell A1 updates to greater than 0 - How do I hide
it?

Conditional Button.
 

Reread my initial reply: If you're entering the values manually, see the
second solution I gave...

Entering a value doesn't necessarily trigger a Calculate event.

In article <jiy4g.16717$fG3.11902@dukeread09, "JimMay"
wrote:

This is driving me nuts; I have reviewed it 25-30 times I have
Calculation-Automatic on;

In Sheet1 cell A1 - I enter 10; [Button 1] shows
I enter 0 in A1 [Button 1] still shows

In Sheet1 Module I have in:
Object box: Worksheet
Procedure Box: Calculate
In Code window:

Private Sub Worksheet_Calculate()
With Range("A1")
If IsNumeric(.Value) Then _
Me.Shapes("Button 1").Visible = .Value 0
End With
End Sub

From the Forms Toolbar I created a Command-button
And assigned a macro to (in a standard module) of the WB:

Sub Foo()
MsgBox "Testing"
End Sub

Confused on a Friday night....
Any help appreciated..
Jim


"JE McGimpsey" wrote in message
:

If you put the macro in your worksheet code module, it updates
automatically every time the worksheet is calculated, hiding the button
is A1<=0, displaying it if A10..

In article <Git4g.16365$fG3.15781@dukeread09, "JimMay"
wrote:


I'm missing something here. Once I create button1 on the screen which
is to be visible once cell A1 updates to greater than 0 - How do I hide
it?

Conditional Button.
 

JE,
I'm sorry to be so "thick", but from your last comment (help)
As soon as I went to cell A1 and entered = D1
My Command Button 1 disappeared !!!!
How exciting !!! Thank you, thank you;
I'm finally "informed".
Have a great weekend.
Jim


"JE McGimpsey" wrote in message
:

Reread my initial reply: If you're entering the values manually, see the
second solution I gave...

Entering a value doesn't necessarily trigger a Calculate event.

In article <jiy4g.16717$fG3.11902@dukeread09, "JimMay"
wrote:


This is driving me nuts; I have reviewed it 25-30 times I have
Calculation-Automatic on;

In Sheet1 cell A1 - I enter 10; [Button 1] shows
I enter 0 in A1 [Button 1] still shows

In Sheet1 Module I have in:
Object box: Worksheet
Procedure Box: Calculate
In Code window:

Private Sub Worksheet_Calculate()
With Range("A1")
If IsNumeric(.Value) Then _
Me.Shapes("Button 1").Visible = .Value 0
End With
End Sub

From the Forms Toolbar I created a Command-button
And assigned a macro to (in a standard module) of the WB:

Sub Foo()
MsgBox "Testing"
End Sub

Confused on a Friday night....
Any help appreciated..
Jim


"JE McGimpsey" wrote in message
:


If you put the macro in your worksheet code module, it updates
automatically every time the worksheet is calculated, hiding the button
is A1<=0, displaying it if A10..

In article <Git4g.16365$fG3.15781@dukeread09, "JimMay"
wrote:



I'm missing something here. Once I create button1 on the screen which
is to be visible once cell A1 updates to greater than 0 - How do I hide
it?