Results 1 to 14 of 14

VB Projects and XLM Sheets cannot be saved in a macro-free workbook.

Threaded View

  1. #1
    Forum Contributor
    Join Date
    08-28-2012
    Location
    Klang, Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    113

    VB Projects and XLM Sheets cannot be saved in a macro-free workbook.

    Hello XL Guru,
    I got no idea why my new work book not working. While the old workbook i used the same code is doing fine but not this.

    Private Sub Workbook_Open()
    Dim wSheet As Worksheet
        For Each wSheet In Worksheets
    
            wSheet.Protect Password:="123", _
            UserInterFaceOnly:=True
    Next wSheet
    End Sub
    
    Sub SaveScreeningWithNewName()
        Dim NewFN As Variant
        ' Copy Screening to a new workbook
        Sheets(Array("Screening", "Agent-Sea", "Agent-Air", "B.Confirmation", "S.I.", "HBL")).Copy
        ActiveSheet.Shapes.Range(Array("****")).Delete
        NewFN = "\\CYCHUA-PC\Users\Public\Documents\Agent Record\" & Sheets(1).Range("G1").Text & Sheets(1).Range("H2").Value & " " & Sheets(1).Range("c3").Text & ".xlsx"
        ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
        ActiveWorkbook.Close
    End Sub
    Sub NextScreening()
        Sheets(1).Range("h1").Value = Range("h1").Value + 1
    End Sub
    
    Sub uncheck_all()
    Dim sh As Shape
    Application.ScreenUpdating = False
        For Each sh In ActiveSheet.Shapes
          If sh.Type = msoOLEControlObject Then
            If TypeName(sh.OLEFormat.Object.Object) = "CheckBox" Then sh.OLEFormat.Object.Object = False
          End If
          If sh.Type = msoFormControl Then
            If sh.FormControlType = xlCheckBox Then sh.OLEFormat.Object = False
          End If
        Next sh
    Application.ScreenUpdating = True
    End Sub
    
    Sub ClearUnlockedCells()
     
        Dim wks As Worksheet
     
        For Each wks In ThisWorkbook.Worksheets
            On Error Resume Next
            wks.UsedRange.Value = vbNullString
            Err.Clear: On Error GoTo -1: On Error GoTo 0
        Next wks
     
        Set wks = Nothing
     
    End Sub
    When i test run on the macro, it kept showing message as titled mentioned above.
    Greatly appreciated, if any1 would point out where my mistake is.

    I understand that macro could not run on a protected sheets. After google for hours, i found this userinterface thingy, still no luck.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1