Posts by Insomniac

You might also try adding:

Activecell.activate

as the 1st line of code to ensure correct focus.

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.

ThisWorkbook.Save

'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?

Hi Dumbo,

2 subs for deleting hyperlinks:

Sub Delete_Column_Hypers()
Columns("A:Z").Hyperlinks.Delete
End Sub

Sub Delete_Sheet_Hypers()
ActiveSheet.Hyperlinks.Delete
End Sub

Oh, the link seems to have fixed itself up LOL , after I posted!

Have a look at the example from this site

http://www.ozgrid.com/FreeDownloads/WhichControl.zip

Sorry couldnt get link to work but its on this site, should help with your query.

Adding this should stop the "Do you want to save changes" message.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

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

ActiveCell.Activate
If UnLockSheet Then
ActiveSheet.Unprotect Password:=SheetPassWord
Else
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
Next
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
Next
End If
ActiveSheet.Calculate

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
Next

' run some code based on changes

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

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

Insomniac.

Hey Great idea,

I have tried and failed, would love to see a workable solution.

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 ...name... 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.

Insomniac

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.

Insomniac

Although now that you mention it applied service patch from Magazine Cover Cd only to the Me computer???

will look into.

Yes exactly the same?????