workbookbeforesave

[jsandstr]

Hi,

I writing a workbookbeforesave event but can't get my
PageSetup.LeftFooter to work. The event is executed it is just the
Pagesetup that doesn't work. Is there any known problem with this? I
running excel 2003 sp2.

ws.PageSetup.CenterHeader = "test" '''doesn't work
ws.Range("A4") = "test" ''' works

Code in a Class Module:

Public WithEvents App As Application
Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal
SaveAsUI As Boolean, Cancel As Boolean)

Dim SecurityLevel()

SecurityLevel = Array("Secret", "Confidential", "Proprietary",
"Public")

On Error Resume Next
Do Until Level = 1 Or Level = 2 Or Level = 3 Or Level = 4
Level = InputBox("To AFR secure this document type in security
level, otherwise cancel.1 = Secret, 2 = Confidential, 3 = Proprietary
and 4 = Public.", "Update the date")
If Level = 0 Then
Exit Sub ''' Cancel pressed
End If
Loop

Wb.Worksheets("Sheet1").PageSetup.LeftFooter =
Application.UserName & ", " & "&D" & Chr(10) & "Security Class <" &
SecurityLevel(Level) & ">"

However this works

ws.Worksheets("Sheet1").Range("A2") = "test"

End Sub

Regards

JS

 

[John Bundy]

That one took me a minute, first i don't see where you are setting wb = to
the workbook, but more likely the problem is that arrays are option base 0,
so putting a 4 will cause a subscript error and the others won't match.

 

[jsandstr]

That one took me a minute, first i don't see where you are setting wb =to
the workbook, but more likely the problem is that arrays are option base 0,
so putting a 4 will cause a subscript error and the others won't match.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.
















- Visa citerad text -

I have it in my code just missed to copy it here.

It is just something with PageSetup.whatever. I can change it to
Wb.Worksheets("Sheet1").PageSetup.LeftFooter = "test"

and it still won't work.

I'm going crazy for this small but really annoying problem.

\JS

 

[John Bundy]

Works for me, here is the code i used, set option base to 1 and wb to
thisworkbook. those are the only changes

Option Base 1
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim SecurityLevel()

SecurityLevel = Array("Secret", "Confidential", "Proprietary", "Public")

On Error Resume Next
Do Until Level = 1 Or Level = 2 Or Level = 3 Or Level = 4
Level = InputBox("To AFR secure this document type in security level,
otherwise cancel.1 = Secret, 2 = Confidential, 3 = Proprietary and 4 =
Public.", "Update the date")
If Level = 0 Then
Exit Sub ''' Cancel pressed
End If
Loop

ThisWorkbook.Worksheets("Sheet1").PageSetup.LeftFooter =
Application.UserName & ", " & "&D" & Chr(10) & "Security Class <" &
SecurityLevel(Level) & ">"

End Sub

 

[jsandstr]

Works for me, here is the code i used, set option base to 1 and wb to
thisworkbook. those are the only changes

Option Base 1
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim SecurityLevel()

SecurityLevel = Array("Secret", "Confidential", "Proprietary", "Public")

On Error Resume Next
Do Until Level = 1 Or Level = 2 Or Level = 3 Or Level = 4
Level = InputBox("To AFR secure this document type in security level,
otherwise cancel.1 = Secret, 2 = Confidential, 3 = Proprietary and 4 =
Public.", "Update the date")
If Level = 0 Then
Exit Sub ''' Cancel pressed
End If
Loop

ThisWorkbook.Worksheets("Sheet1").PageSetup.LeftFooter =
Application.UserName & ", " & "&D" & Chr(10) & "Security Class <" &
SecurityLevel(Level) & ">"

End Sub

--
-John
Please rate when your question is answered to help us and others know what
is helpful.










- Visa citerad text -

Thanks for trying but this is something else. This code works for
other I have heard it before but for some reason it doesn't work on my
pc. It is something with beforesave together with PageSetup. I can
make it work for beforeclose but I want it to work in a
workbookbeforesave event.

Cheers

JS