VBA Code to Strip Excel Workbook VBA While Leaving Worksheets VBA, Truncating a Range on a Worksheet

[QC_Guy]

Hi. New here, so please forgive me if I post something incorrectly. Also forgive the long post.

I have a two-worksheet macro-enabled Excel workbook that, when opened, "forces" the user to save the workbook to a specified directory. It seems to work fine and the code for that is:

Private Sub Workbook_Open()
MsgBox ("This workbook MUST be saved to the I:\ drive before use.")
Dim Show_Box As Boolean
Dim Response As Variant
' Set the Show_Dialog variable to True.
Show_Box = True
' Begin While loop.
While Show_Box = True
' Show the input box.
Response = InputBox("My Instructions to users", _
"Save File Instructions")

' See if Cancel was pressed.
If Response = "" Then
' If Cancel was pressed,
' break out of the loop.
' Show_Box = False
Else
' Test to make sure an entry was made
If Response <> "" Then
' Set the path to save the file to
MyPath = "Path I specify"
' Set the format of the saved file
ActiveWorkbook.SaveAs Filename:=MyPath & "\" & Response, FileFormat:=51, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False
Show_Box = False
Else
' Since I set no limitations on the file name, the MsgBox is not used but is here in case I need to change things
MsgBox "Please Enter a Valid File Name"
End If
End If
' End the While loop.
Wend
End Sub

When the users enter the file name, a popup window opens stating,
"The following features cannot be saved in macro-free workbooks:
VB Project
To save a file with these features, click No, and then choose a macro-enabled file type in the file type list."

Thus far, I have been having the users press 'Yes' to save as a macro-free workbook. If they hit 'No', a VB box opens stating, "VB projects and XLM Sheets cannot be saved in a macro-free workbook ('Debug' is one of the buttons in the box but it is grayed out). As I stated, clicking 'Yes' thus far has been fine, but I now need the saved workbook to contain VBA on one of the worksheets.

So, I would like the code to automatically strip the workbook VBA code (above) and automatically save the new workbook as a macro-enabled workbook while leaving the code I need on the worksheet in-tact and automatically active (see below).

The code I need on the worksheet is for a range of cells (e.g. E1:E100) to be continuously monitored for user entry of numbers (e.g. 9.99876, 101.2, 303.2987, 196, and such - no negative numbers, just positive). Any numbers less than 100 need to be truncated to one decimal place (9.99876 would become 9.9, 99.99 would become 99.9) and numbers greater than or equal to 100 would be truncated to no decimal places (101.2 would become 101, 196 would remain as 196).

I'm not sure how much of this is possible. I am having the IT administrator set the internal drive the to a "Secured Site" so that macros are enabled all the time.

Any help, guidance, and suggestions would be greatly appeciated. Sorry again for the length of the post.

 

[VoG]

Hello. This isn't very clear, at least to me. To save as a macro enabled workbook FileFormat should be 52, not 51.

This may help you http://www.cpearson.com/excel/VBE.aspx

 

[QC_Guy]

Solved: VBA Code to Strip Excel Workbook VBA While Leaving Worksheets VBA, Truncating a Range on a Worksheet

I finally had a breakthrough last night as I was helping my daughter with her science fair project (always good to step away to get a better perspective - a little sleep helps too). The code below does exactly what I want it to do. It doesn't strip the code, but it does disable it in the saved macro-enabled workbook. I was a little too vague in my initial post and I apologize for that.

I do have another question I'll post in another thread. It concerns a macro that can "monitor" a range of data entry cells on a worksheet for user input.

Thank you.

<!-- BEGIN TEMPLATE: bbcode_code -->

<!-- END TEMPLATE: bbcode_code -->Private Sub Workbook_Open()
' Written by Authors
' Last updated 02/24/2011
    ' Check to see if the opened workbook is the template workbook or a saved workbook.
    ' If the opened workbook is the template, the SaveAs code below will run.
    ' If the opened workbook is a previously saved workbook, the code below is skipped.
    If ActiveWorkbook.Name = "TemplateFileName.xlsm" Then
 
        ' Open a Message Box informing the user that they MUST save the workbook before use.
        MsgBox ("This workbook MUST be saved to the I:\ drive before use.")
        Dim Show_Box As Boolean
        Dim Response As Variant
        ' Set the Show_Dialog variable to True.
        Show_Box = True
        ' Begin While loop.
        While Show_Box = True
             ' Show the Save File Instructions Input Box.
             Response = InputBox("Save File Instructions", _
                  "Save File Formatting Instructions")
             ' Check to ensure the user entered a file name.
             If Response = "" Then
             Else
                  ' Test to make sure an entry was made.
                  If Response <> "" Then
                       ' Set the path on the I:\ drive to save the file to
                       MyPath = "I:\MyPath"
 
                       ' Set the format of the saved file as a macro-enabled workbook
                       ActiveWorkbook.SaveAs Filename:=MyPath & "\" & Response, FileFormat:=52
                       Show_Box = False
                  Else
                  End If
             End If
 
        ' End the While loop.
        Wend
    Else
    Exit Sub
    End If
 
End Sub