Results 1 to 5 of 5

Thread: File Save AS VBA error

  1. #1

    File Save AS VBA error



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have 2 issues going on here. I have code in my worksheet area and code in my workbook area. Here is my code:

    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet = Unprotect
    If Range("G9").Value = "Implant Pass-through: Auto Invoice Pricing (AIP)" Then
    Range("B67").Locked = False
    Else
    Range("B67").Locked = True
    End If
    If Range("G9").Value = "Implant Pass-through: PPR Tied to Invoice" Then
    Range("B69").Locked = False
    Else
    Range("B69").Locked = True
    End If
    ActiveSheet.Protect
    End Sub
    
    Option Explicit
    Private Sub Workbook_Open()
    Dim wSheet As Worksheet
    For Each wSheet In Worksheets
    wSheet.Protect Password:="audit", _
    UserInterFaceOnly:=True
    Next wSheet
    End Sub
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim Start As Boolean
    Dim Rng1 As Range
    Dim Prompt As String, RngStr As String
    Dim Cell As Range
    
    Sheets("Form").Unprotect Password:="audit"
    Set Rng1 = Sheets("Form").Range("B4, B7, B8, B9, B12, B74, G9, A16, B10, D10, E74, E77")
    'message is returned if there are blanks or no value in required fields
    Prompt = "Please make sure are highlighted fields are filled in." & vbCrLf & _
    "The following cells are incomplete and have been highlighted yellow:" _
    & vbCrLf & vbCrLf
    Start = True
    'highlights the blank cells
    For Each Cell In Rng1
    If Cell.Value = vbNullString Or Cell.Value = 0 Or Cell.Value <= 0 Then
    Cell.Interior.ColorIndex = 6 ' color yellow
    If Start Then RngStr = RngStr & Cell.Parent.name & vbCrLf
    Start = False
    RngStr = RngStr & Cell.Address(False, False) & ", "
    Else
    Cell.Interior.ColorIndex = 0 '** no color
    End If
    Next
    Sheets("Form").Protect Password:="audit", _
    UserInterFaceOnly:=True
    If RngStr <> "" Then RngStr = Left$(RngStr, Len(RngStr) - 2)
    If RngStr <> "" Then
    MsgBox Prompt & RngStr, vbCritical, "Incomplete Data"
    Cancel = True
    Else
    'saves the changes before closing
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="C:\Users\" & Environ$("username") & _
    "\Documents\Audits\" & Range("B9").Text & Chr(32) & Range("B7").Text & _
    Format(Range("B10").Value, “MM-DD-YYYY”) & ".xlsx", _
    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Application.DisplayAlerts = True
    
    End If
    Set Rng1 = Nothing
    ActiveSheet.Protect
    End Sub
    


    The problems:
    1) Code in the worksheet always locks cell B67 and allows B69 to remain unlocked

    2) The code on the workbook works fine and will saveas like I want, however then I get a pop-up after I click the blue disk when it saved that asks me again to SaveAs and has it autoformatted as .xlsm and I have to click cancel and it takes me to the .xlsx I just saved based on the template. The template is automatically closed after I saveas which is fine. However, when I pull the template back up, it gives me a run-time error 1004 that my password is wrong and it never prompts for password. I actually do not want it to. The only reaosn I have a password is I want to retain the formulas in 4 cells on the sheet and do not want the end-users putting their own stuff in these 4 cells. The autocount and autosum.

    I attached my workbook hoping someone can help me figure these 2 issues out. You will notice the workbook code is commented out where the highlighting is done. That is because I cannot do saveas template without that event happening all the time. Then, some of my hidden sheets I had to delete because of PHI, privacy stuff for my organization and patients we service. I also had to file saveas .xlsm because I cannot upload a .xltm on this site.





    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,268
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    The first problem in this thread is related to this one, is that correct? Let's deal with that part in the original thread.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,268
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    This is virtually impossible for me to test, as I don't know how to pass your validation fields. I think you're going to need to provide us a little more help in that area to help you. It would also be nice if you uploaded a file that didn't have any password protection in place, as the password to work with is not "audit" in this file.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    Actually, based on your input for what goes in the worksheet event, that works great after I copied the 2nd changes you made. I think though I need to actually change the event under the workbook to a beforeclose and not a save. That is what I think the problem is. Going to test it out here and let you know.

  5. #5
    Ok. Yes. It almost works great. I changed it to beforeclose and when I click the red X, it will highlight what the end-user needs to fill in, if they did fill in all the required fields, the protect cells and the items you helped me with work great.

    The only problem is it will ask the end-user for the password and I was trying to avoid the nurses having to do this. Our nurses are not tech savvy so I am trying to make this as easy for them as possible. So, I will research how to get around that enter password popup.

    The second issue is when I then go back and try and reopen the template, I get this error: run-time error 1004 The password you supplied is not correct. verify that the CAPS lock key is off and be sure to use the correct capitalization. Then I click debug and it takes me to my VBA workbook where my password code is. It never gives me the option to put it in prior to opening and I do not have my caps lock on. I will research how to fix this too.

Posting Permissions

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