Activating Command Button on Workbook Being Opened

CorpusSpongiosum · Mar 18th, 2002, 10:12 AM

I have a workbook with two worksheets "Rota" and "Message Board". The "Rota" worksheet has a command button (Comman Button1) which when clicked saves the workbook, emails it to a specified destination, inactivates the command button and displays a message box indicating that the workbook has been emailed. Is it possible to include code that will re-activate the control button 1 in the event of the worksheet being re-opened. Is it also possible to include code to close the workbook upon the OK button of the message box being clicked on?

Many thanks

Craig

KniteMare · Mar 18th, 2002, 12:12 PM

You use the sheet activate event to do this.
You would paste your code there.

Frome the VBE choose the sheet ROTA
In the Code window select Activate from the pull down list on the right top of the window.
Paste the code there.
it will run when you click on the sheet tab.

as the last line of your code put

ThisWorkbook.Close

The workbook will close and if there are any changes ask you if you want to save them. to avoid this write

ThisWorkbook.Saved = True
ThisWorkbook.Close

The workbook will close and NOT save any changes made since the last time it was saved.

Juan Pablo González · Mar 18th, 2002, 09:31 PM

I feel the bottom line answer is:

CommandButton1.Enabled = True

at the Workbook_Open event ?

Mar 18th, 2002, 09:31 PM	#3
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	I feel the bottom line answer is: CommandButton1.Enabled = True at the Workbook_Open event ? __________________ Regards, Juan Pablo González http://www.juanpg.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 18th, 2002, 10:12 AM	#1
CorpusSpongiosum New Member Join Date: Mar 2002 Posts: 9	I have a workbook with two worksheets "Rota" and "Message Board". The "Rota" worksheet has a command button (Comman Button1) which when clicked saves the workbook, emails it to a specified destination, inactivates the command button and displays a message box indicating that the workbook has been emailed. Is it possible to include code that will re-activate the control button 1 in the event of the worksheet being re-opened. Is it also possible to include code to close the workbook upon the OK button of the message box being clicked on? Many thanks Craig

Mar 18th, 2002, 12:12 PM	#2
KniteMare Board Regular Join Date: Mar 2002 Location: Hilo, Hawaii Posts: 240	You use the sheet activate event to do this. You would paste your code there. Frome the VBE choose the sheet ROTA In the Code window select Activate from the pull down list on the right top of the window. Paste the code there. it will run when you click on the sheet tab. as the last line of your code put ThisWorkbook.Close The workbook will close and if there are any changes ask you if you want to save them. to avoid this write ThisWorkbook.Saved = True ThisWorkbook.Close The workbook will close and NOT save any changes made since the last time it was saved.