+ Reply to Thread
Results 1 to 7 of 7

Object error when shifting sub from Form to Module

Hybrid View

  1. #1
    Registered User
    Join Date
    10-11-2016
    Location
    Aberdeen, Scotland
    MS-Off Ver
    2010
    Posts
    49

    Question Object error when shifting sub from Form to Module

    All,

    I am pretty new to coding and I am sure this question has probably been answered a million times before and people think I am complete muppet (they are not wrong)
    Anyway...

    So I have an excel sheet with 4 userforms which are basically duplicates of each other except the last control button which does things slightly different. Therefore a value eg. LA.Value is used in all 4 userforms.

    When I originally coded it I left all the code in all the userforms and a lot of it is duplicated and it worked (after a lot of trial and error). But trying to be smart I wanted to shift the duplicated subs to a module. When I try and to this I get object error.

    I am sure the fix is really simple but my hours spent trawling here and google in general have turned up nothing. Help!

    Code in Userform:
    Private Sub AddRowButton_Click()
    
    BlankCatcherShort 
    'Sub in Module
    If ExitAll = True Then
    Exit Sub
    End If
    
    AddData 
    'Sub in userform (but want to move to module)
    
    End Sub
    Sub in module:
    Sub BlankCatcherShort()
    ExitAll = False
    
    If LA.Value = "" Then
        MsgBox "Local Authority not selected"
        ExitAll = True
        Exit Sub
    End If
    ...
    End Sub
    Last edited by rpjwhite; 10-12-2016 at 11:13 AM.

  2. #2
    Forum Contributor S O's Avatar
    Join Date
    06-25-2015
    Location
    England
    MS-Off Ver
    Office 2010 + 2013 + 2016
    Posts
    103

    Re: Object error when shifting sub from Form to Module

    Please add code tags to your code, you can do this by highlighting the text and pressing the [#] button on the editor toolbar.

    This makes the code easier to read (plus that bright green text just hurts my eyes!)

  3. #3
    Registered User
    Join Date
    10-11-2016
    Location
    Aberdeen, Scotland
    MS-Off Ver
    2010
    Posts
    49

    Re: Object error when shifting sub from Form to Module

    newb error, hope that helped.

  4. #4
    Forum Contributor S O's Avatar
    Join Date
    06-25-2015
    Location
    England
    MS-Off Ver
    Office 2010 + 2013 + 2016
    Posts
    103

    Re: Object error when shifting sub from Form to Module

    Quote Originally Posted by rpjwhite View Post
    newb error, hope that helped.
    certainly did, thanks.

  5. #5
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Object error when shifting sub from Form to Module

    I would guess LA is a control on a userform and so is not in scope in the module...

    The simplest fix is to change the procedure declaration and pass the value from the calling sub

    Private Sub AddRowButton_Click()
    
    BlankCatcherShort la.value
    'Sub in ...
    Sub BlankCatcherShort(str as String)
    ExitAll = False
    
    If str = "" Then
        MsgBox "Local Authority not selected"
        ExitAll = True
        Exit Sub
    End If
    ...
    End Sub
    Last edited by cytop; 10-12-2016 at 05:35 AM.

  6. #6
    Registered User
    Join Date
    10-11-2016
    Location
    Aberdeen, Scotland
    MS-Off Ver
    2010
    Posts
    49

    Re: Object error when shifting sub from Form to Module

    Thanks Cypto will try that and revert.
    LA is a textbox and I don't really declare it anywhere. Now trying to get my head round scope.

  7. #7
    Registered User
    Join Date
    10-11-2016
    Location
    Aberdeen, Scotland
    MS-Off Ver
    2010
    Posts
    49

    Re: Object error when shifting sub from Form to Module

    Thanks everyone
    Cypto's method worked shame I am checking so much stuff but hey ho.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Fix an error in MODULE to run Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    By mchilapur in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-28-2015, 12:40 PM
  2. [SOLVED] Module object error
    By lazyserv in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-31-2015, 11:28 AM
  3. [SOLVED] Compile error: Member already exists in an object module...Trying to create a form
    By MonaP92 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-12-2015, 11:12 AM
  4. [SOLVED] Member already exists in an object module form which this object module derives error
    By Sc0tt1e in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-01-2014, 03:14 AM
  5. Replies: 3
    Last Post: 10-25-2011, 02:12 PM
  6. Runtime error on form (No object)
    By oo0tommyk0oo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-11-2011, 07:18 PM
  7. Replies: 1
    Last Post: 04-10-2005, 07:07 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1