PDA

View Full Version : Solved: SETTING USER TO CLOSE WORKBOOK IN A BUTTON NOT IN WINDOW



jammer6_9
04-12-2007, 06:56 AM
I want a user to close in a button that i will provide not in the sheetwindow but code that I used in the userform do not exist in the workbook... :help

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If CLOSEMODE = 0 Then Cancel = True
End Sub

gnod
04-12-2007, 07:18 AM
use this command to close the current workbook..


Thisworkbook.Close

jammer6_9
04-12-2007, 07:22 AM
What I want to happen is deactivate the workbook to close in the window and instead i'll be inserting a macro button to close the workbook...

gnod
04-12-2007, 07:45 AM
maybe you can use this as a workaround to force the user to use your button.


Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "Use the Close button."
Cancel = True
End Sub

lucas
04-12-2007, 08:00 AM
Hi Jammer,
I'm not following exactly what your trying to do...
Please clarify:
you have a workbook open and you have a userform
on the userform you have a button which you wish to use to close the active workbook but leave excel open?

jammer6_9
04-12-2007, 09:12 AM
'all apologies for my explanation :banghead: ... i was just comparing a userform to a workbook. The code that i have written below is for the userform to let user close in a button not in the window closemode which I wanted to apply it in a worksheet.

This code is for my userform_BeforeClose which is okay


Private Sub Userform_BeforeClose(Cancel As Boolean)
If CLOSEMODE = 0 Then Cancel = True
End Sub


I am trying this in the workbook but it does not work.


Private Sub WorkBook_BeforeClose(Cancel As Boolean)
If CLOSEMODE = 0 Then Cancel = True
End Sub


Hi Jammer,
I'm not following exactly what your trying to do...
Please clarify:
you have a workbook open and you have a userform
on the userform you have a button which you wish to use to close the active workbook but leave excel open?

jammer6_9
04-12-2007, 09:19 AM
gnod your code for a turnaround works but why my commanbutton that i wanted a user to click to close the workbook is disabled giving msgbox...


maybe you can use this as a workaround to force the user to use your button.


Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "Use the Close button."
Cancel = True
End Sub

lucas
04-12-2007, 09:19 AM
I'm sorry.....I don't know what CLOSEMODE is.....
can you tell us exactly what you want to happen to the form before close and to the worksheet before close?

feathers212
04-12-2007, 09:25 AM
How about this? Use this in the sheet code. I am interpretting CloseMode as the signal triggering that the Close button has been clicked.


Public CloseMode As Integer

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If CloseMode = 0 Then
MsgBox "Use the Close button."
Cancel = True
Else: End If
End Sub

Private Sub CloseButton_Click()
CloseMode = 1
ThisWorkbook.Close
End Sub

jammer6_9
04-12-2007, 09:32 AM
again i apologize... i guess i'm not using the correct term. i am attaching my workbook. please find attache file.

jammer6_9
04-12-2007, 09:43 AM
:whistle: tnxxx feathers... :clap: tnxxx for all the response people...


How about this? Use this in the sheet code. I am interpretting CloseMode as the signal triggering that the Close button has been clicked.


Public CloseMode As Integer

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If CloseMode = 0 Then
MsgBox "Use the Close button."
Cancel = True
Else: End If
End Sub

Private Sub CloseButton_Click()
CloseMode = 1
ThisWorkbook.Close
End Sub

lucas
04-12-2007, 09:46 AM
I think feathers has you figured out. I did put the public declaration in a public module.

code in standard module and in thisworkbook module...

see attached

jammer6_9
04-12-2007, 09:52 AM
yes Lucas feathers did... i apologize for my poor explanation and i am quite sure that if i made it clear at the first place, you will come to figure out earlier... again thnks...


I think feathers has you figured out. I did put the public declaration in a public module.

code in standard module and in thisworkbook module...

see attached

lucas
04-12-2007, 09:54 AM
No problem jammer.......I'm a pretty thick sculled american so it takes a little time to get me to understand...glad you got it hashed out...

feathers212
04-12-2007, 10:11 AM
I think feathers has you figured out. I did put the public declaration in a public module.

code in standard module and in thisworkbook module...

see attached
Lucas, thanks for finalizing everything. I knew that it would work together somehow, but I'm still learning about global variables myself. Wasn't sure where to place everything.

lucas
04-12-2007, 10:16 AM
Keep on helping me feathers. Sometimes it takes a community effort especially when language becomes a barrier.

lucas
04-12-2007, 10:19 AM
yes Lucas feathers did... i apologize for my poor explanation and i am quite sure that if i made it clear at the first place, you will come to figure out earlier... again thnks...

No apology necessary jammer. Just be patient and help us to understand your problem....we will eventually get it worked out. I'm the one who envies your ability to commicate in other than your native language so well.

jammer6_9
04-12-2007, 11:25 AM
:friends:

T-together
E-evryone
A-achieves
M-more

jammer6_9
04-12-2007, 11:36 AM
guys i have one more thing that i wanted to happen on my workbook. since i have set
Application.CommandBars("Worksheet Menu Bar").Enabled = False
can i make my own mymenubar? And on mymenubar can i set macro buttons with it...: pray2: how:dunno

lucas
04-12-2007, 11:40 AM
http://vbaexpress.com/kb/getarticle.php?kb_id=921&PHPSESSID=71970da6b12e95cc6da939ad800497d1

jammer6_9
04-12-2007, 11:51 AM
i knew it... you guys are leading me in the right direction. that was a good example of yours lucas. i really appreciate it. I will be working on it ASAP. once again a big dump of thanks...:beerchug:

lucas
04-12-2007, 11:55 AM
feathers and I usually get paid in vodka.....we'll see you at the pub.

feathers212
04-12-2007, 01:14 PM
feathers and I usually get paid in vodka.....we'll see you at the pub.
Rum or gin works for me:)

jammer6_9
04-12-2007, 01:23 PM
:yes feathers


Rum or gin works for me:)