Posts by Insomniac

    Hi all,

    I have been using the following code as an after save event.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    'run code to hide sheets, columns etc.


    'run code to redisplay sheets etc.

    Application.ScreenUpdating = True
    Application.EnableEvents = True

    Cancel = True 'cancel original save request

    End Sub

    Does anyone know of another or better way to achieve this?

    Can anyone suggest any scenerio where this will fail or get an error?

    I use the office assistant a lot to display information and options.

    eg: The office assistant will display the input message of cells with data validation even if there is no validation criteria.

    I stuff this into sheet selection event to keep turning the assistant back on.

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Application.Assistant.Visible = True
    End Sub

    Hi Chester,

    Just another interpretation:

    If you know the 'Office UserName' of your spreadsheet users you can add some code to the Workbook_SheetActivate event to automate sheet protection.

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim User As String
    Dim UnLockSheet As Boolean
    Const SheetPassWord As String = "Password"

    User = Application.UserName

    Select Case ActiveSheet.Name
    Case "User1's Sheet"
    If User = "User1" Then UnLockSheet = True
    Case "User2's Sheet"
    If User = "User2" Then UnLockSheet = True
    Case "User3's Sheet"
    If User = "User3" Then UnLockSheet = True
    End Select

    If UnLockSheet Then
    ActiveSheet.Unprotect Password:=SheetPassWord
    ActiveSheet.Protect Password:=SheetPassWord, DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If

    End Sub

    This is very low level protection but works fine for the general user. Just alter the sheet names to match the user and the sheet will be unprotected. Change password to whatever you want.

    I think this is what you want

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim FilePath As String, PrintDate As String
    FilePath = ThisWorkbook.Path & "\" & ThisWorkbook.Name
    PrintDate = FileDateTime(FilePath)
    ActiveSheet.PageSetup.CenterFooter = "File Last modified " & PrintDate
    End Sub

    Will put the file last modified date in centre footer of whatever sheet you print.

    (Im hope VBA is OK)

    I can offer a work around I have used to a similar problem that may or may not be suitable for your application.

    Works by setting calculation to manual and picking up values to check in sheetchange event then calculating the sheet and checking for changes in sheet calculate event.

    All this code is placed in the sheet concerned.

    Option Explicit
    Option Base 1
    Dim ColCheck(20)
    Dim Count As Long

    Private Sub Worksheet_Activate()
    Application.Calculation = xlCalculationManual
    For Count = 1 To 20 'Load Array with current values
    ColCheck(Count) = Cells(Count, 31) 'Column AE values
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address = "$D$1" Then 'if D1 is changed
    For Count = 1 To 20 'Load Array with old values
    ColCheck(Count) = Cells(Count, 31) 'Column AE values
    End If

    End Sub

    Private Sub Worksheet_Calculate()
    For Count = 1 To 20
    If ColCheck(Count) <> Cells(Count, 31) Then
    MsgBox "Column AE Row " & Count & "has changed" & _
    "Previous value " & ColCheck(Count) & _
    " New value " & Cells(Count, 31).Value
    End If

    ' run some code based on changes

    For Count = 1 To 20 'Load Array with new values
    ColCheck(Count) = Cells(Count, 31) 'Column AE values

    End Sub

    Private Sub Worksheet_Deactivate()
    Application.Calculation = xlCalculationAutomatic
    End Sub

    This code will compare rows 1-20 of column AE and tell you of changes if D1 is altered.

    Hope it is of some use, Insomniac

    Me to thanks for tip,

    have been trying to get this to work on and off for some time, had given up!

    2x17" monitors - fantastic.

    Coincidentally there is an article in April 2003 edition of Australian PC AUTHORITY magazine on page 125, install new graphics card and duel monitor system


    Thanks to all replies,

    Richie, no need to appologise I realise my original post was a bit ambiguous.

    Im sure there must be a way to access the username of person who has edit rights to a file?#?#?####?

    At the very least I have realised that if you open the file by macro with 'alerts disabled' the user does not get the 'open read only option' but will still get a notification that the file is available for editing when the read/write user closes the file, better than nothing.

    Thanks again.

    Hi Richie,

    Thanks for the reply, but perhaps to clarify my question:

    Working on a network I need to test if another user has a workbook open on their computer thus making the file readonly to anyone else who wants access to it.

    I do not want to 'share the workbook' and would be nice to obtain the office username as normally displayed by Excel under these conditions without the option of opening readonly.

    BTW the files in question are communal data entry tabels that autoclose after 5 mins, hence my message 'Try again in a few minutes'

    Hi all

    I want to check if an Excel file is already opened by someone else and if true display a message like 'File in use by please try again in afew minutes.'

    At the moment I have buttons that open workbooks by macros which turn off screen updating and disable alerts, load the file, check for readonly and close the file if readonly is true with a message that file is in use.

    I want to avoid the Excel user option of 'Open as readonly' but display who is using the file.

    Any suggestions appreciated.

    Hi Anonymous, your name sounds familiar?

    I dont know how big your pics are, but they can increase the size of the Excel file quite dramatically.

    This code will clear Image1 on Sheet1 before saving, and Load it again when workbook is opened.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Sheets("Sheet1").Image1.Picture = Nothing
    End Sub

    Private Sub Workbook_Open()
    Sheets("Sheet1").Image1.Picture = LoadPicture("C:\My Documents\MyPic.jpg")
    End Sub

    You can adapt this to suit your needs.


    Just a quick thanyou to XL-Dennis,

    reinstalled from scratch and all is fine.

    'Sometimes when your looking for answers its hard to just get back to basics or think outside the box'

    Thanx Mate

    (Aussie coloquialism do not be affended)

    In line with all the above advice I only access the internet on my secondary computer which does not contain any important files and which I reinstall at least every 2 months.

    As far as downloads and macro virus I open the download with macros disabled.
    I then view the code, if it is locked or I cannot determine the perpose of the code I delete the file.