Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
make button invisible
Hi, please excuse my lack of knowledge around this, but if anyone can help it will really be appreciated. I have a button on a worksheet that when 'pressed' will e-mail the content of certain cells to me. This works really well! (I found the instructions via a link in another thread!) However, I don't want this button to be visible unless cell E49 has a value of 37 (it is the sum of other cells). The worksheet is called Waste quiz. Can anyone help? I know there is a difference between command buttons and form buttons - I believe this is a form button. Any help is gratefully received. Regards, Kate |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
make button invisible
Right click the sheet tab; and view Code..Paste the below code and try. I have named the button as CommandButton1. Change to suit.. Private Sub Worksheet_Calculate() Application.EnableEvents = False If Range("E49") = 37 Then Me.CommandButton1.Visible = True Else Me.CommandButton1.Visible = False End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "KateB" wrote: Hi, please excuse my lack of knowledge around this, but if anyone can help it will really be appreciated. I have a button on a worksheet that when 'pressed' will e-mail the content of certain cells to me. This works really well! (I found the instructions via a link in another thread!) However, I don't want this button to be visible unless cell E49 has a value of 37 (it is the sum of other cells). The worksheet is called Waste quiz. Can anyone help? I know there is a difference between command buttons and form buttons - I believe this is a form button. Any help is gratefully received. Regards, Kate |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
make button invisible
Thanks Jacob, but its not a command button, its a form button so I don't know if that makes a difference to the script? I've been trying this: Private Sub Worksheet_Change (ByVal Target As Range) If Not Intersect(Me.Range("E49"), Target) Is Nothing Then If Target.Value = 37 Then Buttons("button 8").Visible = True Else Buttons("button 8").Visible = False End If End If End Sub which I found on another thread, but it doesn't do anything. Can anyone suggest changes I can make to it to try to get it to respond? Kate "Jacob Skaria" wrote: Right click the sheet tab; and view Code..Paste the below code and try. I have named the button as CommandButton1. Change to suit.. Private Sub Worksheet_Calculate() Application.EnableEvents = False If Range("E49") = 37 Then Me.CommandButton1.Visible = True Else Me.CommandButton1.Visible = False End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "KateB" wrote: Hi, please excuse my lack of knowledge around this, but if anyone can help it will really be appreciated. I have a button on a worksheet that when 'pressed' will e-mail the content of certain cells to me. This works really well! (I found the instructions via a link in another thread!) However, I don't want this button to be visible unless cell E49 has a value of 37 (it is the sum of other cells). The worksheet is called Waste quiz. Can anyone help? I know there is a difference between command buttons and form buttons - I believe this is a form button. Any help is gratefully received. Regards, Kate |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
make button invisible
Kate, I ran you code & worked ok for me. I am wondering if you have EnableEvents set False. Try running this bit of code & then test your code again Sub atest() Application.EnableEvents = True End Sub -- jb "KateB" wrote: Thanks Jacob, but its not a command button, its a form button so I don't know if that makes a difference to the script? I've been trying this: Private Sub Worksheet_Change (ByVal Target As Range) If Not Intersect(Me.Range("E49"), Target) Is Nothing Then If Target.Value = 37 Then Buttons("button 8").Visible = True Else Buttons("button 8").Visible = False End If End If End Sub which I found on another thread, but it doesn't do anything. Can anyone suggest changes I can make to it to try to get it to respond? Kate "Jacob Skaria" wrote: Right click the sheet tab; and view Code..Paste the below code and try. I have named the button as CommandButton1. Change to suit.. Private Sub Worksheet_Calculate() Application.EnableEvents = False If Range("E49") = 37 Then Me.CommandButton1.Visible = True Else Me.CommandButton1.Visible = False End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "KateB" wrote: Hi, please excuse my lack of knowledge around this, but if anyone can help it will really be appreciated. I have a button on a worksheet that when 'pressed' will e-mail the content of certain cells to me. This works really well! (I found the instructions via a link in another thread!) However, I don't want this button to be visible unless cell E49 has a value of 37 (it is the sum of other cells). The worksheet is called Waste quiz. Can anyone help? I know there is a difference between command buttons and form buttons - I believe this is a form button. Any help is gratefully received. Regards, Kate |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
make button invisible
I ran that and still no difference. I tried swapping the e-mail script to a
command button but couldn't make it 'click' so maybe there is something wrong with my settings? Kate "john" wrote: Kate, I ran you code & worked ok for me. I am wondering if you have EnableEvents set False. Try running this bit of code & then test your code again Sub atest() Application.EnableEvents = True End Sub -- jb "KateB" wrote: Thanks Jacob, but its not a command button, its a form button so I don't know if that makes a difference to the script? I've been trying this: Private Sub Worksheet_Change (ByVal Target As Range) If Not Intersect(Me.Range("E49"), Target) Is Nothing Then If Target.Value = 37 Then Buttons("button 8").Visible = True Else Buttons("button 8").Visible = False End If End If End Sub which I found on another thread, but it doesn't do anything. Can anyone suggest changes I can make to it to try to get it to respond? Kate "Jacob Skaria" wrote: Right click the sheet tab; and view Code..Paste the below code and try. I have named the button as CommandButton1. Change to suit.. Private Sub Worksheet_Calculate() Application.EnableEvents = False If Range("E49") = 37 Then Me.CommandButton1.Visible = True Else Me.CommandButton1.Visible = False End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "KateB" wrote: Hi, please excuse my lack of knowledge around this, but if anyone can help it will really be appreciated. I have a button on a worksheet that when 'pressed' will e-mail the content of certain cells to me. This works really well! (I found the instructions via a link in another thread!) However, I don't want this button to be visible unless cell E49 has a value of 37 (it is the sum of other cells). The worksheet is called Waste quiz. Can anyone help? I know there is a difference between command buttons and form buttons - I believe this is a form button. Any help is gratefully received. Regards, Kate |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
make button invisible
Strange, I took it that you are using the Worksheet_Calculate event as
suggested to you? Your sample code showed the Worksheet_Change event which will not work for you as the result you are testing is in a calculated cell. If you have now added a command button and cannot get it €˜click that sounds like you are still in design mode & you need to turn that off. You can find this option on the controls toolbar. Paste code below behind the appropriate sheet €“ It shows option for either forms Button or Control Toolbox Commandbutton delete as required. Private Sub Worksheet_Calculate() 'delete button type not used If Me.Range("E49").Value = 37 Then 'forms button Me.Buttons("button 8").Visible = True 'or controls commandbutton Me.CommandButton1.Visible = True Else 'forms button Me.Buttons("button 8").Visible = False 'or controls commandbutton Me.CommandButton1.Visible = False End If -- jb "KateB" wrote: I ran that and still no difference. I tried swapping the e-mail script to a command button but couldn't make it 'click' so maybe there is something wrong with my settings? Kate "john" wrote: Kate, I ran you code & worked ok for me. I am wondering if you have EnableEvents set False. Try running this bit of code & then test your code again Sub atest() Application.EnableEvents = True End Sub -- jb "KateB" wrote: Thanks Jacob, but its not a command button, its a form button so I don't know if that makes a difference to the script? I've been trying this: Private Sub Worksheet_Change (ByVal Target As Range) If Not Intersect(Me.Range("E49"), Target) Is Nothing Then If Target.Value = 37 Then Buttons("button 8").Visible = True Else Buttons("button 8").Visible = False End If End If End Sub which I found on another thread, but it doesn't do anything. Can anyone suggest changes I can make to it to try to get it to respond? Kate "Jacob Skaria" wrote: Right click the sheet tab; and view Code..Paste the below code and try. I have named the button as CommandButton1. Change to suit.. Private Sub Worksheet_Calculate() Application.EnableEvents = False If Range("E49") = 37 Then Me.CommandButton1.Visible = True Else Me.CommandButton1.Visible = False End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "KateB" wrote: Hi, please excuse my lack of knowledge around this, but if anyone can help it will really be appreciated. I have a button on a worksheet that when 'pressed' will e-mail the content of certain cells to me. This works really well! (I found the instructions via a link in another thread!) However, I don't want this button to be visible unless cell E49 has a value of 37 (it is the sum of other cells). The worksheet is called Waste quiz. Can anyone help? I know there is a difference between command buttons and form buttons - I believe this is a form button. Any help is gratefully received. Regards, Kate |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
make button invisible
Brilliant! Works exactly how I wanted it to. Many thanks! Kate. "john" wrote: Strange, I took it that you are using the Worksheet_Calculate event as suggested to you? Your sample code showed the Worksheet_Change event which will not work for you as the result you are testing is in a calculated cell. If you have now added a command button and cannot get it €˜click that sounds like you are still in design mode & you need to turn that off. You can find this option on the controls toolbar. Paste code below behind the appropriate sheet €“ It shows option for either forms Button or Control Toolbox Commandbutton delete as required. Private Sub Worksheet_Calculate() 'delete button type not used If Me.Range("E49").Value = 37 Then 'forms button Me.Buttons("button 8").Visible = True 'or controls commandbutton Me.CommandButton1.Visible = True Else 'forms button Me.Buttons("button 8").Visible = False 'or controls commandbutton Me.CommandButton1.Visible = False End If -- jb "KateB" wrote: I ran that and still no difference. I tried swapping the e-mail script to a command button but couldn't make it 'click' so maybe there is something wrong with my settings? Kate "john" wrote: Kate, I ran you code & worked ok for me. I am wondering if you have EnableEvents set False. Try running this bit of code & then test your code again Sub atest() Application.EnableEvents = True End Sub -- jb "KateB" wrote: Thanks Jacob, but its not a command button, its a form button so I don't know if that makes a difference to the script? I've been trying this: Private Sub Worksheet_Change (ByVal Target As Range) If Not Intersect(Me.Range("E49"), Target) Is Nothing Then If Target.Value = 37 Then Buttons("button 8").Visible = True Else Buttons("button 8").Visible = False End If End If End Sub which I found on another thread, but it doesn't do anything. Can anyone suggest changes I can make to it to try to get it to respond? Kate "Jacob Skaria" wrote: Right click the sheet tab; and view Code..Paste the below code and try. I have named the button as CommandButton1. Change to suit.. Private Sub Worksheet_Calculate() Application.EnableEvents = False If Range("E49") = 37 Then Me.CommandButton1.Visible = True Else Me.CommandButton1.Visible = False End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "KateB" wrote: Hi, please excuse my lack of knowledge around this, but if anyone can help it will really be appreciated. I have a button on a worksheet that when 'pressed' will e-mail the content of certain cells to me. This works really well! (I found the instructions via a link in another thread!) However, I don't want this button to be visible unless cell E49 has a value of 37 (it is the sum of other cells). The worksheet is called Waste quiz. Can anyone help? I know there is a difference between command buttons and form buttons - I believe this is a form button. Any help is gratefully received. Regards, Kate |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
make button invisible if macros disabled | Excel Discussion (Misc queries) | |||
make VbA invisible | Excel Programming | |||
How do I make a command button invisible or visible? | Excel Programming | |||
How to make the Toolbar Invisible through VBA | Excel Programming | |||
Make one column Invisible. | Excel Programming |