| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Office Excel 2003 Versions and VBA
Hi all,
Have whipped the following code in Excel/VBA and placed in the 'ThisWorkbook' module. The problem that I have is that it works well with other users using Excel 2003 Standard edition. I'm using Beginners edition - is that why the code won't run? The vba code: [-- code start --] Private Sub Workbook_Open() 'Prompt user on opening XLS to Print Worksheet and quit. Dim Resp Resp = MsgBox("Do you wish to Print and Exit ?", _ vbYesNo + vbQuestion, _ "Print & Exit ?") If Resp = vbYes Then ActiveWorkbook.PrintOut Application.Quit End If End Sub [-- code end --] Please help - this is driving me nuts. -- Cheers, Cameron |
|
#2
|
|||
|
|||
|
Which bit doesn't work?
-- HTH Bob Phillips "Cameron Young" wrote in message ... Hi all, Have whipped the following code in Excel/VBA and placed in the 'ThisWorkbook' module. The problem that I have is that it works well with other users using Excel 2003 Standard edition. I'm using Beginners edition - is that why the code won't run? The vba code: [-- code start --] Private Sub Workbook_Open() 'Prompt user on opening XLS to Print Worksheet and quit. Dim Resp Resp = MsgBox("Do you wish to Print and Exit ?", _ vbYesNo + vbQuestion, _ "Print & Exit ?") If Resp = vbYes Then ActiveWorkbook.PrintOut Application.Quit End If End Sub [-- code end --] Please help - this is driving me nuts. -- Cheers, Cameron |
|
#3
|
|||
|
|||
|
Hi Bob,
Actually, all of it. For some reason the Workbook_Open() event just doesn't seem to fire. -- Cheers, Cameron "Bob Phillips" wrote: Which bit doesn't work? -- HTH Bob Phillips "Cameron Young" wrote in message ... Hi all, Have whipped the following code in Excel/VBA and placed in the 'ThisWorkbook' module. The problem that I have is that it works well with other users using Excel 2003 Standard edition. I'm using Beginners edition - is that why the code won't run? The vba code: [-- code start --] Private Sub Workbook_Open() 'Prompt user on opening XLS to Print Worksheet and quit. Dim Resp Resp = MsgBox("Do you wish to Print and Exit ?", _ vbYesNo + vbQuestion, _ "Print & Exit ?") If Resp = vbYes Then ActiveWorkbook.PrintOut Application.Quit End If End Sub [-- code end --] Please help - this is driving me nuts. -- Cheers, Cameron |
|
#4
|
|||
|
|||
|
You did put it in the ThisWorkbook code module, not a standard code module?
-- HTH Bob Phillips "Cameron Young" wrote in message ... Hi Bob, Actually, all of it. For some reason the Workbook_Open() event just doesn't seem to fire. -- Cheers, Cameron "Bob Phillips" wrote: Which bit doesn't work? -- HTH Bob Phillips "Cameron Young" wrote in message ... Hi all, Have whipped the following code in Excel/VBA and placed in the 'ThisWorkbook' module. The problem that I have is that it works well with other users using Excel 2003 Standard edition. I'm using Beginners edition - is that why the code won't run? The vba code: [-- code start --] Private Sub Workbook_Open() 'Prompt user on opening XLS to Print Worksheet and quit. Dim Resp Resp = MsgBox("Do you wish to Print and Exit ?", _ vbYesNo + vbQuestion, _ "Print & Exit ?") If Resp = vbYes Then ActiveWorkbook.PrintOut Application.Quit End If End Sub [-- code end --] Please help - this is driving me nuts. -- Cheers, Cameron |
|
#5
|
|||
|
|||
|
Close that workbook (if it's open)
tools|macro|security...|security level tab Change it to medium (to be prompted each time a workbook containing macros opens) or change it to low (to always allow macros to run) Cameron Young wrote: Hi Bob, Actually, all of it. For some reason the Workbook_Open() event just doesn't seem to fire. -- Cheers, Cameron "Bob Phillips" wrote: Which bit doesn't work? -- HTH Bob Phillips "Cameron Young" wrote in message ... Hi all, Have whipped the following code in Excel/VBA and placed in the 'ThisWorkbook' module. The problem that I have is that it works well with other users using Excel 2003 Standard edition. I'm using Beginners edition - is that why the code won't run? The vba code: [-- code start --] Private Sub Workbook_Open() 'Prompt user on opening XLS to Print Worksheet and quit. Dim Resp Resp = MsgBox("Do you wish to Print and Exit ?", _ vbYesNo + vbQuestion, _ "Print & Exit ?") If Resp = vbYes Then ActiveWorkbook.PrintOut Application.Quit End If End Sub [-- code end --] Please help - this is driving me nuts. -- Cheers, Cameron -- Dave Peterson |
|
#6
|
|||
|
|||
|
Gents,
Thanks for your responces. Bob:: Workbook_Open() code is in the ThisWorkbook module. Dave:: Have already sert the macro security to low. And still nothing. -- Cheers, Cameron "Dave Peterson" wrote: Close that workbook (if it's open) tools|macro|security...|security level tab Change it to medium (to be prompted each time a workbook containing macros opens) or change it to low (to always allow macros to run) Cameron Young wrote: Hi Bob, Actually, all of it. For some reason the Workbook_Open() event just doesn't seem to fire. -- Cheers, Cameron "Bob Phillips" wrote: Which bit doesn't work? -- HTH Bob Phillips "Cameron Young" wrote in message ... Hi all, Have whipped the following code in Excel/VBA and placed in the 'ThisWorkbook' module. The problem that I have is that it works well with other users using Excel 2003 Standard edition. I'm using Beginners edition - is that why the code won't run? The vba code: [-- code start --] Private Sub Workbook_Open() 'Prompt user on opening XLS to Print Worksheet and quit. Dim Resp Resp = MsgBox("Do you wish to Print and Exit ?", _ vbYesNo + vbQuestion, _ "Print & Exit ?") If Resp = vbYes Then ActiveWorkbook.PrintOut Application.Quit End If End Sub [-- code end --] Please help - this is driving me nuts. -- Cheers, Cameron -- Dave Peterson |
|
#7
|
|||
|
|||
|
How do you open that workbook?
Do you open it using another macro in a different workbook? If yes, do you use a shortcut key assigned to that macro. If yes, does that shortcut key include the shiftkey. If yes (again!), then remove the shiftkey from the short cut key. === Do you have events enabled when you try to open the workbook? Cameron Young wrote: Gents, Thanks for your responces. Bob:: Workbook_Open() code is in the ThisWorkbook module. Dave:: Have already sert the macro security to low. And still nothing. -- Cheers, Cameron "Dave Peterson" wrote: Close that workbook (if it's open) tools|macro|security...|security level tab Change it to medium (to be prompted each time a workbook containing macros opens) or change it to low (to always allow macros to run) Cameron Young wrote: Hi Bob, Actually, all of it. For some reason the Workbook_Open() event just doesn't seem to fire. -- Cheers, Cameron "Bob Phillips" wrote: Which bit doesn't work? -- HTH Bob Phillips "Cameron Young" wrote in message ... Hi all, Have whipped the following code in Excel/VBA and placed in the 'ThisWorkbook' module. The problem that I have is that it works well with other users using Excel 2003 Standard edition. I'm using Beginners edition - is that why the code won't run? The vba code: [-- code start --] Private Sub Workbook_Open() 'Prompt user on opening XLS to Print Worksheet and quit. Dim Resp Resp = MsgBox("Do you wish to Print and Exit ?", _ vbYesNo + vbQuestion, _ "Print & Exit ?") If Resp = vbYes Then ActiveWorkbook.PrintOut Application.Quit End If End Sub [-- code end --] Please help - this is driving me nuts. -- Cheers, Cameron -- Dave Peterson -- Dave Peterson |
|
#8
|
|||
|
|||
|
I open the file by either:
a) Double-clicking the file from its folder. b) Using the Excel Recent FilecHistory list. c) Performing a "File | Open". -- Cheers, Cameron "Dave Peterson" wrote: How do you open that workbook? Do you open it using another macro in a different workbook? If yes, do you use a shortcut key assigned to that macro. If yes, does that shortcut key include the shiftkey. If yes (again!), then remove the shiftkey from the short cut key. === Do you have events enabled when you try to open the workbook? Cameron Young wrote: Gents, Thanks for your responces. Bob:: Workbook_Open() code is in the ThisWorkbook module. Dave:: Have already sert the macro security to low. And still nothing. -- Cheers, Cameron "Dave Peterson" wrote: Close that workbook (if it's open) tools|macro|security...|security level tab Change it to medium (to be prompted each time a workbook containing macros opens) or change it to low (to always allow macros to run) Cameron Young wrote: Hi Bob, Actually, all of it. For some reason the Workbook_Open() event just doesn't seem to fire. -- Cheers, Cameron "Bob Phillips" wrote: Which bit doesn't work? -- HTH Bob Phillips "Cameron Young" wrote in message ... Hi all, Have whipped the following code in Excel/VBA and placed in the 'ThisWorkbook' module. The problem that I have is that it works well with other users using Excel 2003 Standard edition. I'm using Beginners edition - is that why the code won't run? The vba code: [-- code start --] Private Sub Workbook_Open() 'Prompt user on opening XLS to Print Worksheet and quit. Dim Resp Resp = MsgBox("Do you wish to Print and Exit ?", _ vbYesNo + vbQuestion, _ "Print & Exit ?") If Resp = vbYes Then ActiveWorkbook.PrintOut Application.Quit End If End Sub [-- code end --] Please help - this is driving me nuts. -- Cheers, Cameron -- Dave Peterson -- Dave Peterson |
|
#9
|
|||
|
|||
|
Do other workbooks with macros run when you open them the same way?
Cameron Young wrote: I open the file by either: a) Double-clicking the file from its folder. b) Using the Excel Recent FilecHistory list. c) Performing a "File | Open". -- Cheers, Cameron "Dave Peterson" wrote: How do you open that workbook? Do you open it using another macro in a different workbook? If yes, do you use a shortcut key assigned to that macro. If yes, does that shortcut key include the shiftkey. If yes (again!), then remove the shiftkey from the short cut key. === Do you have events enabled when you try to open the workbook? Cameron Young wrote: Gents, Thanks for your responces. Bob:: Workbook_Open() code is in the ThisWorkbook module. Dave:: Have already sert the macro security to low. And still nothing. -- Cheers, Cameron "Dave Peterson" wrote: Close that workbook (if it's open) tools|macro|security...|security level tab Change it to medium (to be prompted each time a workbook containing macros opens) or change it to low (to always allow macros to run) Cameron Young wrote: Hi Bob, Actually, all of it. For some reason the Workbook_Open() event just doesn't seem to fire. -- Cheers, Cameron "Bob Phillips" wrote: Which bit doesn't work? -- HTH Bob Phillips "Cameron Young" wrote in message ... Hi all, Have whipped the following code in Excel/VBA and placed in the 'ThisWorkbook' module. The problem that I have is that it works well with other users using Excel 2003 Standard edition. I'm using Beginners edition - is that why the code won't run? The vba code: [-- code start --] Private Sub Workbook_Open() 'Prompt user on opening XLS to Print Worksheet and quit. Dim Resp Resp = MsgBox("Do you wish to Print and Exit ?", _ vbYesNo + vbQuestion, _ "Print & Exit ?") If Resp = vbYes Then ActiveWorkbook.PrintOut Application.Quit End If End Sub [-- code end --] Please help - this is driving me nuts. -- Cheers, Cameron -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#10
|
|||
|
|||
|
Have not yet tried.
The workbook I'm having trouble with does work as expected on other users pc's. .........[minutes later]....... No, sadly, no other xls workbooks with Workbook_Open() events are working. This is a problem. -- Cheers, Cameron "Dave Peterson" wrote: Do other workbooks with macros run when you open them the same way? Cameron Young wrote: I open the file by either: a) Double-clicking the file from its folder. b) Using the Excel Recent FilecHistory list. c) Performing a "File | Open". -- Cheers, Cameron "Dave Peterson" wrote: How do you open that workbook? Do you open it using another macro in a different workbook? If yes, do you use a shortcut key assigned to that macro. If yes, does that shortcut key include the shiftkey. If yes (again!), then remove the shiftkey from the short cut key. === Do you have events enabled when you try to open the workbook? Cameron Young wrote: Gents, Thanks for your responces. Bob:: Workbook_Open() code is in the ThisWorkbook module. Dave:: Have already sert the macro security to low. And still nothing. -- Cheers, Cameron "Dave Peterson" wrote: Close that workbook (if it's open) tools|macro|security...|security level tab Change it to medium (to be prompted each time a workbook containing macros opens) or change it to low (to always allow macros to run) Cameron Young wrote: Hi Bob, Actually, all of it. For some reason the Workbook_Open() event just doesn't seem to fire. -- Cheers, Cameron "Bob Phillips" wrote: Which bit doesn't work? -- HTH Bob Phillips "Cameron Young" wrote in message ... Hi all, Have whipped the following code in Excel/VBA and placed in the 'ThisWorkbook' module. The problem that I have is that it works well with other users using Excel 2003 Standard edition. I'm using Beginners edition - is that why the code won't run? The vba code: [-- code start --] Private Sub Workbook_Open() 'Prompt user on opening XLS to Print Worksheet and quit. Dim Resp Resp = MsgBox("Do you wish to Print and Exit ?", _ vbYesNo + vbQuestion, _ "Print & Exit ?") If Resp = vbYes Then ActiveWorkbook.PrintOut Application.Quit End If End Sub [-- code end --] Please help - this is driving me nuts. -- Cheers, Cameron -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#11
|
|||
|
|||
|
If you create a test subroutine in a test workbook:
Option Explicit sub testme01 msgbox "hi" exit sub Then close (and save) that test workbook. Then reopen it, can you run that sub yourself? If you rename TestMe01 to Auto_open, then close (and save) and reopen, does the Auto_open procedure run? (Testme and auto_open should be in a general module.) ====== Can you edit the workbook_open event code? If yes, can you just add: Msgbox "Hi" right near the top. Then you'll know if the event is firing (and something else breaks) or if it's the event that's not firing. (And double check that Tools|Macro|Security setting.) Cameron Young wrote: Have not yet tried. The workbook I'm having trouble with does work as expected on other users pc's. ........[minutes later]....... No, sadly, no other xls workbooks with Workbook_Open() events are working. This is a problem. -- Cheers, Cameron "Dave Peterson" wrote: Do other workbooks with macros run when you open them the same way? Cameron Young wrote: I open the file by either: a) Double-clicking the file from its folder. b) Using the Excel Recent FilecHistory list. c) Performing a "File | Open". -- Cheers, Cameron "Dave Peterson" wrote: How do you open that workbook? Do you open it using another macro in a different workbook? If yes, do you use a shortcut key assigned to that macro. If yes, does that shortcut key include the shiftkey. If yes (again!), then remove the shiftkey from the short cut key. === Do you have events enabled when you try to open the workbook? Cameron Young wrote: Gents, Thanks for your responces. Bob:: Workbook_Open() code is in the ThisWorkbook module. Dave:: Have already sert the macro security to low. And still nothing. -- Cheers, Cameron "Dave Peterson" wrote: Close that workbook (if it's open) tools|macro|security...|security level tab Change it to medium (to be prompted each time a workbook containing macros opens) or change it to low (to always allow macros to run) Cameron Young wrote: Hi Bob, Actually, all of it. For some reason the Workbook_Open() event just doesn't seem to fire. -- Cheers, Cameron "Bob Phillips" wrote: Which bit doesn't work? -- HTH Bob Phillips "Cameron Young" wrote in message ... Hi all, Have whipped the following code in Excel/VBA and placed in the 'ThisWorkbook' module. The problem that I have is that it works well with other users using Excel 2003 Standard edition. I'm using Beginners edition - is that why the code won't run? The vba code: [-- code start --] Private Sub Workbook_Open() 'Prompt user on opening XLS to Print Worksheet and quit. Dim Resp Resp = MsgBox("Do you wish to Print and Exit ?", _ vbYesNo + vbQuestion, _ "Print & Exit ?") If Resp = vbYes Then ActiveWorkbook.PrintOut Application.Quit End If End Sub [-- code end --] Please help - this is driving me nuts. -- Cheers, Cameron -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#12
|
|||
|
|||
|
Hi Dave,
Yes, the Auto_open() does work, but I was under the impression that this was an older (Excel) version process of the Workbook_Open(). -- Cheers, Cameron "Dave Peterson" wrote: If you create a test subroutine in a test workbook: Option Explicit sub testme01 msgbox "hi" exit sub Then close (and save) that test workbook. Then reopen it, can you run that sub yourself? If you rename TestMe01 to Auto_open, then close (and save) and reopen, does the Auto_open procedure run? (Testme and auto_open should be in a general module.) ====== Can you edit the workbook_open event code? If yes, can you just add: Msgbox "Hi" right near the top. Then you'll know if the event is firing (and something else breaks) or if it's the event that's not firing. (And double check that Tools|Macro|Security setting.) Cameron Young wrote: Have not yet tried. The workbook I'm having trouble with does work as expected on other users pc's. ........[minutes later]....... No, sadly, no other xls workbooks with Workbook_Open() events are working. This is a problem. -- Cheers, Cameron "Dave Peterson" wrote: Do other workbooks with macros run when you open them the same way? Cameron Young wrote: I open the file by either: a) Double-clicking the file from its folder. b) Using the Excel Recent FilecHistory list. c) Performing a "File | Open". -- Cheers, Cameron "Dave Peterson" wrote: How do you open that workbook? Do you open it using another macro in a different workbook? If yes, do you use a shortcut key assigned to that macro. If yes, does that shortcut key include the shiftkey. If yes (again!), then remove the shiftkey from the short cut key. === Do you have events enabled when you try to open the workbook? Cameron Young wrote: Gents, Thanks for your responces. Bob:: Workbook_Open() code is in the ThisWorkbook module. Dave:: Have already sert the macro security to low. And still nothing. -- Cheers, Cameron "Dave Peterson" wrote: Close that workbook (if it's open) tools|macro|security...|security level tab Change it to medium (to be prompted each time a workbook containing macros opens) or change it to low (to always allow macros to run) Cameron Young wrote: Hi Bob, Actually, all of it. For some reason the Workbook_Open() event just doesn't seem to fire. -- Cheers, Cameron "Bob Phillips" wrote: Which bit doesn't work? -- HTH Bob Phillips "Cameron Young" wrote in message ... Hi all, Have whipped the following code in Excel/VBA and placed in the 'ThisWorkbook' module. The problem that I have is that it works well with other users using Excel 2003 Standard edition. I'm using Beginners edition - is that why the code won't run? The vba code: [-- code start --] Private Sub Workbook_Open() 'Prompt user on opening XLS to Print Worksheet and quit. Dim Resp Resp = MsgBox("Do you wish to Print and Exit ?", _ vbYesNo + vbQuestion, _ "Print & Exit ?") If Resp = vbYes Then ActiveWorkbook.PrintOut Application.Quit End If End Sub [-- code end --] Please help - this is driving me nuts. -- Cheers, Cameron -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#13
|
|||
|
|||
|
xl97 added the workbook_open event, but auto_open is still supported.
Did you add that msgbox to the workbook_open event (near the top) to see if the event was actually started? == I have seen posts like this that: Private Sub Workbook_Open() Application.OnTime Now, "Continue_Open" End Sub Then in a general module: sub Continue_Open() 'your real code here end sub Sometimes there would be timing issues that could be avoided this way. (I've never seen this in real life, though--but others have.) Cameron Young wrote: Hi Dave, Yes, the Auto_open() does work, but I was under the impression that this was an older (Excel) version process of the Workbook_Open(). -- Cheers, Cameron "Dave Peterson" wrote: If you create a test subroutine in a test workbook: Option Explicit sub testme01 msgbox "hi" exit sub Then close (and save) that test workbook. Then reopen it, can you run that sub yourself? If you rename TestMe01 to Auto_open, then close (and save) and reopen, does the Auto_open procedure run? (Testme and auto_open should be in a general module.) ====== Can you edit the workbook_open event code? If yes, can you just add: Msgbox "Hi" right near the top. Then you'll know if the event is firing (and something else breaks) or if it's the event that's not firing. (And double check that Tools|Macro|Security setting.) Cameron Young wrote: Have not yet tried. The workbook I'm having trouble with does work as expected on other users pc's. ........[minutes later]....... No, sadly, no other xls workbooks with Workbook_Open() events are working. This is a problem. -- Cheers, Cameron "Dave Peterson" wrote: Do other workbooks with macros run when you open them the same way? Cameron Young wrote: I open the file by either: a) Double-clicking the file from its folder. b) Using the Excel Recent FilecHistory list. c) Performing a "File | Open". -- Cheers, Cameron "Dave Peterson" wrote: How do you open that workbook? Do you open it using another macro in a different workbook? If yes, do you use a shortcut key assigned to that macro. If yes, does that shortcut key include the shiftkey. If yes (again!), then remove the shiftkey from the short cut key. === Do you have events enabled when you try to open the workbook? Cameron Young wrote: Gents, Thanks for your responces. Bob:: Workbook_Open() code is in the ThisWorkbook module. Dave:: Have already sert the macro security to low. And still nothing. -- Cheers, Cameron "Dave Peterson" wrote: Close that workbook (if it's open) tools|macro|security...|security level tab Change it to medium (to be prompted each time a workbook containing macros opens) or change it to low (to always allow macros to run) Cameron Young wrote: Hi Bob, Actually, all of it. For some reason the Workbook_Open() event just doesn't seem to fire. -- Cheers, Cameron "Bob Phillips" wrote: Which bit doesn't work? -- HTH Bob Phillips "Cameron Young" wrote in message ... Hi all, Have whipped the following code in Excel/VBA and placed in the 'ThisWorkbook' module. The problem that I have is that it works well with other users using Excel 2003 Standard edition. I'm using Beginners edition - is that why the code won't run? The vba code: [-- code start --] Private Sub Workbook_Open() 'Prompt user on opening XLS to Print Worksheet and quit. Dim Resp Resp = MsgBox("Do you wish to Print and Exit ?", _ vbYesNo + vbQuestion, _ "Print & Exit ?") If Resp = vbYes Then ActiveWorkbook.PrintOut Application.Quit End If End Sub [-- code end --] Please help - this is driving me nuts. -- Cheers, Cameron -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#14
|
|||
|
|||
|
Hi again Dave,
I tried the code you provided using Workbook_Open() and the Application.OnTime statement. Sadly it still didn't work. This has absolutely got me miffed ! Could it at all be due to me using the Excel 2003 Basic Edition? -- Cheers, Cameron "Dave Peterson" wrote: xl97 added the workbook_open event, but auto_open is still supported. Did you add that msgbox to the workbook_open event (near the top) to see if the event was actually started? == I have seen posts like this that: Private Sub Workbook_Open() Application.OnTime Now, "Continue_Open" End Sub Then in a general module: sub Continue_Open() 'your real code here end sub Sometimes there would be timing issues that could be avoided this way. (I've never seen this in real life, though--but others have.) Cameron Young wrote: Hi Dave, Yes, the Auto_open() does work, but I was under the impression that this was an older (Excel) version process of the Workbook_Open(). -- Cheers, Cameron "Dave Peterson" wrote: If you create a test subroutine in a test workbook: Option Explicit sub testme01 msgbox "hi" exit sub Then close (and save) that test workbook. Then reopen it, can you run that sub yourself? If you rename TestMe01 to Auto_open, then close (and save) and reopen, does the Auto_open procedure run? (Testme and auto_open should be in a general module.) ====== Can you edit the workbook_open event code? If yes, can you just add: Msgbox "Hi" right near the top. Then you'll know if the event is firing (and something else breaks) or if it's the event that's not firing. (And double check that Tools|Macro|Security setting.) Cameron Young wrote: Have not yet tried. The workbook I'm having trouble with does work as expected on other users pc's. ........[minutes later]....... No, sadly, no other xls workbooks with Workbook_Open() events are working. This is a problem. -- Cheers, Cameron "Dave Peterson" wrote: Do other workbooks with macros run when you open them the same way? Cameron Young wrote: I open the file by either: a) Double-clicking the file from its folder. b) Using the Excel Recent FilecHistory list. c) Performing a "File | Open". -- Cheers, Cameron "Dave Peterson" wrote: How do you open that workbook? Do you open it using another macro in a different workbook? If yes, do you use a shortcut key assigned to that macro. If yes, does that shortcut key include the shiftkey. If yes (again!), then remove the shiftkey from the short cut key. === Do you have events enabled when you try to open the workbook? Cameron Young wrote: Gents, Thanks for your responces. Bob:: Workbook_Open() code is in the ThisWorkbook module. Dave:: Have already sert the macro security to low. And still nothing. -- Cheers, Cameron "Dave Peterson" wrote: Close that workbook (if it's open) tools|macro|security...|security level tab Change it to medium (to be prompted each time a workbook containing macros opens) or change it to low (to always allow macros to run) Cameron Young wrote: Hi Bob, Actually, all of it. For some reason the Workbook_Open() event just doesn't seem to fire. -- Cheers, Cameron "Bob Phillips" wrote: Which bit doesn't work? -- HTH Bob Phillips "Cameron Young" wrote in message ... Hi all, Have whipped the following code in Excel/VBA and placed in the 'ThisWorkbook' module. The problem that I have is that it works well with other users using Excel 2003 Standard edition. I'm using Beginners edition - is that why the code won't run? The vba code: [-- code start --] Private Sub Workbook_Open() 'Prompt user on opening XLS to Print Worksheet and quit. Dim Resp Resp = MsgBox("Do you wish to Print and Exit ?", _ vbYesNo + vbQuestion, _ "Print & Exit ?") If Resp = vbYes Then ActiveWorkbook.PrintOut Application.Quit End If End Sub [-- code end --] Please help - this is driving me nuts. -- Cheers, Cameron -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#15
|
|||
|
|||
|
I don't think so (about the xl2003 basic edition).
And you checked to see if the event was firing--with that msgbox stuff? Maybe it's firing and doing nothing for you: if somethingaboutyourpc = false then exit sub Have you spoken to the developer to see what he or she thinks? Cameron Young wrote: Hi again Dave, I tried the code you provided using Workbook_Open() and the Application.OnTime statement. Sadly it still didn't work. This has absolutely got me miffed ! Could it at all be due to me using the Excel 2003 Basic Edition? -- Cheers, Cameron "Dave Peterson" wrote: xl97 added the workbook_open event, but auto_open is still supported. Did you add that msgbox to the workbook_open event (near the top) to see if the event was actually started? == I have seen posts like this that: Private Sub Workbook_Open() Application.OnTime Now, "Continue_Open" End Sub Then in a general module: sub Continue_Open() 'your real code here end sub Sometimes there would be timing issues that could be avoided this way. (I've never seen this in real life, though--but others have.) Cameron Young wrote: Hi Dave, Yes, the Auto_open() does work, but I was under the impression that this was an older (Excel) version process of the Workbook_Open(). -- Cheers, Cameron "Dave Peterson" wrote: If you create a test subroutine in a test workbook: Option Explicit sub testme01 msgbox "hi" exit sub Then close (and save) that test workbook. Then reopen it, can you run that sub yourself? If you rename TestMe01 to Auto_open, then close (and save) and reopen, does the Auto_open procedure run? (Testme and auto_open should be in a general module.) ====== Can you edit the workbook_open event code? If yes, can you just add: Msgbox "Hi" right near the top. Then you'll know if the event is firing (and something else breaks) or if it's the event that's not firing. (And double check that Tools|Macro|Security setting.) Cameron Young wrote: Have not yet tried. The workbook I'm having trouble with does work as expected on other users pc's. ........[minutes later]....... No, sadly, no other xls workbooks with Workbook_Open() events are working. This is a problem. -- Cheers, Cameron "Dave Peterson" wrote: Do other workbooks with macros run when you open them the same way? Cameron Young wrote: I open the file by either: a) Double-clicking the file from its folder. b) Using the Excel Recent FilecHistory list. c) Performing a "File | Open". -- Cheers, Cameron "Dave Peterson" wrote: How do you open that workbook? Do you open it using another macro in a different workbook? If yes, do you use a shortcut key assigned to that macro. If yes, does that shortcut key include the shiftkey. If yes (again!), then remove the shiftkey from the short cut key. === Do you have events enabled when you try to open the workbook? Cameron Young wrote: Gents, Thanks for your responces. Bob:: Workbook_Open() code is in the ThisWorkbook module. Dave:: Have already sert the macro security to low. And still nothing. -- Cheers, Cameron "Dave Peterson" wrote: Close that workbook (if it's open) tools|macro|security...|security level tab Change it to medium (to be prompted each time a workbook containing macros opens) or change it to low (to always allow macros to run) Cameron Young wrote: Hi Bob, Actually, all of it. For some reason the Workbook_Open() event just doesn't seem to fire. -- Cheers, Cameron "Bob Phillips" wrote: Which bit doesn't work? -- HTH Bob Phillips "Cameron Young" wrote in message ... Hi all, Have whipped the following code in Excel/VBA and placed in the 'ThisWorkbook' module. The problem that I have is that it works well with other users using Excel 2003 Standard edition. I'm using Beginners edition - is that why the code won't run? The vba code: [-- code start --] Private Sub Workbook_Open() 'Prompt user on opening XLS to Print Worksheet and quit. Dim Resp Resp = MsgBox("Do you wish to Print and Exit ?", _ vbYesNo + vbQuestion, _ "Print & Exit ?") If Resp = vbYes Then ActiveWorkbook.PrintOut Application.Quit End If End Sub [-- code end --] Please help - this is driving me nuts. -- Cheers, Cameron -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#16
|
|||
|
|||
|
Hi Dave,
I developed the xls, I was just adding effectively two lines of code to ask the user on Workbook_Open() if they wanted to print the xls and exit. somethingaboutyourpc = false - It's a new laptop with all new software - standard installs. Using the Auto_Open() method, I'd resolved the issue with: Public Sub Auto_Open() 'Prompt user on opening XLS to Print Worksheet and quit. Dim Resp As Variant Resp = MsgBox("Do you wish to Print and Exit ?", _ vbYesNo + vbQuestion + vbSystemModal, _ "Print & Exit ?") If Resp = vbYes Then ActiveWorkbook.PrintOut ActiveWorkbook.Close SaveChanges:=False End If End Sub And it fires as expected. Not worth spending more time mulling over, but thank-you very much for your time and patience with this issue. Hope others have been able to benefit. -- Cheers, Cameron "Dave Peterson" wrote: I don't think so (about the xl2003 basic edition). And you checked to see if the event was firing--with that msgbox stuff? Maybe it's firing and doing nothing for you: if somethingaboutyourpc = false then exit sub Have you spoken to the developer to see what he or she thinks? Cameron Young wrote: Hi again Dave, I tried the code you provided using Workbook_Open() and the Application.OnTime statement. Sadly it still didn't work. This has absolutely got me miffed ! Could it at all be due to me using the Excel 2003 Basic Edition? -- Cheers, Cameron "Dave Peterson" wrote: xl97 added the workbook_open event, but auto_open is still supported. Did you add that msgbox to the workbook_open event (near the top) to see if the event was actually started? == I have seen posts like this that: Private Sub Workbook_Open() Application.OnTime Now, "Continue_Open" End Sub Then in a general module: sub Continue_Open() 'your real code here end sub Sometimes there would be timing issues that could be avoided this way. (I've never seen this in real life, though--but others have.) Cameron Young wrote: Hi Dave, Yes, the Auto_open() does work, but I was under the impression that this was an older (Excel) version process of the Workbook_Open(). -- Cheers, Cameron "Dave Peterson" wrote: If you create a test subroutine in a test workbook: Option Explicit sub testme01 msgbox "hi" exit sub Then close (and save) that test workbook. Then reopen it, can you run that sub yourself? If you rename TestMe01 to Auto_open, then close (and save) and reopen, does the Auto_open procedure run? (Testme and auto_open should be in a general module.) ====== Can you edit the workbook_open event code? If yes, can you just add: Msgbox "Hi" right near the top. Then you'll know if the event is firing (and something else breaks) or if it's the event that's not firing. (And double check that Tools|Macro|Security setting.) Cameron Young wrote: Have not yet tried. The workbook I'm having trouble with does work as expected on other users pc's. ........[minutes later]....... No, sadly, no other xls workbooks with Workbook_Open() events are working. This is a problem. -- Cheers, Cameron "Dave Peterson" wrote: Do other workbooks with macros run when you open them the same way? Cameron Young wrote: I open the file by either: a) Double-clicking the file from its folder. b) Using the Excel Recent FilecHistory list. c) Performing a "File | Open". -- Cheers, Cameron "Dave Peterson" wrote: How do you open that workbook? Do you open it using another macro in a different workbook? If yes, do you use a shortcut key assigned to that macro. If yes, does that shortcut key include the shiftkey. If yes (again!), then remove the shiftkey from the short cut key. === Do you have events enabled when you try to open the workbook? Cameron Young wrote: Gents, Thanks for your responces. Bob:: Workbook_Open() code is in the ThisWorkbook module. Dave:: Have already sert the macro security to low. And still nothing. -- Cheers, Cameron "Dave Peterson" wrote: Close that workbook (if it's open) tools|macro|security...|security level tab Change it to medium (to be prompted each time a workbook containing macros opens) or change it to low (to always allow macros to run) Cameron Young wrote: Hi Bob, Actually, all of it. For some reason the Workbook_Open() event just doesn't seem to fire. -- Cheers, Cameron "Bob Phillips" wrote: Which bit doesn't work? -- HTH Bob Phillips "Cameron Young" wrote in message ... Hi all, Have whipped the following code in Excel/VBA and placed in the 'ThisWorkbook' module. The problem that I have is that it works well with other users using Excel 2003 Standard edition. I'm using Beginners edition - is that why the code won't run? The vba code: [-- code start --] Private Sub Workbook_Open() 'Prompt user on opening XLS to Print Worksheet and quit. Dim Resp Resp = MsgBox("Do you wish to Print and Exit ?", _ vbYesNo + vbQuestion, _ "Print & Exit ?") If Resp = vbYes Then ActiveWorkbook.PrintOut Application.Quit End If End Sub [-- code end --] Please help - this is driving me nuts. -- Cheers, Cameron -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#17
|
|||
|
|||
|
Glad you found a workaround--but I don't see anything that would stop it from
running in the workbook_open event--and since it worked on other pcs, I'd bet there isn't! I was actually thinking that the developer could be playing games: if lcase(application.username) like "*cameron* then exit sub But since you're the developer, I'd guess that ain't the case! Cameron Young wrote: Hi Dave, I developed the xls, I was just adding effectively two lines of code to ask the user on Workbook_Open() if they wanted to print the xls and exit. somethingaboutyourpc = false - It's a new laptop with all new software - standard installs. Using the Auto_Open() method, I'd resolved the issue with: Public Sub Auto_Open() 'Prompt user on opening XLS to Print Worksheet and quit. Dim Resp As Variant Resp = MsgBox("Do you wish to Print and Exit ?", _ vbYesNo + vbQuestion + vbSystemModal, _ "Print & Exit ?") If Resp = vbYes Then ActiveWorkbook.PrintOut ActiveWorkbook.Close SaveChanges:=False End If End Sub And it fires as expected. Not worth spending more time mulling over, but thank-you very much for your time and patience with this issue. Hope others have been able to benefit. -- Cheers, Cameron "Dave Peterson" wrote: I don't think so (about the xl2003 basic edition). And you checked to see if the event was firing--with that msgbox stuff? Maybe it's firing and doing nothing for you: if somethingaboutyourpc = false then exit sub Have you spoken to the developer to see what he or she thinks? Cameron Young wrote: Hi again Dave, I tried the code you provided using Workbook_Open() and the Application.OnTime statement. Sadly it still didn't work. This has absolutely got me miffed ! Could it at all be due to me using the Excel 2003 Basic Edition? -- Cheers, Cameron "Dave Peterson" wrote: xl97 added the workbook_open event, but auto_open is still supported. Did you add that msgbox to the workbook_open event (near the top) to see if the event was actually started? == I have seen posts like this that: Private Sub Workbook_Open() Application.OnTime Now, "Continue_Open" End Sub Then in a general module: sub Continue_Open() 'your real code here end sub Sometimes there would be timing issues that could be avoided this way. (I've never seen this in real life, though--but others have.) Cameron Young wrote: Hi Dave, Yes, the Auto_open() does work, but I was under the impression that this was an older (Excel) version process of the Workbook_Open(). -- Cheers, Cameron "Dave Peterson" wrote: If you create a test subroutine in a test workbook: Option Explicit sub testme01 msgbox "hi" exit sub Then close (and save) that test workbook. Then reopen it, can you run that sub yourself? If you rename TestMe01 to Auto_open, then close (and save) and reopen, does the Auto_open procedure run? (Testme and auto_open should be in a general module.) ====== Can you edit the workbook_open event code? If yes, can you just add: Msgbox "Hi" right near the top. Then you'll know if the event is firing (and something else breaks) or if it's the event that's not firing. (And double check that Tools|Macro|Security setting.) Cameron Young wrote: Have not yet tried. The workbook I'm having trouble with does work as expected on other users pc's. ........[minutes later]....... No, sadly, no other xls workbooks with Workbook_Open() events are working. This is a problem. -- Cheers, Cameron "Dave Peterson" wrote: Do other workbooks with macros run when you open them the same way? Cameron Young wrote: I open the file by either: a) Double-clicking the file from its folder. b) Using the Excel Recent FilecHistory list. c) Performing a "File | Open". -- Cheers, Cameron "Dave Peterson" wrote: How do you open that workbook? Do you open it using another macro in a different workbook? If yes, do you use a shortcut key assigned to that macro. If yes, does that shortcut key include the shiftkey. If yes (again!), then remove the shiftkey from the short cut key. === Do you have events enabled when you try to open the workbook? Cameron Young wrote: Gents, Thanks for your responces. Bob:: Workbook_Open() code is in the ThisWorkbook module. Dave:: Have already sert the macro security to low. And still nothing. -- Cheers, Cameron "Dave Peterson" wrote: Close that workbook (if it's open) tools|macro|security...|security level tab Change it to medium (to be prompted each time a workbook containing macros opens) or change it to low (to always allow macros to run) Cameron Young wrote: Hi Bob, Actually, all of it. For some reason the Workbook_Open() event just doesn't seem to fire. -- Cheers, Cameron "Bob Phillips" wrote: Which bit doesn't work? -- HTH Bob Phillips "Cameron Young" wrote in message ... Hi all, Have whipped the following code in Excel/VBA and placed in the 'ThisWorkbook' module. The problem that I have is that it works well with other users using Excel 2003 Standard edition. I'm using Beginners edition - is that why the code won't run? The vba code: [-- code start --] Private Sub Workbook_Open() 'Prompt user on opening XLS to Print Worksheet and quit. Dim Resp Resp = MsgBox("Do you wish to Print and Exit ?", _ vbYesNo + vbQuestion, _ "Print & Exit ?") If Resp = vbYes Then ActiveWorkbook.PrintOut Application.Quit End If End Sub [-- code end --] Please help - this is driving me nuts. -- Cheers, Cameron -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
