Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I hide a command button based on a condition?
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? :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I hide a command button based on a condition?
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 -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... 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? :) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I hide a command button based on a condition?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I hide a command button based on a condition?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I hide a command button based on a condition?
If you're doing it manually, you can use ctrl-enter to force a new line.
" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Hide Form and Command Buttons until such condition is true? | Excel Discussion (Misc queries) | |||
Hide rows based on a condition | Excel Programming | |||
Hide a row based on one cell's condition | Excel Worksheet Functions | |||
how do you hide a forms command button | Excel Programming | |||
Hide command button on worksheet | Excel Programming |