on open
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: on open

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Calgary
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Is it possible for me to manipulate this code such that if the filename is OPEX Variance Analysis rev.2.xls it will run the macro, otherwise, nothing will happen.

    Here is the code that I have so far.

    Option Explicit



    Private Sub Workbook_Open()
    Dim filename As String
    If filename = "Y:IQ38316DataExcelOPEX-CAPEX Input SheetsOPEX Variance Analysis rev.2.xls" Then

    'set range you want to clear
    Worksheets("Variance Analysis").Range("K30:T53").ClearContents

    'returns OrgCode Values back to default "*" value
    Worksheets("Variance Analysis").Range("C8:C10").Value = "*"

    'returns month back to default of "01-January"
    Worksheets("Variance Analysis").Range("B1").Value = "'01"

    End If


    End Sub



  2. #2
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi. This is not good way.
    Please try this, while you wait for other methods of other respondents.

    'Please copy this into a standard module in PERSONAL.XLS
    Sub BookNameCheck()
    Dim filename As String, wb As Workbook
    filename = "OPEX Variance Analysis rev.2.xls"
    For Each wb In Workbooks
    If wb.Name = filename Then
    'set range you want to clear
    Worksheets("Variance Analysis").Range("K30:T53").ClearContents
    'returns OrgCode Values back to default "*" value
    Worksheets("Variance Analysis").Range("C8:C10").Value = "*"
    'returns month back to default of "01-January"
    Worksheets("Variance Analysis").Range("B1").Value = "'01"
    End If
    Next
    End Sub

    'Copy this into ThisWorkbook module in Each Book
    Private Sub Workbook_Open()
    Application.Run "PERSONAL.XLS!BookNameCheck"
    End Sub

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com