|
Help with Workbook_Open macro please
The following macro works partially...
--------------------------------------- Private Sub Workbook_Open() On Error GoTo Workbook_Open_Error If Worksheets("Audit").Range("E2").Value = "New" Then Worksheets("Audit").Range("A2").Value = Application.UserName Worksheets("Audit").Range("B2").Value = Date Worksheets("Audit").Range("C2").Value = Time Randomize Worksheets("Audit").Range("D2").Value = Rnd() ....up to here, but the next two lines don't do anything. They are supposed to protect with a random password, and then hide the sheet.... Worksheets("Audit").Protect Password:="A" & Int(Rnd() * 10000000000#) Worksheets("Audit").Visible = False End If GoTo Workbook_Open_Complete Workbook_Open_Error: MsgBox "Error in Workbook_Open" Workbook_Open_Complete: End Sub --------------------------------------- Can someone give me some clues? I'm sure this worked yesterday! |
Help with Workbook_Open macro please
Geoff,
You need to coerce the random numbe to be a string: Worksheets("Audit").Protect Password:="A" & CStr(Int(Rnd() * 10000000000#)) HTH, Bernie MS Excel MVP "Geoff C" wrote in message ... The following macro works partially... --------------------------------------- Private Sub Workbook_Open() On Error GoTo Workbook_Open_Error If Worksheets("Audit").Range("E2").Value = "New" Then Worksheets("Audit").Range("A2").Value = Application.UserName Worksheets("Audit").Range("B2").Value = Date Worksheets("Audit").Range("C2").Value = Time Randomize Worksheets("Audit").Range("D2").Value = Rnd() ...up to here, but the next two lines don't do anything. They are supposed to protect with a random password, and then hide the sheet.... Worksheets("Audit").Protect Password:="A" & Int(Rnd() * 10000000000#) Worksheets("Audit").Visible = False End If GoTo Workbook_Open_Complete Workbook_Open_Error: MsgBox "Error in Workbook_Open" Workbook_Open_Complete: End Sub --------------------------------------- Can someone give me some clues? I'm sure this worked yesterday! |
Help with Workbook_Open macro please
Bernie, thank for this, but having tried it, it made no difference. Is there
anything else that might help? I've succesfully used the random string-protect technique many times before, but not in a Workbook_Open macro before. I don't really understand why it seems to be failing on these two lines. Thanks, Geoff. "Bernie Deitrick" wrote: Geoff, You need to coerce the random numbe to be a string: Worksheets("Audit").Protect Password:="A" & CStr(Int(Rnd() * 10000000000#)) HTH, Bernie MS Excel MVP "Geoff C" wrote in message ... The following macro works partially... --------------------------------------- Private Sub Workbook_Open() On Error GoTo Workbook_Open_Error If Worksheets("Audit").Range("E2").Value = "New" Then Worksheets("Audit").Range("A2").Value = Application.UserName Worksheets("Audit").Range("B2").Value = Date Worksheets("Audit").Range("C2").Value = Time Randomize Worksheets("Audit").Range("D2").Value = Rnd() ...up to here, but the next two lines don't do anything. They are supposed to protect with a random password, and then hide the sheet.... Worksheets("Audit").Protect Password:="A" & Int(Rnd() * 10000000000#) Worksheets("Audit").Visible = False End If GoTo Workbook_Open_Complete Workbook_Open_Error: MsgBox "Error in Workbook_Open" Workbook_Open_Complete: End Sub --------------------------------------- Can someone give me some clues? I'm sure this worked yesterday! |
Help with Workbook_Open macro please
I used the following successfully on the FIRST run of the macro.
Private Sub Workbook_Open() Dim x As String On Error GoTo Workbook_Open_Error If Worksheets("Audit").Range("E2").Value = "New" Then Worksheets("Audit").Range("A2").Value = Application.UserName Worksheets("Audit").Range("B2").Value = Date Worksheets("Audit").Range("C2").Value = Time Randomize Worksheets("Audit").Range("D2").Value = Rnd() '...up to here, but the next two lines don't do anything. They are supposed 'to protect with a random password, and then hide the sheet.... x = "A" & Int(Rnd() * 10000000000#) Worksheets("Audit").Protect Password:=x Worksheets("Audit").Visible = False End If GoTo Workbook_Open_Complete Workbook_Open_Error: MsgBox "Error in Workbook_Open" Workbook_Open_Complete: End Sub HOWEVER, when you think of the logic, all subsequent opening events are trying to make changes to the Audit sheet which is now PROTECTED and not likely to be unprotected, even by the developer, since the password is random. It seems to me this is the cause of a failu trying to change a protected sheet. "Geoff C" wrote: Bernie, thank for this, but having tried it, it made no difference. Is there anything else that might help? I've succesfully used the random string-protect technique many times before, but not in a Workbook_Open macro before. I don't really understand why it seems to be failing on these two lines. Thanks, Geoff. "Bernie Deitrick" wrote: Geoff, You need to coerce the random numbe to be a string: Worksheets("Audit").Protect Password:="A" & CStr(Int(Rnd() * 10000000000#)) HTH, Bernie MS Excel MVP "Geoff C" wrote in message ... The following macro works partially... --------------------------------------- Private Sub Workbook_Open() On Error GoTo Workbook_Open_Error If Worksheets("Audit").Range("E2").Value = "New" Then Worksheets("Audit").Range("A2").Value = Application.UserName Worksheets("Audit").Range("B2").Value = Date Worksheets("Audit").Range("C2").Value = Time Randomize Worksheets("Audit").Range("D2").Value = Rnd() ...up to here, but the next two lines don't do anything. They are supposed to protect with a random password, and then hide the sheet.... Worksheets("Audit").Protect Password:="A" & Int(Rnd() * 10000000000#) Worksheets("Audit").Visible = False End If GoTo Workbook_Open_Complete Workbook_Open_Error: MsgBox "Error in Workbook_Open" Workbook_Open_Complete: End Sub --------------------------------------- Can someone give me some clues? I'm sure this worked yesterday! |
Help with Workbook_Open macro please
Thanks for the ideas guys, but nothing's working - the macro only protects
the sheet after making changes - it's never caused a problem before. Also, while trying difference things, it worked once, but when I went back in to work out why, it stopped working again. I think there must be something damaged about the file. Trouble is, I really don't want to start again! "gocush" wrote: I used the following successfully on the FIRST run of the macro. Private Sub Workbook_Open() Dim x As String On Error GoTo Workbook_Open_Error If Worksheets("Audit").Range("E2").Value = "New" Then Worksheets("Audit").Range("A2").Value = Application.UserName Worksheets("Audit").Range("B2").Value = Date Worksheets("Audit").Range("C2").Value = Time Randomize Worksheets("Audit").Range("D2").Value = Rnd() '...up to here, but the next two lines don't do anything. They are supposed 'to protect with a random password, and then hide the sheet.... x = "A" & Int(Rnd() * 10000000000#) Worksheets("Audit").Protect Password:=x Worksheets("Audit").Visible = False End If GoTo Workbook_Open_Complete Workbook_Open_Error: MsgBox "Error in Workbook_Open" Workbook_Open_Complete: End Sub HOWEVER, when you think of the logic, all subsequent opening events are trying to make changes to the Audit sheet which is now PROTECTED and not likely to be unprotected, even by the developer, since the password is random. It seems to me this is the cause of a failu trying to change a protected sheet. "Geoff C" wrote: Bernie, thank for this, but having tried it, it made no difference. Is there anything else that might help? I've succesfully used the random string-protect technique many times before, but not in a Workbook_Open macro before. I don't really understand why it seems to be failing on these two lines. Thanks, Geoff. "Bernie Deitrick" wrote: Geoff, You need to coerce the random numbe to be a string: Worksheets("Audit").Protect Password:="A" & CStr(Int(Rnd() * 10000000000#)) HTH, Bernie MS Excel MVP "Geoff C" wrote in message ... The following macro works partially... --------------------------------------- Private Sub Workbook_Open() On Error GoTo Workbook_Open_Error If Worksheets("Audit").Range("E2").Value = "New" Then Worksheets("Audit").Range("A2").Value = Application.UserName Worksheets("Audit").Range("B2").Value = Date Worksheets("Audit").Range("C2").Value = Time Randomize Worksheets("Audit").Range("D2").Value = Rnd() ...up to here, but the next two lines don't do anything. They are supposed to protect with a random password, and then hide the sheet.... Worksheets("Audit").Protect Password:="A" & Int(Rnd() * 10000000000#) Worksheets("Audit").Visible = False End If GoTo Workbook_Open_Complete Workbook_Open_Error: MsgBox "Error in Workbook_Open" Workbook_Open_Complete: End Sub --------------------------------------- Can someone give me some clues? I'm sure this worked yesterday! |
Help with Workbook_Open macro please
Geoff,
Try using Rob Bovey's CodeCleaner. It can be a lifesaver. http://www.appspro.com/downloads/CodeCleaner.exe HTH, Bernie MS Excel MVP "Geoff C" wrote in message ... Thanks for the ideas guys, but nothing's working - the macro only protects the sheet after making changes - it's never caused a problem before. Also, while trying difference things, it worked once, but when I went back in to work out why, it stopped working again. I think there must be something damaged about the file. Trouble is, I really don't want to start again! "gocush" wrote: I used the following successfully on the FIRST run of the macro. Private Sub Workbook_Open() Dim x As String On Error GoTo Workbook_Open_Error If Worksheets("Audit").Range("E2").Value = "New" Then Worksheets("Audit").Range("A2").Value = Application.UserName Worksheets("Audit").Range("B2").Value = Date Worksheets("Audit").Range("C2").Value = Time Randomize Worksheets("Audit").Range("D2").Value = Rnd() '...up to here, but the next two lines don't do anything. They are supposed 'to protect with a random password, and then hide the sheet.... x = "A" & Int(Rnd() * 10000000000#) Worksheets("Audit").Protect Password:=x Worksheets("Audit").Visible = False End If GoTo Workbook_Open_Complete Workbook_Open_Error: MsgBox "Error in Workbook_Open" Workbook_Open_Complete: End Sub HOWEVER, when you think of the logic, all subsequent opening events are trying to make changes to the Audit sheet which is now PROTECTED and not likely to be unprotected, even by the developer, since the password is random. It seems to me this is the cause of a failu trying to change a protected sheet. "Geoff C" wrote: Bernie, thank for this, but having tried it, it made no difference. Is there anything else that might help? I've succesfully used the random string-protect technique many times before, but not in a Workbook_Open macro before. I don't really understand why it seems to be failing on these two lines. Thanks, Geoff. "Bernie Deitrick" wrote: Geoff, You need to coerce the random numbe to be a string: Worksheets("Audit").Protect Password:="A" & CStr(Int(Rnd() * 10000000000#)) HTH, Bernie MS Excel MVP "Geoff C" wrote in message ... The following macro works partially... --------------------------------------- Private Sub Workbook_Open() On Error GoTo Workbook_Open_Error If Worksheets("Audit").Range("E2").Value = "New" Then Worksheets("Audit").Range("A2").Value = Application.UserName Worksheets("Audit").Range("B2").Value = Date Worksheets("Audit").Range("C2").Value = Time Randomize Worksheets("Audit").Range("D2").Value = Rnd() ...up to here, but the next two lines don't do anything. They are supposed to protect with a random password, and then hide the sheet.... Worksheets("Audit").Protect Password:="A" & Int(Rnd() * 10000000000#) Worksheets("Audit").Visible = False End If GoTo Workbook_Open_Complete Workbook_Open_Error: MsgBox "Error in Workbook_Open" Workbook_Open_Complete: End Sub --------------------------------------- Can someone give me some clues? I'm sure this worked yesterday! |
Help with Workbook_Open macro please
On your "Audit" sheet are the cells in A2:D2 LOCKED?
Or are you locking them with some other macro, say in your Close_event? If so, the next time you try to Open this file it will crash since your Workbook_Open event tries to change these cells. On the other hand if these cells are NOT locked what is the point? "Geoff C" wrote: Thanks for the ideas guys, but nothing's working - the macro only protects the sheet after making changes - it's never caused a problem before. Also, while trying difference things, it worked once, but when I went back in to work out why, it stopped working again. I think there must be something damaged about the file. Trouble is, I really don't want to start again! "gocush" wrote: I used the following successfully on the FIRST run of the macro. Private Sub Workbook_Open() Dim x As String On Error GoTo Workbook_Open_Error If Worksheets("Audit").Range("E2").Value = "New" Then Worksheets("Audit").Range("A2").Value = Application.UserName Worksheets("Audit").Range("B2").Value = Date Worksheets("Audit").Range("C2").Value = Time Randomize Worksheets("Audit").Range("D2").Value = Rnd() '...up to here, but the next two lines don't do anything. They are supposed 'to protect with a random password, and then hide the sheet.... x = "A" & Int(Rnd() * 10000000000#) Worksheets("Audit").Protect Password:=x Worksheets("Audit").Visible = False End If GoTo Workbook_Open_Complete Workbook_Open_Error: MsgBox "Error in Workbook_Open" Workbook_Open_Complete: End Sub HOWEVER, when you think of the logic, all subsequent opening events are trying to make changes to the Audit sheet which is now PROTECTED and not likely to be unprotected, even by the developer, since the password is random. It seems to me this is the cause of a failu trying to change a protected sheet. "Geoff C" wrote: Bernie, thank for this, but having tried it, it made no difference. Is there anything else that might help? I've succesfully used the random string-protect technique many times before, but not in a Workbook_Open macro before. I don't really understand why it seems to be failing on these two lines. Thanks, Geoff. "Bernie Deitrick" wrote: Geoff, You need to coerce the random numbe to be a string: Worksheets("Audit").Protect Password:="A" & CStr(Int(Rnd() * 10000000000#)) HTH, Bernie MS Excel MVP "Geoff C" wrote in message ... The following macro works partially... --------------------------------------- Private Sub Workbook_Open() On Error GoTo Workbook_Open_Error If Worksheets("Audit").Range("E2").Value = "New" Then Worksheets("Audit").Range("A2").Value = Application.UserName Worksheets("Audit").Range("B2").Value = Date Worksheets("Audit").Range("C2").Value = Time Randomize Worksheets("Audit").Range("D2").Value = Rnd() ...up to here, but the next two lines don't do anything. They are supposed to protect with a random password, and then hide the sheet.... Worksheets("Audit").Protect Password:="A" & Int(Rnd() * 10000000000#) Worksheets("Audit").Visible = False End If GoTo Workbook_Open_Complete Workbook_Open_Error: MsgBox "Error in Workbook_Open" Workbook_Open_Complete: End Sub --------------------------------------- Can someone give me some clues? I'm sure this worked yesterday! |
Help with Workbook_Open macro please
Thankyou! I tracked the problem down to some sort of interference with a UDF.
I have no idea what CodeCleaner does, but after 3 tries it turned out a version that works. Saved and cemented to the ground. "Bernie Deitrick" wrote: Geoff, Try using Rob Bovey's CodeCleaner. It can be a lifesaver. http://www.appspro.com/downloads/CodeCleaner.exe HTH, Bernie MS Excel MVP "Geoff C" wrote in message ... Thanks for the ideas guys, but nothing's working - the macro only protects the sheet after making changes - it's never caused a problem before. Also, while trying difference things, it worked once, but when I went back in to work out why, it stopped working again. I think there must be something damaged about the file. Trouble is, I really don't want to start again! "gocush" wrote: I used the following successfully on the FIRST run of the macro. Private Sub Workbook_Open() Dim x As String On Error GoTo Workbook_Open_Error If Worksheets("Audit").Range("E2").Value = "New" Then Worksheets("Audit").Range("A2").Value = Application.UserName Worksheets("Audit").Range("B2").Value = Date Worksheets("Audit").Range("C2").Value = Time Randomize Worksheets("Audit").Range("D2").Value = Rnd() '...up to here, but the next two lines don't do anything. They are supposed 'to protect with a random password, and then hide the sheet.... x = "A" & Int(Rnd() * 10000000000#) Worksheets("Audit").Protect Password:=x Worksheets("Audit").Visible = False End If GoTo Workbook_Open_Complete Workbook_Open_Error: MsgBox "Error in Workbook_Open" Workbook_Open_Complete: End Sub HOWEVER, when you think of the logic, all subsequent opening events are trying to make changes to the Audit sheet which is now PROTECTED and not likely to be unprotected, even by the developer, since the password is random. It seems to me this is the cause of a failu trying to change a protected sheet. "Geoff C" wrote: Bernie, thank for this, but having tried it, it made no difference. Is there anything else that might help? I've succesfully used the random string-protect technique many times before, but not in a Workbook_Open macro before. I don't really understand why it seems to be failing on these two lines. Thanks, Geoff. "Bernie Deitrick" wrote: Geoff, You need to coerce the random numbe to be a string: Worksheets("Audit").Protect Password:="A" & CStr(Int(Rnd() * 10000000000#)) HTH, Bernie MS Excel MVP "Geoff C" wrote in message ... The following macro works partially... --------------------------------------- Private Sub Workbook_Open() On Error GoTo Workbook_Open_Error If Worksheets("Audit").Range("E2").Value = "New" Then Worksheets("Audit").Range("A2").Value = Application.UserName Worksheets("Audit").Range("B2").Value = Date Worksheets("Audit").Range("C2").Value = Time Randomize Worksheets("Audit").Range("D2").Value = Rnd() ...up to here, but the next two lines don't do anything. They are supposed to protect with a random password, and then hide the sheet.... Worksheets("Audit").Protect Password:="A" & Int(Rnd() * 10000000000#) Worksheets("Audit").Visible = False End If GoTo Workbook_Open_Complete Workbook_Open_Error: MsgBox "Error in Workbook_Open" Workbook_Open_Complete: End Sub --------------------------------------- Can someone give me some clues? I'm sure this worked yesterday! |
| All times are GMT +1. The time now is 03:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com