Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open macro - what gives?
Hi there, I was here asking questions yesterday about my workbook_open macro
(correctly placed in ThisWorkbook). The code is below, and I'm stumped. Some parts work and some don't - and previously the parts that don't work did, while the parts that do didn't. Is there something about this special macro that I'm just not getting? The book contains two worksheets, "Audit" and "Profiles". The code is supposed to activate the Audit worksheet, record the user, the time and date that a new workbook is created from a template, enter a random number that determines whether the workbook is QC'd later on, protect the Audit worksheet with a random password, hide it, and then return to the Profiles worksheet. If the sheet is not new (i.e. contains the random number), it should bypass all that. I changed it a bit yesterday following advice from the group, and now the lines I've folowed with a * don't work anymore, but in older versions have functioned perfectly well. I'm working in Excel 2000. ------------- Private Sub Workbook_Open() Sheets("Audit").Select *used to work (as far as I know) ' If Sheets("Audit").Range("E2").Value = "New" Then *Commented out yesterday - but putting it back doesn't help! If Len(Sheets("Audit").Range("D2").Value) = 0 Then Sheets("Audit").Range("A2").Value = Application.UserName Sheets("Audit").Range("B2").Value = Date Sheets("Audit").Range("C2").Value = Time Randomize Sheets("Audit").Range("D2").Value = Rnd() * 'ActiveSheet.Protect Password:="A" & Int(Rnd() * 10000000000#) 'ActiveSheet.Visible = False Sheets("Audit").Protect Password:="A" & Int(Rnd() * 10000000000#) Sheets("Audit").Visible = False *neither of these code variants work, the code previous contained the first two lines, and worked fine End If Sheets("Profiles").Select End Sub ------------------------ Feeling dim, and grateful for some advice, Geoff. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open macro - what gives?
Is the sheet hidden when the workbook opens. You can not select a sheet that
is not visible. Add this to the beginning sheets("Audit").visible = xlvisible HTH "Geoff C" wrote: Hi there, I was here asking questions yesterday about my workbook_open macro (correctly placed in ThisWorkbook). The code is below, and I'm stumped. Some parts work and some don't - and previously the parts that don't work did, while the parts that do didn't. Is there something about this special macro that I'm just not getting? The book contains two worksheets, "Audit" and "Profiles". The code is supposed to activate the Audit worksheet, record the user, the time and date that a new workbook is created from a template, enter a random number that determines whether the workbook is QC'd later on, protect the Audit worksheet with a random password, hide it, and then return to the Profiles worksheet. If the sheet is not new (i.e. contains the random number), it should bypass all that. I changed it a bit yesterday following advice from the group, and now the lines I've folowed with a * don't work anymore, but in older versions have functioned perfectly well. I'm working in Excel 2000. ------------- Private Sub Workbook_Open() Sheets("Audit").Select *used to work (as far as I know) ' If Sheets("Audit").Range("E2").Value = "New" Then *Commented out yesterday - but putting it back doesn't help! If Len(Sheets("Audit").Range("D2").Value) = 0 Then Sheets("Audit").Range("A2").Value = Application.UserName Sheets("Audit").Range("B2").Value = Date Sheets("Audit").Range("C2").Value = Time Randomize Sheets("Audit").Range("D2").Value = Rnd() * 'ActiveSheet.Protect Password:="A" & Int(Rnd() * 10000000000#) 'ActiveSheet.Visible = False Sheets("Audit").Protect Password:="A" & Int(Rnd() * 10000000000#) Sheets("Audit").Visible = False *neither of these code variants work, the code previous contained the first two lines, and worked fine End If Sheets("Profiles").Select End Sub ------------------------ Feeling dim, and grateful for some advice, Geoff. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open macro - what gives?
Thanks for the suggestion, but the sheet is visible. I tried it anyway -
after I put the line in, none of the code worked. Took it out, and the value settings worked again, but not the sheet selection, protection or hiding. "Jim Thomlinson" wrote: Is the sheet hidden when the workbook opens. You can not select a sheet that is not visible. Add this to the beginning sheets("Audit").visible = xlvisible HTH "Geoff C" wrote: Hi there, I was here asking questions yesterday about my workbook_open macro (correctly placed in ThisWorkbook). The code is below, and I'm stumped. Some parts work and some don't - and previously the parts that don't work did, while the parts that do didn't. Is there something about this special macro that I'm just not getting? The book contains two worksheets, "Audit" and "Profiles". The code is supposed to activate the Audit worksheet, record the user, the time and date that a new workbook is created from a template, enter a random number that determines whether the workbook is QC'd later on, protect the Audit worksheet with a random password, hide it, and then return to the Profiles worksheet. If the sheet is not new (i.e. contains the random number), it should bypass all that. I changed it a bit yesterday following advice from the group, and now the lines I've folowed with a * don't work anymore, but in older versions have functioned perfectly well. I'm working in Excel 2000. ------------- Private Sub Workbook_Open() Sheets("Audit").Select *used to work (as far as I know) ' If Sheets("Audit").Range("E2").Value = "New" Then *Commented out yesterday - but putting it back doesn't help! If Len(Sheets("Audit").Range("D2").Value) = 0 Then Sheets("Audit").Range("A2").Value = Application.UserName Sheets("Audit").Range("B2").Value = Date Sheets("Audit").Range("C2").Value = Time Randomize Sheets("Audit").Range("D2").Value = Rnd() * 'ActiveSheet.Protect Password:="A" & Int(Rnd() * 10000000000#) 'ActiveSheet.Visible = False Sheets("Audit").Protect Password:="A" & Int(Rnd() * 10000000000#) Sheets("Audit").Visible = False *neither of these code variants work, the code previous contained the first two lines, and worked fine End If Sheets("Profiles").Select End Sub ------------------------ Feeling dim, and grateful for some advice, Geoff. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open macro - what gives?
From looking at your code I notice that you are protecting your sheet using a
random number. How are you unprotecting it? I also don't see where you unprotect it. This is going to cause you a problem (as a guess). "Geoff C" wrote: Thanks for the suggestion, but the sheet is visible. I tried it anyway - after I put the line in, none of the code worked. Took it out, and the value settings worked again, but not the sheet selection, protection or hiding. "Jim Thomlinson" wrote: Is the sheet hidden when the workbook opens. You can not select a sheet that is not visible. Add this to the beginning sheets("Audit").visible = xlvisible HTH "Geoff C" wrote: Hi there, I was here asking questions yesterday about my workbook_open macro (correctly placed in ThisWorkbook). The code is below, and I'm stumped. Some parts work and some don't - and previously the parts that don't work did, while the parts that do didn't. Is there something about this special macro that I'm just not getting? The book contains two worksheets, "Audit" and "Profiles". The code is supposed to activate the Audit worksheet, record the user, the time and date that a new workbook is created from a template, enter a random number that determines whether the workbook is QC'd later on, protect the Audit worksheet with a random password, hide it, and then return to the Profiles worksheet. If the sheet is not new (i.e. contains the random number), it should bypass all that. I changed it a bit yesterday following advice from the group, and now the lines I've folowed with a * don't work anymore, but in older versions have functioned perfectly well. I'm working in Excel 2000. ------------- Private Sub Workbook_Open() Sheets("Audit").Select *used to work (as far as I know) ' If Sheets("Audit").Range("E2").Value = "New" Then *Commented out yesterday - but putting it back doesn't help! If Len(Sheets("Audit").Range("D2").Value) = 0 Then Sheets("Audit").Range("A2").Value = Application.UserName Sheets("Audit").Range("B2").Value = Date Sheets("Audit").Range("C2").Value = Time Randomize Sheets("Audit").Range("D2").Value = Rnd() * 'ActiveSheet.Protect Password:="A" & Int(Rnd() * 10000000000#) 'ActiveSheet.Visible = False Sheets("Audit").Protect Password:="A" & Int(Rnd() * 10000000000#) Sheets("Audit").Visible = False *neither of these code variants work, the code previous contained the first two lines, and worked fine End If Sheets("Profiles").Select End Sub ------------------------ Feeling dim, and grateful for some advice, Geoff. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open macro - what gives?
Try this code. It never selects the Audit sheet. It just manipulates it as
necessary... Private Sub Workbook_Open() Dim wksAudit As Worksheet Set wksAudit = Sheets("Audit") ' If Sheets("Audit").Range("E2").Value = "New" Then If Len(wksAudit.Range("D2").Value) = 0 Then With wksAudit .Range("A2").Value = Application.UserName .Range("B2").Value = Date .Range("C2").Value = Time Randomize .Range("D2").Value = Rnd() End With wksAudit.Protect Password:="A" & Int(Rnd() * 10000000000#) wksAudit.Visible = xlHidden End If Sheets("Profiles").Select End Sub I created an audit sheet object, which is not strictly necessary but it does make the coding a little faster and easier. HTH "Geoff C" wrote: Thanks for the suggestion, but the sheet is visible. I tried it anyway - after I put the line in, none of the code worked. Took it out, and the value settings worked again, but not the sheet selection, protection or hiding. "Jim Thomlinson" wrote: Is the sheet hidden when the workbook opens. You can not select a sheet that is not visible. Add this to the beginning sheets("Audit").visible = xlvisible HTH "Geoff C" wrote: Hi there, I was here asking questions yesterday about my workbook_open macro (correctly placed in ThisWorkbook). The code is below, and I'm stumped. Some parts work and some don't - and previously the parts that don't work did, while the parts that do didn't. Is there something about this special macro that I'm just not getting? The book contains two worksheets, "Audit" and "Profiles". The code is supposed to activate the Audit worksheet, record the user, the time and date that a new workbook is created from a template, enter a random number that determines whether the workbook is QC'd later on, protect the Audit worksheet with a random password, hide it, and then return to the Profiles worksheet. If the sheet is not new (i.e. contains the random number), it should bypass all that. I changed it a bit yesterday following advice from the group, and now the lines I've folowed with a * don't work anymore, but in older versions have functioned perfectly well. I'm working in Excel 2000. ------------- Private Sub Workbook_Open() Sheets("Audit").Select *used to work (as far as I know) ' If Sheets("Audit").Range("E2").Value = "New" Then *Commented out yesterday - but putting it back doesn't help! If Len(Sheets("Audit").Range("D2").Value) = 0 Then Sheets("Audit").Range("A2").Value = Application.UserName Sheets("Audit").Range("B2").Value = Date Sheets("Audit").Range("C2").Value = Time Randomize Sheets("Audit").Range("D2").Value = Rnd() * 'ActiveSheet.Protect Password:="A" & Int(Rnd() * 10000000000#) 'ActiveSheet.Visible = False Sheets("Audit").Protect Password:="A" & Int(Rnd() * 10000000000#) Sheets("Audit").Visible = False *neither of these code variants work, the code previous contained the first two lines, and worked fine End If Sheets("Profiles").Select End Sub ------------------------ Feeling dim, and grateful for some advice, Geoff. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open macro - what gives?
Thanks for trying Jim, but it was no different. I even tried moving the macro
into a module and calling it from ThisWorkbook, with exactly the same results. But the same macro then works perfectly when called directly after opening the workbook. Clearly it's not the code itself that is failing. I'm going to have to chalk it up to "Corrupt file", and try to redraft from scratch. It drives me spare that something so useful can be so flaky, but there you go... Thanks for the tip on layout, much neater. "Jim Thomlinson" wrote: Try this code. It never selects the Audit sheet. It just manipulates it as necessary... Private Sub Workbook_Open() Dim wksAudit As Worksheet Set wksAudit = Sheets("Audit") ' If Sheets("Audit").Range("E2").Value = "New" Then If Len(wksAudit.Range("D2").Value) = 0 Then With wksAudit .Range("A2").Value = Application.UserName .Range("B2").Value = Date .Range("C2").Value = Time Randomize .Range("D2").Value = Rnd() End With wksAudit.Protect Password:="A" & Int(Rnd() * 10000000000#) wksAudit.Visible = xlHidden End If Sheets("Profiles").Select End Sub I created an audit sheet object, which is not strictly necessary but it does make the coding a little faster and easier. HTH "Geoff C" wrote: Thanks for the suggestion, but the sheet is visible. I tried it anyway - after I put the line in, none of the code worked. Took it out, and the value settings worked again, but not the sheet selection, protection or hiding. "Jim Thomlinson" wrote: Is the sheet hidden when the workbook opens. You can not select a sheet that is not visible. Add this to the beginning sheets("Audit").visible = xlvisible HTH "Geoff C" wrote: Hi there, I was here asking questions yesterday about my workbook_open macro (correctly placed in ThisWorkbook). The code is below, and I'm stumped. Some parts work and some don't - and previously the parts that don't work did, while the parts that do didn't. Is there something about this special macro that I'm just not getting? The book contains two worksheets, "Audit" and "Profiles". The code is supposed to activate the Audit worksheet, record the user, the time and date that a new workbook is created from a template, enter a random number that determines whether the workbook is QC'd later on, protect the Audit worksheet with a random password, hide it, and then return to the Profiles worksheet. If the sheet is not new (i.e. contains the random number), it should bypass all that. I changed it a bit yesterday following advice from the group, and now the lines I've folowed with a * don't work anymore, but in older versions have functioned perfectly well. I'm working in Excel 2000. ------------- Private Sub Workbook_Open() Sheets("Audit").Select *used to work (as far as I know) ' If Sheets("Audit").Range("E2").Value = "New" Then *Commented out yesterday - but putting it back doesn't help! If Len(Sheets("Audit").Range("D2").Value) = 0 Then Sheets("Audit").Range("A2").Value = Application.UserName Sheets("Audit").Range("B2").Value = Date Sheets("Audit").Range("C2").Value = Time Randomize Sheets("Audit").Range("D2").Value = Rnd() * 'ActiveSheet.Protect Password:="A" & Int(Rnd() * 10000000000#) 'ActiveSheet.Visible = False Sheets("Audit").Protect Password:="A" & Int(Rnd() * 10000000000#) Sheets("Audit").Visible = False *neither of these code variants work, the code previous contained the first two lines, and worked fine End If Sheets("Profiles").Select End Sub ------------------------ Feeling dim, and grateful for some advice, Geoff. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook_Open macro not running | Excel Discussion (Misc queries) | |||
Help with Workbook_Open macro please | Excel Programming | |||
Disable Workbook_Open() Macro | Excel Programming | |||
Prevent Workbook_Open macro | Excel Programming | |||
Workbook_Open & Macro | Excel Programming |