Announcement

Collapse
No announcement yet.

Run-Time error '438': Object Doesn't support this property or method vba

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Run-Time error '438': Object Doesn't support this property or method vba



    Hi all, this is post on what has been a very useful resource in my vba learning!

    I have the run-time error:
    Run-Time error '438': Object Doesn't support this property or method in the code at the bottom of this post. Does anyone know why? Normally it highlights the line but there is no highlighting when it errors.

    Bit of background, I am doing a task for work as told. I know changing the settings on open is bad practice, but that's what the client wants.

    This is the second excel to open. I am using a VBA that pulls information from the Google Analytics API (see automate analytics) however because of the way the formula is written calculations must be set to manual. I know this is bad design but it is unavoidable (or at least my research so far says it is).

    Because my boss doesn't want the client to fiddle with settings themselves, they open up a hidden workbook that only shows a userfrom. This userform sets calculations to manual and stops events and then opens the actual spreadsheet so it doesn't process on the open event. This all works, and then the formulas do with various vba's, however, the white label and the full screen settings don't work on open when opened through the other spreadsheet and the runtime error happens only when I try to run it when in vba and hitting run.

    Apart from the obvious - don't design your sheet this way (as I can't help it), does anybody know how to fix the error? Or if there is a simple vba that says on open and after open don't calculate the cells with different formulas in?


    Code:
    Private Sub Workbook_open()
    With Application
    .EnableEvents = True
    .Sheet9.Range("B11:D70").Calculate
    .Caption = "company name"
    .Sheet9.Range("B11:D70").Calculate
    
    End With
    
    Dim ws As Worksheet
    
    'Full screen No headers
    If Sheet9.Range("G34") = Sheet9.Range("G29") Then
    On Error Resume Next
        
            For Each ws In Worksheets
            ActiveWindow.DisplayHeadings = False
                Next ws
            With Application
                .DisplayFullScreen = False
                .CommandBars("Worksheet Menu Bar").Enabled = False
            End With
        
    End If
    
    'Full screen with headers
    
    If Sheet9.Range("G34") = Sheet9.Range("G30") Then
    On Error Resume Next
            For Each ws In Worksheets
            ActiveWindow.DisplayHeadings = True
                Next ws
            With Application
                .DisplayFullScreen = True
                .CommandBars("Worksheet Menu Bar").Enabled = False
            End With
        
    End If
    
    'No full screen with headers
    
    If Sheet9.Range("G34") = Sheet9.Range("G30") Then
    On Error Resume Next
            For Each ws In Worksheets
            ActiveWindow.DisplayHeadings = True
                Next ws
            With Application
                .DisplayFullScreen = False
                .CommandBars("Worksheet Menu Bar").Enabled = False
            End With
        
    End If
    'No full screen no headers
    If Sheet9.Range("G34") = Sheet9.Range("G30") Then
    On Error Resume Next
            For Each ws In Worksheets
            ActiveWindow.DisplayHeadings = False
                Next ws
            With Application
                .DisplayFullScreen = False
                .CommandBars("Worksheet Menu Bar").Enabled = False
            End With
        
    End If
        
    End Sub
    (What code does - when in spreadsheet, button to choose setting - this gets placed in a cell which then decides the settings) I am still deciding the ways the spreadsheet can open, so some of the code in the true or false may contradict the notes

    Sorry if its mis-mashed, I'm just teaching myself. Oh and I did search, figured it could be a syntax style of error, but every similar error seems to be for a very specific instance

    Cheers

    Rachel
    Last edited by rachel.sw; April 15th, 2011, 22:04. Reason: needed to add info

  • #2
    Re: Run-Time error '438': Object Doesn't support this property or method vba

    The sheet is not a property of the application. Change this part at the start:
    Code:
    With Application
    .EnableEvents = True
    .Sheet9.Range("B11:D70").Calculate
    .Caption = "company name"
    .Sheet9.Range("B11:D70").Calculate
    to this:
    Code:
    With Application
    .EnableEvents = True
    Sheet9.Range("B11:D70").Calculate
    .Caption = "company name"
    Sheet9.Range("B11:D70").Calculate
    Rory
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Programmers combine theory and practice: nothing works and they donít know why

    Comment


    • #3
      Re: Run-Time error '438': Object Doesn't support this property or method vba

      Beaten to it

      Comment


      • #4
        Re: Run-Time error '438': Object Doesn't support this property or method vba

        ah, ha, cheers, must make note of that.

        Problem is, I'm now getting Runtime 1004: application-defined or object-defined error?

        Comment


        • #5
          Re: Run-Time error '438': Object Doesn't support this property or method vba

          Comment out the 'On Error...' statements and go to the Tools/Options menu. Select the 'General' Tab and select ;Break on all errors'

          Then your code will stop each time there's an error - and you can ask about the errors... Once it's working you can put the error handlers (or not!) back...

          Comment


          • #6
            Re: Run-Time error '438': Object Doesn't support this property or method vba

            Hard to debug if you can't tell us where the errors occur...

            I would suggest:
            1. Move all the code from the Workbook_Open event to a routine in a normal module and then call that routine from the Open event using Application.OnTime
            2. Change this code:
            Code:
             For Each ws In Worksheets
                    ActiveWindow.DisplayHeadings = False
                        Next ws
            to this:
            Code:
             For Each ws In thisworkbook.Worksheets
                 ws.activate
                    ActiveWindow.DisplayHeadings = False
                        Next ws
            otherwise you are merely turning headings off for the same window repeatedly.

            3. Remove all the On Error Resume Next statements.
            Rory
            Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Programmers combine theory and practice: nothing works and they donít know why

            Comment


            • #7
              Re: Run-Time error '438': Object Doesn't support this property or method vba

              Originally posted by rory View Post
              Hard to debug if you can't tell us where the errors occur...

              I would suggest:
              1. Move all the code from the Workbook_Open event to a routine in a normal module and then call that routine from the Open event using Application.OnTime
              Ok, I almost followed this: I added a module and labelled as below. in the Workbook_open sub I have written:
              Code:
              private Sub workbook_open()
              Application.OnTime
              end sub
              do I need something like OpenExcel.show?

              Code:
              Sub OpenExcel()
              
              With Application
              .EnableEvents = True
              .Caption = "iProspect"
              Sheet9.Range("B11:D70").Calculate
              Sheet9.Range("B11:D70").Calculate
              End With
              
              
              
              Dim ws As Worksheet
              
              'Full screen No headers
              If Sheet9.Range("G34") = Sheet9.Range("G29") Then
              
                  
                      For Each ws In Worksheets
                           ws.Activate
                      ActiveWindow.DisplayHeadings = False
                          Next ws
                      With Application
                          .DisplayFullScreen = False
                          .CommandBars("Worksheet Menu Bar").Enabled = False
                      End With
                  
              End If
              
              'Full screen with headers
              
              If Sheet9.Range("G34") = Sheet9.Range("G30") Then
              
                      For Each ws In Worksheets
                           ws.Activate
                      ActiveWindow.DisplayHeadings = True
                          Next ws
                      With Application
                          .DisplayFullScreen = True
                          .CommandBars("Worksheet Menu Bar").Enabled = False
                      End With
                  
              End If
              
              'No full screen with headers
              
              If Sheet9.Range("G34") = Sheet9.Range("G30") Then
              
                      For Each ws In Worksheets
                           ws.Activate
                              ActiveWindow.DisplayHeadings = True
                          Next ws
                      With Application
                          .DisplayFullScreen = False
                          .CommandBars("Worksheet Menu Bar").Enabled = False
                      End With
                  
              End If
              'No full screen no headers
              If Sheet9.Range("G34") = Sheet9.Range("G30") Then
              
                      For Each ws In Worksheets
                           ws.Activate
                              ActiveWindow.DisplayHeadings = False
                          Next ws
                      With Application
                          .DisplayFullScreen = False
                          .CommandBars("Worksheet Menu Bar").Enabled = False
                      End With
                  
              End If
              
              End Sub
              Originally posted by rory View Post
              2. Change this code

              otherwise you are merely turning headings off for the same window repeatedly.
              - I have done this, cheers

              3. Remove all the On Error Resume Next statements.done, cheers, I'd copied the code, hadn't noticed the on error, I normally do avoid those
              Originally posted by cytop View Post
              Comment out the 'On Error...' statements and go to the Tools/Options menu. Select the 'General' Tab and select ;Break on all errors'

              Then your code will stop each time there's an error - and you can ask about the errors... Once it's working you can put the error handlers (or not!) back...
              - I have done this now, thanks for the tip - the error still happens on the line:

              Code:
              Sheet9.Range("B11:D70").Calculate

              Comment


              • #8
                Re: Run-Time error '438': Object Doesn't support this property or method vba

                Almost:

                Code:
                private Sub workbook_open()
                Application.OnTime Now() + TimeSerial(0,0,1), "OpenExcel"
                end sub
                Rory
                Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Programmers combine theory and practice: nothing works and they donít know why

                Comment


                • #9
                  Re: Run-Time error '438': Object Doesn't support this property or method vba

                  Cheers again, so I've done all of this and have a brad new error, attached below, any ideas?

                  Click image for larger version

Name:	error.JPG
Views:	1
Size:	16.0 KB
ID:	1100134

                  Comment


                  • #10
                    Re: Run-Time error '438': Object Doesn't support this property or method vba

                    You didn't call the module OpenExcel too, did you?
                    Rory
                    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Programmers combine theory and practice: nothing works and they donít know why

                    Comment


                    • #11
                      Re: Run-Time error '438': Object Doesn't support this property or method vba

                      Originally posted by rory View Post
                      You didn't call the module OpenExcel too, did you?
                      ah ha, yes I did, I'll rename - sorry temporarly unattached the attachment as it had the file destination on it

                      Comment


                      • #12
                        Re: Run-Time error '438': Object Doesn't support this property or method vba

                        So I have now done all of this, and when I run it, it works - no errors, however on opening, the caption and settings aren't changing,

                        It's really not my day today!

                        Any ideas?

                        Comment


                        • #13
                          Re: Run-Time error '438': Object Doesn't support this property or method vba

                          Have you tried stepping through the code to see whether the cell values do match the criteria and which, if any, parts of the code are being run?
                          Rory
                          Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Programmers combine theory and practice: nothing works and they donít know why

                          Comment


                          • #14
                            Re: Run-Time error '438': Object Doesn't support this property or method vba

                            On open, I've checked and none of it has run, however, when I go into 'This workbook' and the open sub, and press F5, the whole thing runs (with limited success - the caption does change and the headers disappear and it changes to full screen but then changes back)

                            Is this because on activate all is switched off (events nad manual calc?) or am I way out? also, in order to learn, how come the module couldn't be spelt the same?

                            Thanks very much for your help so far, it may be Monday when I next have access to the spreadsheet, but any help is greatly appreciated!

                            Cheers
                            Rachel

                            Comment


                            • #15


                              Re: Run-Time error '438': Object Doesn't support this property or method vba

                              Open the VBEditor, open the Immediate Window (Ctrl+g) and type in:
                              Code:
                              Application.Enableevents = True
                              and press enter. Then reopen the workbook.

                              Re the module name, it's not a good idea to name the module the same as a routine (due to the confusion!) but if you do then you would have to refer to the routine as OpenExcel.OpenExcel so that excel knows what you are talking about.
                              Rory
                              Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Programmers combine theory and practice: nothing works and they donít know why

                              Comment

                              Working...
                              X