Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable Close Button
I wanted to disable the close button in the upper right hand corner of Excel
so users would have to use a button on the spreadsheet (assigned to a macro) to close the worksheet. I used the following code: Private Sub WorkBook_BeforeClose (Cancel As Boolean) Cancel = True End Sub The code works great but now my macro button will not work and I can't close the worksheet at all. Can you help me out? I'd appreciate it! -- Dan N |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable Close Button
In your code that closes the workbook, you'll want to make sure that you don't
allow the workbook_beforeclose event to run normally (cancel = true). I'd put this in at the top of a General Module: Public BlkProc as Boolean By declaring this variable outside a procedure and making it public, each routine can read the value in that variable. Then in your code that closes the workbook: BlkProc = true thisworkbook.close savechanges:=true 'or false??? And then change the workbook_beforeclose event to look at that variable: Private Sub WorkBook_BeforeClose (Cancel As Boolean) if blkproc = true then 'do nothing, let the workbook close else Cancel = True end if End Sub Dan wrote: I wanted to disable the close button in the upper right hand corner of Excel so users would have to use a button on the spreadsheet (assigned to a macro) to close the worksheet. I used the following code: Private Sub WorkBook_BeforeClose (Cancel As Boolean) Cancel = True End Sub The code works great but now my macro button will not work and I can't close the worksheet at all. Can you help me out? I'd appreciate it! -- Dan N -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable Close Button
Dave
Thank you for your response. I follow everything you said except the General Module part. I'm not sure what a general module is and exactly where to put the code Public BlkProc as Boolean. I put it in the ThisWorkbook module but it is still not responding. -- Dan N "Dave Peterson" wrote: In your code that closes the workbook, you'll want to make sure that you don't allow the workbook_beforeclose event to run normally (cancel = true). I'd put this in at the top of a General Module: Public BlkProc as Boolean By declaring this variable outside a procedure and making it public, each routine can read the value in that variable. Then in your code that closes the workbook: BlkProc = true thisworkbook.close savechanges:=true 'or false??? And then change the workbook_beforeclose event to look at that variable: Private Sub WorkBook_BeforeClose (Cancel As Boolean) if blkproc = true then 'do nothing, let the workbook close else Cancel = True end if End Sub Dan wrote: I wanted to disable the close button in the upper right hand corner of Excel so users would have to use a button on the spreadsheet (assigned to a macro) to close the worksheet. I used the following code: Private Sub WorkBook_BeforeClose (Cancel As Boolean) Cancel = True End Sub The code works great but now my macro button will not work and I can't close the worksheet at all. Can you help me out? I'd appreciate it! -- Dan N -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable Close Button
Select your project in the VBE.
Insert|module and put the declaration there. Dan wrote: Dave Thank you for your response. I follow everything you said except the General Module part. I'm not sure what a general module is and exactly where to put the code Public BlkProc as Boolean. I put it in the ThisWorkbook module but it is still not responding. -- Dan N "Dave Peterson" wrote: In your code that closes the workbook, you'll want to make sure that you don't allow the workbook_beforeclose event to run normally (cancel = true). I'd put this in at the top of a General Module: Public BlkProc as Boolean By declaring this variable outside a procedure and making it public, each routine can read the value in that variable. Then in your code that closes the workbook: BlkProc = true thisworkbook.close savechanges:=true 'or false??? And then change the workbook_beforeclose event to look at that variable: Private Sub WorkBook_BeforeClose (Cancel As Boolean) if blkproc = true then 'do nothing, let the workbook close else Cancel = True end if End Sub Dan wrote: I wanted to disable the close button in the upper right hand corner of Excel so users would have to use a button on the spreadsheet (assigned to a macro) to close the worksheet. I used the following code: Private Sub WorkBook_BeforeClose (Cancel As Boolean) Cancel = True End Sub The code works great but now my macro button will not work and I can't close the worksheet at all. Can you help me out? I'd appreciate it! -- Dan N -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable Close Button
Dave,
It is working perfectly! Thank you so much. I really appreciate your help and quick response. -- Dan N "Dave Peterson" wrote: Select your project in the VBE. Insert|module and put the declaration there. Dan wrote: Dave Thank you for your response. I follow everything you said except the General Module part. I'm not sure what a general module is and exactly where to put the code Public BlkProc as Boolean. I put it in the ThisWorkbook module but it is still not responding. -- Dan N "Dave Peterson" wrote: In your code that closes the workbook, you'll want to make sure that you don't allow the workbook_beforeclose event to run normally (cancel = true). I'd put this in at the top of a General Module: Public BlkProc as Boolean By declaring this variable outside a procedure and making it public, each routine can read the value in that variable. Then in your code that closes the workbook: BlkProc = true thisworkbook.close savechanges:=true 'or false??? And then change the workbook_beforeclose event to look at that variable: Private Sub WorkBook_BeforeClose (Cancel As Boolean) if blkproc = true then 'do nothing, let the workbook close else Cancel = True end if End Sub Dan wrote: I wanted to disable the close button in the upper right hand corner of Excel so users would have to use a button on the spreadsheet (assigned to a macro) to close the worksheet. I used the following code: Private Sub WorkBook_BeforeClose (Cancel As Boolean) Cancel = True End Sub The code works great but now my macro button will not work and I can't close the worksheet at all. Can you help me out? I'd appreciate it! -- Dan N -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable Close Button
Glad you got it working.
Dan wrote: Dave, It is working perfectly! Thank you so much. I really appreciate your help and quick response. -- Dan N "Dave Peterson" wrote: Select your project in the VBE. Insert|module and put the declaration there. Dan wrote: Dave Thank you for your response. I follow everything you said except the General Module part. I'm not sure what a general module is and exactly where to put the code Public BlkProc as Boolean. I put it in the ThisWorkbook module but it is still not responding. -- Dan N "Dave Peterson" wrote: In your code that closes the workbook, you'll want to make sure that you don't allow the workbook_beforeclose event to run normally (cancel = true). I'd put this in at the top of a General Module: Public BlkProc as Boolean By declaring this variable outside a procedure and making it public, each routine can read the value in that variable. Then in your code that closes the workbook: BlkProc = true thisworkbook.close savechanges:=true 'or false??? And then change the workbook_beforeclose event to look at that variable: Private Sub WorkBook_BeforeClose (Cancel As Boolean) if blkproc = true then 'do nothing, let the workbook close else Cancel = True end if End Sub Dan wrote: I wanted to disable the close button in the upper right hand corner of Excel so users would have to use a button on the spreadsheet (assigned to a macro) to close the worksheet. I used the following code: Private Sub WorkBook_BeforeClose (Cancel As Boolean) Cancel = True End Sub The code works great but now my macro button will not work and I can't close the worksheet at all. Can you help me out? I'd appreciate it! -- Dan N -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use the Merge & Center Button with unprotected Cells - sheet prote | Setting up and Configuration of Excel | |||
Disable Exit button on a UserForm | Excel Discussion (Misc queries) | |||
Disable "save" option on close. | Excel Discussion (Misc queries) | |||
Userform + close button | Excel Discussion (Misc queries) | |||
VBA: Disable Frame and Radio Buttons based on Another Radio Button Being True | Excel Worksheet Functions |