Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    23

    Unanswered: disable/re-enable Workbook_Open() and howto save as Text()today

    Hi VB expert,

    In my Excel 2002 template (let's say template.xls) I have a macro that auto-populates the required cells. I name the macro as MacroStockingUpdate in the Standard Module.

    This Excel 2002 template has an Auto-Start macro in ThisWorkbook as below:


    Private Sub Workbook_Open()
    Call MacroStockingUpdate
    End Sub


    After the auto-execution tasks are done, I save it as StockingStatus.xls.
    My problem is that when the viewers open StockingStatus.xls, it also load Workbook_Open() and I do not want that.

    (1) what VB code would disable the Workbook_Open() after I saved it as StockingStatus.xls ?
    (2) what VB code would enable again the Workbook_Open() when I saved it back as template.xls after erasing the populated data ?
    (3) what VB code to allow me to save the worksheet as "StockingStatus" with System ()today's date (eg. StockingStatus_10-Oct-2004, or any other date format as long as it's today) ?


    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by alienscript
    Hi VB expert,

    (1) what VB code would disable the Workbook_Open() after I saved it as StockingStatus.xls ?
    (2) what VB code would enable again the Workbook_Open() when I saved it back as template.xls after erasing the populated data ?
    (3) what VB code to allow me to save the worksheet as "StockingStatus" with System ()today's date (eg. StockingStatus_10-Oct-2004, or any other date format as long as it's today) ?


    Thanks in advance.
    Here's some examples to do what you asked.
    Code:
    ' (1) and (2)
    
    Private Sub Workbook_Open()
    ' Put this line in your workbook_open
    ' Event.
    
    If Not ThisWorkbook.Name = "template.xls" Then
        Exit Sub
    End If
    End Sub
    
    ' (3)
    Sub SaveWorkbook()
    
       strAppend = Format(Date, "DD-MMM-YYYY")
       strPath = "C:\Documents and Settings\Owner\My Documents\"
       fSaveName = strPath & "StockingStatus_" & strAppend
    
       ThisWorkbook.SaveAs fSaveName
    End Sub
    ~

    Bill

  3. #3
    Join Date
    Jul 2004
    Posts
    23
    It works great !
    Thanks so much.

Posting Permissions

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