Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
disable pushbutton on spreadsheet
Is it possible to disable a pushbutton that is placed on
top of my spreadsheet?? Basically, if the button is pressed I will display my userform object. If the userform is visible/loaded, I want the button to be disabled. Then, when the userform is closed out I want to re-enable the button again. thank u |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
disable pushbutton on spreadsheet
I had this in a General module:
Option Explicit Sub testme() UserForm1.Show False End Sub This was the macro that I assigned to the button from the Forms toolbar. The button was on Sheet1 and named "Button 1". If you used a commandbutton from the control toolbox toolbar, then this code was behind the worksheet that held that commandbutton: Option Explicit Private Sub CommandButton1_Click() UserForm1.Show False End Sub The name of this button was "CommandButton1". I didn't know what kind of button you used on the sheet. Then my userform had a commandbutton that would cancel the userform. This was named Commandbutton2. This is the code that was behind the Userform. Option Explicit Private Sub CommandButton2_Click() 'cancel button on the userform ThisWorkbook.Worksheets("sheet1").Buttons("button 1").Enabled = True ThisWorkbook.Worksheets("Sheet1").CommandButton1.E nabled = True Unload Me End Sub Private Sub UserForm_Initialize() ThisWorkbook.Worksheets("sheet1").Buttons("button 1").Enabled = False ThisWorkbook.Worksheets("Sheet1").CommandButton1.E nabled = False End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then Call CommandButton2_Click End If End Sub Delete the pair of lines (commandbutton or "button 1") depending on what kind of button you used. Remember to change the sheetname and the button/commandbutton to match what you need. ps. When the commandbutton was disabled, it looked disabled. The button from the Forms toolbar didn't look as nice. (You could change the code to hide/show it or even change the colors/caption if that was a problem.) Robert Crandal wrote: Is it possible to disable a pushbutton that is placed on top of my spreadsheet?? Basically, if the button is pressed I will display my userform object. If the userform is visible/loaded, I want the button to be disabled. Then, when the userform is closed out I want to re-enable the button again. thank u -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
disable pushbutton on spreadsheet
Hi Robert,
I have assumed that you are using an AxtiveX command button from the Controls Toolbox toolbar. (not a button from the Forms toolbar.) Also assumed that the form is Modeless otherwise the button is not accessable while the form is open anyway. Use the following code for the command button. Private Sub CommandButton1_Click() Me.CommandButton1.Enabled = False UserForm1.Show vbModeless End Sub Insert the following code in the forms code module. Private Sub UserForm_Terminate() 'Edit "Sheet1" to your worksheet name Sheets("Sheet1").CommandButton1.Enabled = True End Sub -- Regards, OssieMac "Robert Crandal" wrote: Is it possible to disable a pushbutton that is placed on top of my spreadsheet?? Basically, if the button is pressed I will display my userform object. If the userform is visible/loaded, I want the button to be disabled. Then, when the userform is closed out I want to re-enable the button again. thank u . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
disable pushbutton on spreadsheet
I do not use the CommandButton1 on my Sheet1. I am just using a regular
push button on top of my sheet which has the caption of "Button 1". I tried using your code below, but it did not seem to work. I got a subscript out of range error. Does my button caption need to be "button 1" or "button_1" or something else?? I don't know what I'm doing wrong. thank you "Dave Peterson" wrote in message ... Option Explicit Private Sub CommandButton2_Click() 'cancel button on the userform ThisWorkbook.Worksheets("sheet1").Buttons("button 1").Enabled = True Unload Me End Sub Private Sub UserForm_Initialize() ThisWorkbook.Worksheets("sheet1").Buttons("button 1").Enabled = False End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
disable pushbutton on spreadsheet
Hi again Robert,
If you are using a forms button then you will probably be better to use Dave's suggestion to make the button not visible because disabling a forms button does not change its looks like graying out the caption. If you use the following code for your Forms button click then it will hide the button when it shows the userform. Sub Button1_Click() 'Edit "Sheet1" to your worksheet name ThisWorkbook.Worksheets("Sheet1") _ .Buttons("Button 1").Visible = False UserForm1.Show vbModeless End Sub Then insert the following code in the forms code area and the button will be visible again when the form is closed. Private Sub UserForm_Terminate() 'Edit "Sheet1" to your worksheet name ThisWorkbook.Worksheets("Sheet1") _ .Buttons("Button 1").Visible = True End Sub Note that a space and underscore at the end of a line is a line break in an otherwise single line of code. -- Regards, OssieMac "Robert Crandal" wrote: I do not use the CommandButton1 on my Sheet1. I am just using a regular push button on top of my sheet which has the caption of "Button 1". I tried using your code below, but it did not seem to work. I got a subscript out of range error. Does my button caption need to be "button 1" or "button_1" or something else?? I don't know what I'm doing wrong. thank you "Dave Peterson" wrote in message ... Option Explicit Private Sub CommandButton2_Click() 'cancel button on the userform ThisWorkbook.Worksheets("sheet1").Buttons("button 1").Enabled = True Unload Me End Sub Private Sub UserForm_Initialize() ThisWorkbook.Worksheets("sheet1").Buttons("button 1").Enabled = False End Sub . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
disable pushbutton on spreadsheet
Is the code in the same workbook as the button?
Is the name of the sheet really Sheet1? Did you assign the macro to show the userform to the correct button? If this doesn't help, you may want to share the code you tried, where you put it and the names of those objects. Robert Crandal wrote: I do not use the CommandButton1 on my Sheet1. I am just using a regular push button on top of my sheet which has the caption of "Button 1". I tried using your code below, but it did not seem to work. I got a subscript out of range error. Does my button caption need to be "button 1" or "button_1" or something else?? I don't know what I'm doing wrong. thank you "Dave Peterson" wrote in message ... Option Explicit Private Sub CommandButton2_Click() 'cancel button on the userform ThisWorkbook.Worksheets("sheet1").Buttons("button 1").Enabled = True Unload Me End Sub Private Sub UserForm_Initialize() ThisWorkbook.Worksheets("sheet1").Buttons("button 1").Enabled = False End Sub -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
disable pushbutton on spreadsheet
Ps. It's not the caption of the button that's important to the code. It's the
name of the button that's important. Select the button (right click on it should do fine). Then look in the Namebox (to the left of the formula bar) to see the name that the code has to use. pps. I'd change the caption to something meaningful the user would understand. Robert Crandal wrote: I do not use the CommandButton1 on my Sheet1. I am just using a regular push button on top of my sheet which has the caption of "Button 1". I tried using your code below, but it did not seem to work. I got a subscript out of range error. Does my button caption need to be "button 1" or "button_1" or something else?? I don't know what I'm doing wrong. thank you "Dave Peterson" wrote in message ... Option Explicit Private Sub CommandButton2_Click() 'cancel button on the userform ThisWorkbook.Worksheets("sheet1").Buttons("button 1").Enabled = True Unload Me End Sub Private Sub UserForm_Initialize() ThisWorkbook.Worksheets("sheet1").Buttons("button 1").Enabled = False End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I disable all hyperlinks in a spreadsheet? | Excel Discussion (Misc queries) | |||
How do you create a pushbutton lookup control | Excel Worksheet Functions | |||
pushbutton to move data from one excel wkbk to another | Excel Programming | |||
Disable Right Click in Spreadsheet | Excel Programming | |||
Disable controls on spreadsheet | Excel Programming |