VB help

Posted on 2012-09-19
Last Modified: 2012-09-20
The code below appears to copy sheet 3 only and insert this into an outlook file. The problem I get is that with the new 'file' the vb password is lost and it is possible to view the vb code. Is it possible to amend the code so that the VB password remains intact or if not possible a new password is added.

Many thanks for your help, it is much appricated.

Private Sub CommandButton1_Click()
Dim wb1 As Workbook, wb2 As Workbook
    Dim Sht1 As Worksheet, strTempShtname As String, TmpSheet As Worksheet
    Dim pass As String, obj As OLEObject

    pass = InputBox("please enter a password")
    If Len(Trim(pass)) = 0 Then GoTo ExitNow
    If pass <> "gaz5" Then
        MsgBox "Incorrect Password"
        Exit Sub
    End If
    Application.DisplayAlerts = False
    On Error GoTo Err
    Dim wbk As Workbook
'Save temporary file so that it can be attached to an email
   OutFile = Environ$("temp") & "\" & Format(Now, "DD-MM-YYYY") & " Copy" & ".xls"  'construct temp file name
   If Dir(OutFile) <> "" Then Kill OutFile   ' delete any previously saved temp file
   ' copy sheet to new workbook
   Set wbk = ActiveWorkbook
   wbk.SaveAs Filename:=OutFile  ' save temp file
   ' construct email, attaching temp file to email
   Set OutApp = CreateObject("Outlook.Application")
   Set OutMail = OutApp.CreateItem(0)
   With OutMail
      .Subject = "new file "
      .Body = "please see attachment"
      .Attachments.Add OutFile
   End With
   Set OutMail = Nothing
   Set OutApp = Nothing
   wbk.Close False
   If Dir(OutFile) <> "" Then Kill OutFile   ' delete the temp file
    Application.DisplayAlerts = True
    Exit Sub
    MsgBox Err.Description
End Sub
Question by:Kiwi-123
    1 Comment
    LVL 61

    Accepted Solution

    Try revising your SaveAs line to include a password:

       wbk.SaveAs Filename:=OutFile, Password := "Password1234"

    Open in new window

      ' save temp file

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now