Shared workbook problem!

[Kentman]

I have a workbook that works fine except when I share it. Clicking the OK button on a userform causes the following error:

'1004'
'Application-defined or object-defined error'

The worksheets are protected but this only happens once I share it not at any other time.

Microsoft says: "This issue may occur if one or more of the cells in an array (range of cells) contain a character string that is set to contain more than 911 characters."
But I don't have any data in any of the sheets as I'm testing a blank version!

Does anyone know what it means or is referring to or how to stop this?

Any help would be greatly appreciated as I'm on the last leg of a long development.

 

[Kentman]

For example, .......the conditional formats won't be pasted. That's from testing in Excel 2000.

I wondered why they hadn't been applied.

Life can be so, so hard!

Cheers Andrew.

 

[Domski]

Not that it will fix your problem but one suggestion would be to avoid using activecell and select. Selecting cells in particular is almost never necessary when you are writing VBA.

For example the first part of your code can be written like this:

Private Sub CmdButNewOK_Click()
Dim wsWritesheet As Worksheet
Dim lngWriteRow As Long

Dim SNTDay, SNTMonth, SNTYear, SNTDate As String
Dim SUPERDay, SUPERMonth, SUPERYear, SUPERDate As String
Dim DISPDay, DISPMonth, DISPYear, DISPDate As String
Dim SNTHour, SNTMinute, SNTTime As String
Application.ScreenUpdating = False
'check to see if they picked a ward
If SheetPicked = "" Then
MsgBox "You must select a Ward from the list!"
Exit Sub
End If
'Select worksheet picked and move to the first blank row
Set wsWritesheet = ActiveWorkbook.Sheets(SheetPicked)
lngWriteRow = wsWritesheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
wsWritesheet.Range("Z" & lngWriteRow) = Now 'This is the current date and time of the new record

You also need to ensure if you are exiting your code part way through a procedure that you turn back on things like events and screen updating.

Dom

 

[Kentman]

Thanks for the tips Dom.