1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

auto run macro on excel start

Discussion in 'Business Applications' started by whschimmel, Dec 7, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. whschimmel

    whschimmel Thread Starter

    Joined:
    Jul 7, 2008
    Messages:
    168
    Hello,

    I've got this code to get rid of all leading apostrophes in the Excel (2003) Cels:
    Code:
    Sub Auto_Open()
       
        Dim S As Range, temp As String
        Worksheets("qryOfficeNetForeign").Activate
        With Worksheets(1)
        If MsgBox("Are you sure you want to remove all leading apostrophes from the entire sheet?", _
        vbOKCancel + vbQuestion, "Remove Apostrophes") = vbCancel Then Exit Sub
        Application.ScreenUpdating = False
        For Each S In ActiveSheet.UsedRange
            If S.HasFormula = False Then
                 'Gets text and rewrites to same cell without   the apostrophe.
                S.Value = S.Text
            End If
        Next S
        Application.ScreenUpdating = True
    End Sub
    This code runs fine when I use the shortkey for it (CTRL + R), but when I try to autorun it I get an error:
    Code:
    Runtime Error 91: Object Variable Or With Block Variable Not Set 
    When I click the debug button, this line is selected:
    Code:
    For Each S In ActiveSheet.UsedRange

    How can I autorun this code on opening of Excel, I would also like to run the code only if the sheetname is: "qryOfficeNetForeign"


    I am no VBA expert, so am getting only errors :mad:


    Thanks,
    Wouter
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    This statement is where your error is coming from...
    Code:
        With Worksheets(1)
    You have an opening With but not a corresponding End With, hence error 91.

    The Auto_Open, while it's legacy supported, is not the norm anymore, so I'd recommend using a Workbook_Open procedure. This should work...
    Code:
    Private Sub Workbook_Open()
        Dim S As Range, temp As String, WS As Worksheet
        On Error GoTo ExitRoutine
        Set WS = Me.Worksheets("qryOfficeNetForeign")
        If MsgBox("Are you sure you want to remove all leading apostrophes from the entire sheet?", _
                  vbOKCancel + vbQuestion, "Remove Apostrophes") = vbCancel Then Exit Sub
        Application.ScreenUpdating = False
        For Each S In WS.UsedRange
            If S.HasFormula = False Then
        'Gets text and rewrites to same cell without   the apostrophe.
                S.Value = S.Text
            End If
        Next S
    ExitRoutine:
        Application.ScreenUpdating = True
    End Sub
    HTH
     
  3. whschimmel

    whschimmel Thread Starter

    Joined:
    Jul 7, 2008
    Messages:
    168
    I tried the code from Zack Barresse but get an error:
    Code:
    Compile Error: Invalid Use of Me Keyword
    Also when I open the file it does not run, so I the autorun is not working properly.
    I am using Excel 2003, no 2007 or 2010

    With my first piece of code it all started automaticly, but get an error:
    Code:
    For Each S In ActiveSheet.UsedRange
    --> Looks like the code runs before the sheet is opened, how can I make sure the sheet is opened before the code runs?

    Sorry about the one line "With Worksheets(1)", I forgot to remove that one. But still get the error mentioned above when I remove this line.

    I placed my code in my Personal macro workbook (Personal.xls), so it can be run on all opened excel sheets
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    The code was designed to go into a workbooks ThisWorkbook module. If you put it into any other type of module, the Me keyword may not work, as it refers to the module which houses it. Also, it is meant to run on a single workbooks open routine, not on every workbook as it opens. Is this what you're after?
     
  5. whschimmel

    whschimmel Thread Starter

    Joined:
    Jul 7, 2008
    Messages:
    168
    Zack first of all thanks for placing this code online! Saw your name at that post (y)(y)(y)

    Yes I have some digital payment files in XLS format in which the leading apostrophe characters need to be removed.

    I just read about the personal macro workbook yesterday, therefore I placed the code/module in there (Personal.xls). I would like:

    • Run the code every time excel opens, but only if the (first) sheetname is: "qryOfficeNetForeign"

    • If possible to automate the process, only give a message box if an error occurred.

    The last point is not necessary, I can also make a script that runs the file and sends an keystroke to the needed window.
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Ah, that is different. There are a couple of ways to do this, one way using a class module to capture that type of event (other workbooks opening), and one slightly less robust way without it. You can do this if you want, but it requires some steps to carefully be followed. For more information you can find almost anything you need on this topic here...

    http://www.cpearson.com/excel/AppEvent.aspx

    For this to work, I'm assuming you're going to be working with your Personal.xls workbook.

    In your ThisWorkbook, post this at the top of the module (below any 'Option' statements)...
    Code:
    Private WithEvents XL As Application
    In your Personal.xls Workbook_Open procedure, have this line (posting the entire routine, just add the one line of code if you already have one)...
    Code:
    Private Sub Workbook_Open()
        Set XL = Application
    End Sub
    Then you will have access to multiple workbook events, and you can paste this code in the same module...

    Code:
    Private Sub XL_WorkbookOpen(ByVal Wb As Workbook)
        Dim S As Range, temp As String, WS As Worksheet
        On Error GoTo ExitRoutine
        Set WS = Wb.Worksheets("qryOfficeNetForeign")
        Application.ScreenUpdating = False
        For Each S In WS.UsedRange
            If S.HasFormula = False Then
        'Gets text and rewrites to same cell without   the apostrophe.
                S.Value = S.Text
            End If
        Next S
    ExitRoutine:
        Application.ScreenUpdating = True
    End Sub
    So, if that was all you had in your ThisWorkbook module, the entire module's code would look like this...

    Code:
    Option Explicit
    
    Private WithEvents XL As Application
    
    Private Sub Workbook_Open()
        Set XL = Application
    End Sub
    
    Private Sub XL_WorkbookOpen(ByVal Wb As Workbook)
        Dim S As Range, temp As String, WS As Worksheet
        On Error GoTo ExitRoutine
        Set WS = Wb.Worksheets("qryOfficeNetForeign")
        Application.ScreenUpdating = False
        For Each S In WS.UsedRange
            If S.HasFormula = False Then
        'Gets text and rewrites to same cell without   the apostrophe.
                S.Value = S.Text
            End If
        Next S
    ExitRoutine:
        Application.ScreenUpdating = True
    End Sub
    This is the non-class method of doing it. You will need to save and close Excel and re-open to get it to work (or put your cursor in the Workbook_Open procedure and press F5). Remember, any changes made to that module will break the connection to the "XL" variable and you will either need to close/re-open Excel or re-run the open procedure manually, in order to get it to work again.

    Let us know if this works for you.
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1030133

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice