Conditional Button.

[sungen99]

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?

[JE McGimpsey]

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 <[email protected]>,
sungen99 <[email protected]>
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?

[JimMay]

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" <[email protected]> wrote in message
news:[email protected]:

> 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 <[email protected]>,
> sungen99 <[email protected]>
> 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?

[JE McGimpsey]

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 A1>0..

In article <Git4g.16365$fG3.15781@dukeread09>, "JimMay" <[email protected]>
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?

[JimMay]

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" <[email protected]> wrote in message
news:[email protected]:

> 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 A1>0..
>
> In article <Git4g.16365$fG3.15781@dukeread09>, "JimMay" <[email protected]>
> 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?

[JE McGimpsey]

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" <[email protected]>
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" <[email protected]> wrote in message
> news:[email protected]:
>
> > 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 A1>0..
> >
> > In article <Git4g.16365$fG3.15781@dukeread09>, "JimMay" <[email protected]>
> > 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?

[JimMay]

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" <[email protected]> wrote in message
news:[email protected]:

> 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" <[email protected]>
> 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" <[email protected]> wrote in message
> > news:[email protected]:
> >
>
> > > 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 A1>0..
> > >
> > > In article <Git4g.16365$fG3.15781@dukeread09>, "JimMay" <[email protected]>
> > > 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?