Posts by dleister

You are a genius!! Thanks again I was able to get it on our SharePoint site and I tested it and it worked like a charm, just as I was hoping. Okay hopefully you will not hear from me again on this form Thanks!

You're right the spreadsheet does not have any macros so i don't need the splash screen. I just need to remove the existing data in the form and still have the code you wrote in it so that I can place it on the sharepoint site for the staff to use as a template, I don't want anyone to save over this file. How can I do this since it saves it without the code?

Hi Roy,

Sorry to bother you again, but I want to remove the data I entered into the form and change the code from 52 to 51 and leave it as a macro-enabled document in order to save it on my SharePoint site; however I normally could do these changes and then do ctrl S to save and then just end the task for excel in order to get out and all my changes were done. However I cannot to that action with the new code, any suggestions?

Hi Roy it works!!!

Thank you so much for all your help and patience with me I am going to make it into a Macro-Enabled Template document and place it into SharePoint, finger crossed that it work when the user downloads it. I will let you know..................thanks again!!

Deborah

Hi Roy,

I entered data as the end-user would do and it did not give me the error this time, however it is not saving it as the auto-populated name.

Hi Roy,

I added the above code to a new module and entered data in the worksheet and when I tried to save I got this run-time error.

I am so sorry that this has taken so much of your time, but I really appreciate your help!

Deborah

Hi Roy,

I made the changes you noted above; however I am getting the VB compile error. I attached the error message.

Deborah

Hi Roy, I entered data in the blue fields to show you how the auto-populated field (W2) looks. When the document is saved it should save the file with the filename shown on W2.

Hi Roy, thank you again for staying with me on this issue. Cell W2 is created as the user enter information into the form (blue fields), and then it is to be saved where ever they choose. I think the file will need to be saved as a .xlsm since it will still contain macros, otherwise it may not open correctly, right?

That is strange that it is working for you and not when I tried it

Hi Danglor, I tried replacing my code with what you noted above however when I save the document it is not saving it as the auto-populated name "W2" (this is created as the user enters information in this form), it is saving it as PR Form.

Hi Roy, I entered the required data in order to obtain the auto populated filename and when saving it auto closed the file I was saving. When I went to see how it saved it was without an extension so I am not able to open the file in excel.

Yes, that is correct.

Thank you for your help on this issue. Just a little background details; this file is a template format which resides on our SharePoint site, when the user downloads the document it saves a .xlsx copy on their computer but when they save it should force the auto populated filename and the macro-enabled extension. Again thank you for your time!

I notice that sometimes when I am saving (save as) the file type shows macro-enabled however the filename is blank, this is when the saved file has no extension when saved. Other times the filename will show PR Template1 when saving, this is when the file saves correctly.

The way this works is that they can save it with the given name which was downloaded when they open the template "PR Template1"; or even if they change the filename it will override when saving to be the filename within cell W2.

Somehow the blank filename during saving is what is causing the problem.

Hi Roy,

Thank you so much for helping me, I took your suggestion and added the worksheet name; however, the result is still saving as a no file extension. Is there something else you see that I am doing wrong?

I have a macro enabled excel template, when the user opens a new document aspects of their data they entry create a filename in a designated cell. This code forces the excel document to be saved as a macro-enabled extension and no matter how they name it it will be named what is stated in the designated cell for the filename. 99% of the time it works great; however 1% of the time the file is saved without any extension and is unusable, it is not an excel document. Can someone review this code and offer any suggestions?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


Dim xFileName As String


If SaveAsUI <> False Then
Cancel = True
ThisFile = Range("W2").Value
xFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As xlsm file")
If xFileName <> "False" Then
Application.EnableEvents = False
ActiveWorkbook.SaveAs Filename:=ThisFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled


Application.EnableEvents = True
Else
MsgBox "Action Cancelled"
Cancel = True
Exit Sub
End If
End If
End Sub

My objective is to have a button on the form which:
unprotects the form if it is protected
activates the autofilter for all columns if it is not already active

if it is already unprotected activates the autofilter for all columns if it is not already active

This code unfortunately does not operate the autofilter correctly; if the autofilter is already on it removes it

Here is my code:

Sub FilterInv()
'
' FilterInv Macro for Invoice Log
'
With ActiveSheet




If ActiveSheet.ProtectContents = True Then
    ActiveSheet.Unprotect
    .AutoFilterMode = False




    .Range("A6").AutoFilter




Else
    .AutoFilterMode = False




    .Range("A6").AutoFilter
    
End If




End With
End Sub