How do I hide a command button based on a condition?

[ratttman]

Hi everybody,
Nice forum. I have read over the hide command button responces but I
simply cannot follow the advice because I guess its to far over my
head. Can anybody please explain to me how to hide a command button
when a cell ( say E27 ) is 0 but as soon as it turns to 1 (through a
formula) a command button will appear in the sheet and allow a user to
launch a macro to save the data (the button will run a macro that
copies data to a different part of the spreadsheet); after the macro
has run I would like the button to hide again.
I have tried various examples but I guess I am putting the samples in
the wrong places ( modules or microsoft excel objects).
I have spent ~6 hours on this one little issue and it is driving me
crazy.
Thanks for any help,
rattman

p.s. If you write the instructions for a 4th grader maybe I will
understand it?

 

[Ron de Bruin]

Hi

Copy this event in the sheet module
I use a button with the name CommandButton1 and E27 is a formula


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("E27"), rng) Is Nothing Then
If Range("E27").Value > 0 Then
ActiveSheet.Shapes("CommandButton1").Visible = True
Else
ActiveSheet.Shapes("CommandButton1").Visible = False
End If
End If
End If
EndMacro:
End Sub

 

[ratttman]

Thanks for the quick responce!
When you say put it in the sheet module do you mean the Excel Objects
sheet1 or do you mean Modules/Module1? I tried both and could not get
it to work. The button I know how to add a macro to is a form button (
I just right click and add macro) If I add the macro does that change
the name of the button to the name of the macro? If not how to I figure
out the button name?
I also used a command button (added by the control toolbox) but like a
moron I do not know how to assign a macro to it.
If I click on the vba run button to run your code I get a prompt to
pick a macro. How does Excel know when to run the code?
I would attach the spreadsheet but it does not look like I can.
Thanks for the initial help. I really appreciate it.
Rattman

 

[ratttman]

I figured out the command button and got it to work!
Last question - I changed the text of the button but it shows up as one
line. How do I get the button text to word wrap?
Thanks for the help Ron!
Rattman

 

[Dave Peterson]

If you're doing it manually, you can use ctrl-enter to force a new line.