Glaswegian
02-03-2009, 08:31 AM
Hi all
I'm creating a workbook that will allow users to amend an existing workbook. Since I don't know how users will have named their files I'm using GetOpenFilename and letting them select the relevant file. This is the code
Private Sub CommandButton1_Click()
Dim myFile
myFile = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
If myFile = False Then
MsgBox "No files were selected - please try again.", vbCritical + vbOKOnly, "Warning"
Exit Sub
End If
Workbooks.Open myFile
Application.ScreenUpdating = False
With ActiveWorkbook.Sheets("Sheet3")
.Visible = True
.Range("C23").Value = "E"
.Range("C24").Value = "F"
.Range("C25").Value = "G"
.Range("C26").Value = "H"
.Range("C27").Value = "I"
.Range("C28").Value = "J"
.Range("C18:C28").Name = "LendCat"
.Visible = False
End With
ActiveWorkbook.Close True
Application.ScreenUpdating = True
End Sub
The problem occurs when the above code is closing the amended workbook. The second workbook has code that writes to a Log file and it crashes after the Unprotect line. This is the code in the second workbook - it will be the same no matter to what users have renamed the file.
With Sheets("Log")
.Unprotect Password:="*****"
.Range("F65536").End(xlUp).Offset(1, 0).Value = Date <---crashes on this line
.Range("G65536").End(xlUp).Offset(1, 0).Value = Time
.Range("H65536").End(xlUp).Offset(1, 0).Value = UserName
.Range("I65536").End(xlUp).Offset(1, 0).Value = NameOfComputer
.Protect Password:="*****"
End With
I can't see any particular reason for this as this second workbook will be opened and closed on a regular basis. Can anyone help?
Thanks
I'm creating a workbook that will allow users to amend an existing workbook. Since I don't know how users will have named their files I'm using GetOpenFilename and letting them select the relevant file. This is the code
Private Sub CommandButton1_Click()
Dim myFile
myFile = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
If myFile = False Then
MsgBox "No files were selected - please try again.", vbCritical + vbOKOnly, "Warning"
Exit Sub
End If
Workbooks.Open myFile
Application.ScreenUpdating = False
With ActiveWorkbook.Sheets("Sheet3")
.Visible = True
.Range("C23").Value = "E"
.Range("C24").Value = "F"
.Range("C25").Value = "G"
.Range("C26").Value = "H"
.Range("C27").Value = "I"
.Range("C28").Value = "J"
.Range("C18:C28").Name = "LendCat"
.Visible = False
End With
ActiveWorkbook.Close True
Application.ScreenUpdating = True
End Sub
The problem occurs when the above code is closing the amended workbook. The second workbook has code that writes to a Log file and it crashes after the Unprotect line. This is the code in the second workbook - it will be the same no matter to what users have renamed the file.
With Sheets("Log")
.Unprotect Password:="*****"
.Range("F65536").End(xlUp).Offset(1, 0).Value = Date <---crashes on this line
.Range("G65536").End(xlUp).Offset(1, 0).Value = Time
.Range("H65536").End(xlUp).Offset(1, 0).Value = UserName
.Range("I65536").End(xlUp).Offset(1, 0).Value = NameOfComputer
.Protect Password:="*****"
End With
I can't see any particular reason for this as this second workbook will be opened and closed on a regular basis. Can anyone help?
Thanks