Run time error '40036'

[Cricketer]

Can anyone tell me in simple terms how to correct the following.
On sheet named 'Autom' I have 3 command buttons to enable users to move to other sheets quickly.
2 work with no problems but the third (most important one) returns the following error.

Run time error '40036' Application-defined or object-defined error.

The macros are very simple
Sheets("name").Select
ActiveWindow.ScrollRow = 1
End Sub

Debug points to first line 'Sheets("name").Select.
Ihave recorded the macro so no mistake on the name which is 'Autom Conf'. Tried altering the name but no improvement - it will not move to this sheet.
No problem selecting the tab to move and the sheet which has 21 combo boxes functions ok and runs various macros attached to combo boxes.
Thanks for any help.

 

[Legacy 98055]

???
Try:
Sheets("name").Activate
tom

 

[Cricketer]

Tom
Thanks for your suggestion but still had the same run time error

 

[Legacy 98055]

I don't know Crick? Scratching my head...
Send me the workbook if you'd like and I'll try and figure out what's wrong if I can.
Tom
tstom@hotmail.com

Found problem.
To find compile errors, from your VBE, click on Debug, Compile VBAProject...
This message was edited by TsTom on 2002-11-25 15:51

 

[somf]

That is SO weird. Same error, totally different situation (came up with error during a Workbook_BeforeSave event), and I was totally baffled.

Followed Tom's suggestion of compiling, and found a dangling End If on the other side of the world of my workbook.

For any other people with this problem who have happened to stumble across this as I did, trust me: just go and manully compile your code, even if you are sure it won't help.

Rob

 

[froodley]

ACK!

I am having this error come up all of a sudden in a macro that was working fine and hasn't been changed. I followed the tip to trace through the compile and correct all the little errors there, but it's still happening. So I'm hoping that I'm just missing something and you wizards can pick it up.

The error is occurring on the .Unprotect line, but it occurs no matter what I do there... if I try .Activate or .Name or whatever.

TIA,
frood

Sub copyTemplate()


    Dim sheetName As String
    Dim newWorkbook As Workbook
    
    Set newWorkbook = makeNewWorkbook(1)
    
    sheetName = "blah"
    
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With
    
        
    Workbooks("Template.xls").Sheets("Template").Copy _
        Before:=newWorkbook.Sheets(1)
    
    newWorkbook.Sheets("Sheet1").Delete
    'Removes the default sheet
    
    newWorkbook.Sheets("Template").Unprotect
    newWorkbook.Sheets("Template").Name = sheetName
        
        
    newWorkbook.Sheets(sheetName).Buttons(2).Enabled = True
    newWorkbook.Sheets(sheetName).Buttons(3).Enabled = True
    
    newWorkbook.Sheets(sheetName).Buttons(1).Delete

    addADOReference newWorkbook

    newWorkbook.Sheets(sheetName).Activate

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With
    

End Sub

The macro it calls (makeNewWorkbook):

Function makeNewWorkbook(wsCount As Integer) As Workbook
    Dim originalWorksheetCount As Long
    
    Set makeNewWorkbook = Nothing
    
    If wsCount < 1 Or wsCount > 255 Then Exit Function
    
    originalWorksheetCount = Application.SheetsInNewWorkbook
    
    Application.SheetsInNewWorkbook = wsCount
    Set makeNewWorkbook = Workbooks.Add
    
    Application.SheetsInNewWorkbook = originalWorksheetCount
End Function

 

[froodley]

The only thing I can think of is that I edited the workbook from excel 2007 the night it started to show this issue. It was created in and intended for excel 2003. The behavior shows up in both editions, though.

I tried creating a new workbook and copying everything over, but that made it WORSE - now the 40036 occurs in the step where the Template sheet is copied into newWorkbook.

So I went down a layer and created a new workbook and copied the cells and buttons over instead of the template worksheet... then copied over the macros with cut and paste instead of export/import... and got the same result, 40036 when copying the worksheet.

I am losing my mind over this! It was supposed to go to the customer next Monday, was almost finished, and out of the blue I've got nothing. Please help!