Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional option button help | Excel Worksheet Functions | |||
conditional error for option button | Excel Worksheet Functions | |||
How to make a button conditional | Excel Programming | |||
Conditional Formatting a Button | Excel Programming | |||
Conditional Button Enabling based on Checkbox | Excel Programming |