|
Hiding a Button
Hi, Just wondering if anyone out there can help? I have a formula in cell A1 and if this is true it puts a '1' in A1 o and if false leaves this cell blank. What I am after is... Should cell A1 contain a number 1 then I want button to be showing be available to click. However, if this cel contains anything else I want the button to be hidden. Is this possible? If so, how? Many thanks :cool -- gatesheadthund ----------------------------------------------------------------------- gatesheadthunde's Profile: http://www.excelforum.com/member.php...fo&userid=1053 View this thread: http://www.excelforum.com/showthread.php?threadid=46631 |
Hiding a Button
You can use the worksheet change and sheet activate events to detect the
value of Cell A1. Based on that you can either hide or enable/disable the button (I perfer enable/disable as the user know it is there is is just greyed out if it is disabled) The button that you have... is it from the control toolbox or the forms toolbar. control toolbox puts the code in the sheet, while forms toolbar attaches to code written in a module. It makes a difference because you have to be able to find the button in your code and the syntax is different depending on the type of button. Private Sub Worksheet_Activate() If ActiveSheet.Range("A1") = 1 Then MsgBox "Unhide the button" Else MsgBox "Hide the button" End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" And Target.Value = 1 Then MsgBox "Unhide the button" Else MsgBox "Hide the button" End If End Sub -- HTH... Jim Thomlinson "gatesheadthunde" wrote: Hi, Just wondering if anyone out there can help? I have a formula in cell A1 and if this is true it puts a '1' in A1 or and if false leaves this cell blank. What I am after is... Should cell A1 contain a number 1 then I want a button to be showing be available to click. However, if this cell contains anything else I want the button to be hidden. Is this possible? If so, how? Many thanks :cool: -- gatesheadthunde ------------------------------------------------------------------------ gatesheadthunde's Profile: http://www.excelforum.com/member.php...o&userid=10536 View this thread: http://www.excelforum.com/showthread...hreadid=466312 |
Hiding a Button
Jim,
How do you actually enable or disable a button? -- Trefor "Jim Thomlinson" wrote: You can use the worksheet change and sheet activate events to detect the value of Cell A1. Based on that you can either hide or enable/disable the button (I perfer enable/disable as the user know it is there is is just greyed out if it is disabled) The button that you have... is it from the control toolbox or the forms toolbar. control toolbox puts the code in the sheet, while forms toolbar attaches to code written in a module. It makes a difference because you have to be able to find the button in your code and the syntax is different depending on the type of button. Private Sub Worksheet_Activate() If ActiveSheet.Range("A1") = 1 Then MsgBox "Unhide the button" Else MsgBox "Hide the button" End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" And Target.Value = 1 Then MsgBox "Unhide the button" Else MsgBox "Hide the button" End If End Sub -- HTH... Jim Thomlinson "gatesheadthunde" wrote: Hi, Just wondering if anyone out there can help? I have a formula in cell A1 and if this is true it puts a '1' in A1 or and if false leaves this cell blank. What I am after is... Should cell A1 contain a number 1 then I want a button to be showing be available to click. However, if this cell contains anything else I want the button to be hidden. Is this possible? If so, how? Many thanks :cool: -- gatesheadthunde ------------------------------------------------------------------------ gatesheadthunde's Profile: http://www.excelforum.com/member.php...o&userid=10536 View this thread: http://www.excelforum.com/showthread...hreadid=466312 |
Hiding a Button
There is an Enabled Property for command buttons. Select the button and
click the properties button on the control box toolbar, look under Enabled. Or myBar.Enabled = False From VBA Check out help and look at the object browser under CommandButton I am not sure if this is what you were looking for. Sean. "Trefor" wrote in message ... Jim, How do you actually enable or disable a button? -- Trefor "Jim Thomlinson" wrote: You can use the worksheet change and sheet activate events to detect the value of Cell A1. Based on that you can either hide or enable/disable the button (I perfer enable/disable as the user know it is there is is just greyed out if it is disabled) The button that you have... is it from the control toolbox or the forms toolbar. control toolbox puts the code in the sheet, while forms toolbar attaches to code written in a module. It makes a difference because you have to be able to find the button in your code and the syntax is different depending on the type of button. Private Sub Worksheet_Activate() If ActiveSheet.Range("A1") = 1 Then MsgBox "Unhide the button" Else MsgBox "Hide the button" End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" And Target.Value = 1 Then MsgBox "Unhide the button" Else MsgBox "Hide the button" End If End Sub -- HTH... Jim Thomlinson "gatesheadthunde" wrote: Hi, Just wondering if anyone out there can help? I have a formula in cell A1 and if this is true it puts a '1' in A1 or and if false leaves this cell blank. What I am after is... Should cell A1 contain a number 1 then I want a button to be showing be available to click. However, if this cell contains anything else I want the button to be hidden. Is this possible? If so, how? Many thanks :cool: -- gatesheadthunde ------------------------------------------------------------------------ gatesheadthunde's Profile: http://www.excelforum.com/member.php...o&userid=10536 View this thread: http://www.excelforum.com/showthread...hreadid=466312 |
Hiding a Button
Sean,
Sorry I must be missing something here. If I select the button, then through Control Toolbox I select properties, it only seems to display the properties for the sheet not the button. I used: ActiveSheet.Shapes("Button 30").Visible = False And this works in making it visible or not, but I would like to keep it visible, but "greyed out" and disabled. I thought the following might work: ActiveSheet.Shapes("Button 30").Enabled = False but Enabled is not valid. Iand... I could not find this in Help. -- Trefor "Sean Bartleet" wrote: There is an Enabled Property for command buttons. Select the button and click the properties button on the control box toolbar, look under Enabled. Or myBar.Enabled = False From VBA Check out help and look at the object browser under CommandButton I am not sure if this is what you were looking for. Sean. "Trefor" wrote in message ... Jim, How do you actually enable or disable a button? -- Trefor "Jim Thomlinson" wrote: You can use the worksheet change and sheet activate events to detect the value of Cell A1. Based on that you can either hide or enable/disable the button (I perfer enable/disable as the user know it is there is is just greyed out if it is disabled) The button that you have... is it from the control toolbox or the forms toolbar. control toolbox puts the code in the sheet, while forms toolbar attaches to code written in a module. It makes a difference because you have to be able to find the button in your code and the syntax is different depending on the type of button. Private Sub Worksheet_Activate() If ActiveSheet.Range("A1") = 1 Then MsgBox "Unhide the button" Else MsgBox "Hide the button" End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" And Target.Value = 1 Then MsgBox "Unhide the button" Else MsgBox "Hide the button" End If End Sub -- HTH... Jim Thomlinson "gatesheadthunde" wrote: Hi, Just wondering if anyone out there can help? I have a formula in cell A1 and if this is true it puts a '1' in A1 or and if false leaves this cell blank. What I am after is... Should cell A1 contain a number 1 then I want a button to be showing be available to click. However, if this cell contains anything else I want the button to be hidden. Is this possible? If so, how? Many thanks :cool: -- gatesheadthunde ------------------------------------------------------------------------ gatesheadthunde's Profile: http://www.excelforum.com/member.php...o&userid=10536 View this thread: http://www.excelforum.com/showthread...hreadid=466312 |
Hiding a Button
Try this:
You may have some other setting that is causing problems. I do not know enough about this to suggest where to look. Try this in a new instance of Excel: Open excel, ensure your Control toolbox toolbar is visible (right click on the menu and make sure Control toolbox is checked. Enable the properties (click properties button) The Worksheet will be selected as a default (I think that you have got this far) Create a command button (click command button on Control toolbar and click somewhere in workbook) Now click on the command button (the properties window should be listing the properties for CommandButton1) As for finding this in help try this: From Microsoft visual basic click the object browser button on the standard toolbar (looks like a box with some toys being thrown in) Under classes select the commandbutton item. Good luck. Sean "Trefor" wrote in message ... Sean, Sorry I must be missing something here. If I select the button, then through Control Toolbox I select properties, it only seems to display the properties for the sheet not the button. I used: ActiveSheet.Shapes("Button 30").Visible = False And this works in making it visible or not, but I would like to keep it visible, but "greyed out" and disabled. I thought the following might work: ActiveSheet.Shapes("Button 30").Enabled = False but Enabled is not valid. Iand... I could not find this in Help. -- Trefor "Sean Bartleet" wrote: There is an Enabled Property for command buttons. Select the button and click the properties button on the control box toolbar, look under Enabled. Or myBar.Enabled = False From VBA Check out help and look at the object browser under CommandButton I am not sure if this is what you were looking for. Sean. "Trefor" wrote in message ... Jim, How do you actually enable or disable a button? -- Trefor "Jim Thomlinson" wrote: You can use the worksheet change and sheet activate events to detect the value of Cell A1. Based on that you can either hide or enable/disable the button (I perfer enable/disable as the user know it is there is is just greyed out if it is disabled) The button that you have... is it from the control toolbox or the forms toolbar. control toolbox puts the code in the sheet, while forms toolbar attaches to code written in a module. It makes a difference because you have to be able to find the button in your code and the syntax is different depending on the type of button. Private Sub Worksheet_Activate() If ActiveSheet.Range("A1") = 1 Then MsgBox "Unhide the button" Else MsgBox "Hide the button" End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" And Target.Value = 1 Then MsgBox "Unhide the button" Else MsgBox "Hide the button" End If End Sub -- HTH... Jim Thomlinson "gatesheadthunde" wrote: Hi, Just wondering if anyone out there can help? I have a formula in cell A1 and if this is true it puts a '1' in A1 or and if false leaves this cell blank. What I am after is... Should cell A1 contain a number 1 then I want a button to be showing be available to click. However, if this cell contains anything else I want the button to be hidden. Is this possible? If so, how? Many thanks :cool: -- gatesheadthunde ------------------------------------------------------------------------ gatesheadthunde's Profile: http://www.excelforum.com/member.php...o&userid=10536 View this thread: http://www.excelforum.com/showthread...hreadid=466312 |
Hiding a Button
Sean,
Ok, what you are talking about os a "CommandButton" and the procedure as you describe works fine. I am using a Userforms "Button" and the procedure you describe does not work here. -- Trefor "Sean" wrote: Try this: You may have some other setting that is causing problems. I do not know enough about this to suggest where to look. Try this in a new instance of Excel: Open excel, ensure your Control toolbox toolbar is visible (right click on the menu and make sure Control toolbox is checked. Enable the properties (click properties button) The Worksheet will be selected as a default (I think that you have got this far) Create a command button (click command button on Control toolbar and click somewhere in workbook) Now click on the command button (the properties window should be listing the properties for CommandButton1) As for finding this in help try this: From Microsoft visual basic click the object browser button on the standard toolbar (looks like a box with some toys being thrown in) Under classes select the commandbutton item. Good luck. Sean "Trefor" wrote in message ... Sean, Sorry I must be missing something here. If I select the button, then through Control Toolbox I select properties, it only seems to display the properties for the sheet not the button. I used: ActiveSheet.Shapes("Button 30").Visible = False And this works in making it visible or not, but I would like to keep it visible, but "greyed out" and disabled. I thought the following might work: ActiveSheet.Shapes("Button 30").Enabled = False but Enabled is not valid. Iand... I could not find this in Help. -- Trefor "Sean Bartleet" wrote: There is an Enabled Property for command buttons. Select the button and click the properties button on the control box toolbar, look under Enabled. Or myBar.Enabled = False From VBA Check out help and look at the object browser under CommandButton I am not sure if this is what you were looking for. Sean. "Trefor" wrote in message ... Jim, How do you actually enable or disable a button? -- Trefor "Jim Thomlinson" wrote: You can use the worksheet change and sheet activate events to detect the value of Cell A1. Based on that you can either hide or enable/disable the button (I perfer enable/disable as the user know it is there is is just greyed out if it is disabled) The button that you have... is it from the control toolbox or the forms toolbar. control toolbox puts the code in the sheet, while forms toolbar attaches to code written in a module. It makes a difference because you have to be able to find the button in your code and the syntax is different depending on the type of button. Private Sub Worksheet_Activate() If ActiveSheet.Range("A1") = 1 Then MsgBox "Unhide the button" Else MsgBox "Hide the button" End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" And Target.Value = 1 Then MsgBox "Unhide the button" Else MsgBox "Hide the button" End If End Sub -- HTH... Jim Thomlinson "gatesheadthunde" wrote: Hi, Just wondering if anyone out there can help? I have a formula in cell A1 and if this is true it puts a '1' in A1 or and if false leaves this cell blank. What I am after is... Should cell A1 contain a number 1 then I want a button to be showing be available to click. However, if this cell contains anything else I want the button to be hidden. Is this possible? If so, how? Many thanks :cool: -- gatesheadthunde ------------------------------------------------------------------------ gatesheadthunde's Profile: http://www.excelforum.com/member.php...o&userid=10536 View this thread: http://www.excelforum.com/showthread...hreadid=466312 |
Hiding a Button
Button 30 is a name more typical to a button from the forms toolbar.
in that case Activesheet.Buttons("button 30").Enabled = False works fine, but it doesn't change the appearance of the button - it just doesn't respond to a click. You could change the font color ActiveSheet.buttons("Button 2").Font.ColorIndex = 48 -- Regards, Tom Ogilvy "Trefor" wrote in message ... Sean, Ok, what you are talking about os a "CommandButton" and the procedure as you describe works fine. I am using a Userforms "Button" and the procedure you describe does not work here. -- Trefor "Sean" wrote: Try this: You may have some other setting that is causing problems. I do not know enough about this to suggest where to look. Try this in a new instance of Excel: Open excel, ensure your Control toolbox toolbar is visible (right click on the menu and make sure Control toolbox is checked. Enable the properties (click properties button) The Worksheet will be selected as a default (I think that you have got this far) Create a command button (click command button on Control toolbar and click somewhere in workbook) Now click on the command button (the properties window should be listing the properties for CommandButton1) As for finding this in help try this: From Microsoft visual basic click the object browser button on the standard toolbar (looks like a box with some toys being thrown in) Under classes select the commandbutton item. Good luck. Sean "Trefor" wrote in message ... Sean, Sorry I must be missing something here. If I select the button, then through Control Toolbox I select properties, it only seems to display the properties for the sheet not the button. I used: ActiveSheet.Shapes("Button 30").Visible = False And this works in making it visible or not, but I would like to keep it visible, but "greyed out" and disabled. I thought the following might work: ActiveSheet.Shapes("Button 30").Enabled = False but Enabled is not valid. Iand... I could not find this in Help. -- Trefor "Sean Bartleet" wrote: There is an Enabled Property for command buttons. Select the button and click the properties button on the control box toolbar, look under Enabled. Or myBar.Enabled = False From VBA Check out help and look at the object browser under CommandButton I am not sure if this is what you were looking for. Sean. "Trefor" wrote in message ... Jim, How do you actually enable or disable a button? -- Trefor "Jim Thomlinson" wrote: You can use the worksheet change and sheet activate events to detect the value of Cell A1. Based on that you can either hide or enable/disable the button (I perfer enable/disable as the user know it is there is is just greyed out if it is disabled) The button that you have... is it from the control toolbox or the forms toolbar. control toolbox puts the code in the sheet, while forms toolbar attaches to code written in a module. It makes a difference because you have to be able to find the button in your code and the syntax is different depending on the type of button. Private Sub Worksheet_Activate() If ActiveSheet.Range("A1") = 1 Then MsgBox "Unhide the button" Else MsgBox "Hide the button" End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" And Target.Value = 1 Then MsgBox "Unhide the button" Else MsgBox "Hide the button" End If End Sub -- HTH... Jim Thomlinson "gatesheadthunde" wrote: Hi, Just wondering if anyone out there can help? I have a formula in cell A1 and if this is true it puts a '1' in A1 or and if false leaves this cell blank. What I am after is... Should cell A1 contain a number 1 then I want a button to be showing be available to click. However, if this cell contains anything else I want the button to be hidden. Is this possible? If so, how? Many thanks :cool: -- gatesheadthunde ------------------------------------------------------------------------ gatesheadthunde's Profile: http://www.excelforum.com/member.php...o&userid=10536 View this thread: http://www.excelforum.com/showthread...hreadid=466312 |
Hiding a Button
Tom,
Thankyou, I may stick with what I have got ,thought there might be an easier way. -- Trefor "Tom Ogilvy" wrote: Button 30 is a name more typical to a button from the forms toolbar. in that case Activesheet.Buttons("button 30").Enabled = False works fine, but it doesn't change the appearance of the button - it just doesn't respond to a click. You could change the font color ActiveSheet.buttons("Button 2").Font.ColorIndex = 48 -- Regards, Tom Ogilvy "Trefor" wrote in message ... Sean, Ok, what you are talking about os a "CommandButton" and the procedure as you describe works fine. I am using a Userforms "Button" and the procedure you describe does not work here. -- Trefor "Sean" wrote: Try this: You may have some other setting that is causing problems. I do not know enough about this to suggest where to look. Try this in a new instance of Excel: Open excel, ensure your Control toolbox toolbar is visible (right click on the menu and make sure Control toolbox is checked. Enable the properties (click properties button) The Worksheet will be selected as a default (I think that you have got this far) Create a command button (click command button on Control toolbar and click somewhere in workbook) Now click on the command button (the properties window should be listing the properties for CommandButton1) As for finding this in help try this: From Microsoft visual basic click the object browser button on the standard toolbar (looks like a box with some toys being thrown in) Under classes select the commandbutton item. Good luck. Sean "Trefor" wrote in message ... Sean, Sorry I must be missing something here. If I select the button, then through Control Toolbox I select properties, it only seems to display the properties for the sheet not the button. I used: ActiveSheet.Shapes("Button 30").Visible = False And this works in making it visible or not, but I would like to keep it visible, but "greyed out" and disabled. I thought the following might work: ActiveSheet.Shapes("Button 30").Enabled = False but Enabled is not valid. Iand... I could not find this in Help. -- Trefor "Sean Bartleet" wrote: There is an Enabled Property for command buttons. Select the button and click the properties button on the control box toolbar, look under Enabled. Or myBar.Enabled = False From VBA Check out help and look at the object browser under CommandButton I am not sure if this is what you were looking for. Sean. "Trefor" wrote in message ... Jim, How do you actually enable or disable a button? -- Trefor "Jim Thomlinson" wrote: You can use the worksheet change and sheet activate events to detect the value of Cell A1. Based on that you can either hide or enable/disable the button (I perfer enable/disable as the user know it is there is is just greyed out if it is disabled) The button that you have... is it from the control toolbox or the forms toolbar. control toolbox puts the code in the sheet, while forms toolbar attaches to code written in a module. It makes a difference because you have to be able to find the button in your code and the syntax is different depending on the type of button. Private Sub Worksheet_Activate() If ActiveSheet.Range("A1") = 1 Then MsgBox "Unhide the button" Else MsgBox "Hide the button" End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" And Target.Value = 1 Then MsgBox "Unhide the button" Else MsgBox "Hide the button" End If End Sub -- HTH... Jim Thomlinson "gatesheadthunde" wrote: Hi, Just wondering if anyone out there can help? I have a formula in cell A1 and if this is true it puts a '1' in A1 or and if false leaves this cell blank. What I am after is... Should cell A1 contain a number 1 then I want a button to be showing be available to click. However, if this cell contains anything else I want the button to be hidden. Is this possible? If so, how? Many thanks :cool: -- gatesheadthunde ------------------------------------------------------------------------ gatesheadthunde's Profile: http://www.excelforum.com/member.php...o&userid=10536 View this thread: http://www.excelforum.com/showthread...hreadid=466312 |
| All times are GMT +1. The time now is 12:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com