Posts by Wozzie

Rollis13 and Rory,

Thank you both for your help. My saving problem has been resolved and now works on both Mac and Windows. I have incorporated your suggested code as it's much tighter than my original, although the problem was actually a silly error on my part! I had included a number of "/" in the reference that was making up the filename, which works on a Mac but is not accepted in Windows!

Many Thanks,

Woz.

Quote from rory

Sure - like this:

Code

Sub SaveAs_New_XLSM_Workbook()
'Save as xlsm file using the reference cell value
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant


On Error GoTo errHandler
Set wbA = ActiveWorkbook
Set wsA = ActiveSheet


'Get active workbook folder
strPath = wbA.Path


If strPath = "" Then
strPath = Application.DefaultFilePath
End If


' make sure path ends with the appropriate separator
If Right$(strPath, 1) <> Application.PathSeparator Then strPath = strPath & Application.pathseparatorstrName = wsA.Range("B13").Value


'Create default name and save for file
strFile = strName & ".Model.xlsm"
strPathFile = strPath & strFile
ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled
'Confirmation message with file info
MsgBox "Hello Rob, " & cbCrLf _
& vbCrLf & vbCrLf & _
"This workbook has been saved as a customer model: " _
& vbCrLf & vbCrLf & _
strPathFile


ExitHandler:
Exit Sub


errHandler:
MsgBox "Could not save file"
Resume ExitHandler


End Sub

Display More

Thanks Rory, I tried your code and it kept failing on my Mac. When I stepped through it, it appeared to be failing on the "If Right$" line (I think from the Then part).

I've tried a few things today and got the following code to work on my Mac, using FileFormat:=53. I then changed this to FileFormat:=52 for the users windows pc but it still failed to save when he ran it! There is no doubt a simple fix to this but it is beyond my beginner knowledge!

Woz.

Sub SaveAs_New_XLSM_Workbook()

'Save as xlsm in same folder using the reference cell value

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet


'Get active workbook folder
strPath = wbA.Path
If strPath = "" Then
   strPath = Application.DefaultFilePath
End If

strPath = strPath & "\"
strName = wsA.Range("B13").Value
   
   
'Create file name and save
strFile = strName & "_Model"
strPathFile = strPath & strFile

'__________________________________________________________________

ActiveWorkbook.SaveAs filename:=strFile, FileFormat:=53

'Nb
'On a Mac: FileFormat:=53 = xlOpenXMLWorkbookMacroEnabled = xlsm
'In Windows: It's FileFormat:=52 as the number is +1 for a Mac!
'__________________________________________________________________

'Confirmation message with file info
MsgBox "Hello Rob, " & vbCrLf _
        & vbCrLf & vbCrLf & _
        "This workbook has been saved as a customer model: " _
        & vbCrLf & vbCrLf & _
        strPathFile


ExitHandler:
    Exit Sub

Quote from rory

Ideally you'd use If Right$(strPath, 1) <> application.pathseparator Then strPath = strPath & application.pathseparator so that it works on both systems.

I'd also suggest using the enum xlOpenXMLWorkbookMacroEnabled for the FileFormat parameter.

Rory, Thanks for this, I'm sure this will be the best fix for me, as my brother (the user) has a Windows pc but I'm creating the model on a Mac.

If you have the time, I would really appreciate it if you could please show me how your fixes fit into or replace the code in my original post.

Woz.

Quote from Wozzie

Hi, Thanks for your help.

Do you mean Fileformat:=52? I want the 'saved as' file to be a working customer xlsm not a template.

I know Option Explicit is best practise but would it make a difference here?

Woz.

Display More

Sorry, we crossed

Quote from rollis13

Hi to all.

Probably will also need to change this line:

Code

from:  
ActiveWorkbook.SaveAs Filename:=strFile 
to: 
ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=53

You also have a typo here:

Code

MsgBox "Hello Rob, " & cbCrLf _
should be:
MsgBox "Hello Rob, " & vbCrLf _

Why aren't you using Option Explicit ?

Display More

Hi, Thanks for your help.

Do you mean Fileformat:=52? I want the 'saved as' file to be a working customer xlsm not a template.

I know Option Explicit is best practise but would it make a difference here?

Woz.

Quote from Carim

Hello,

If I am not mistaken ... on a Mac you would need to use strPath = strPath & "/"

Hope this will help

Display More

Hi Carim,

It works fine on my mac. it's on the windows pc that it's not working!

Thanks for looking at this.

Hi,

I have created a small Excel pricing model, that includes the attached vba to force a workbook Save_As to protect the template model in it's original format. When input is made to a certain cell, a workbook change routine calls this module to force the save as to a renamed .xlsm workbook. This works fine on my mac but the save as fails on the user pc (Windows 10). Can the code be amended to solve this? Cell B13 contains the reference being used as part of the file name.

Thanks,

Woz.

Sub SaveAs_New_XLSM_Workbook()

'Save as xlsm file using the reference cell value

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet

'Get active workbook folder

strPath = wbA.Path
If strPath = "" Then
   strPath = Application.DefaultFilePath
End If

strPath = strPath & "\"
strName = wsA.Range("B13").Value

'Create default name and save for file

strFile = strName & ".Model.xlsm"
strPathFile = strPath & strFile

ActiveWorkbook.SaveAs filename:=strFile

'Confirmation message with file info

MsgBox "Hello Rob, " & cbCrLf _
        & vbCrLf & vbCrLf & _
        "This workbook has been saved as a customer model: " _
        & vbCrLf & vbCrLf & _
        strPathFile


ExitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not save file"
    Resume ExitHandler
    
End Sub

I've resolved this by making the workbook visible again but with screen updating =false. The workbook now stays hidden through all userforms until screenupdate switches back on at the output report.

Windows(“Testbuild.xlsm").Visible = False
Application.ScreenUpdating = False
Windows("Testbuild.xlsm").Visible = True
Splashscreen.Show

Woz.

Roy, copy attached (slimmed down to splashscreen and a couple of userforms). In setting up this example for you, I also noticed that since applying the ActiveWindow.visible code, I've lost the ability to edit any userform objects e.g. move or remove a textbox!

Thanks for looking at this for me.

Woz.

xlhidetest.xlsm

Error message:

Quote from royUK

What actually happens

Sorry Roy, I missed your question. The splashscreen opens with excel hidden but when I click the continue button. I get a Run-time error '1004' then when I click on debug it highlights the following line...

UF_CustForm.Show

Woz.

Quote from royUK

You should know by now to use Code Tags when posting your code to the Forum.

I apologise, I will re-submit.

Hi All,

I'm trying to keep an excel workbook hidden/invisible so that only my UserForms are visible as you work through them.

This code works ok to show my splashscreen with the workbook hidden...

Sub Workbook_Open()
ActiveWindow.Visible = False
Splashscreen.Show
End Sub

but falls over when opening the first UserForm via the splashscreen command button as highlighted...

Private Sub CmdBtnStart_Click()
Unload Splashscreen
UF_CustForm.Show
End Sub

Any help will be greatly appreciated as I've spent a couple of hours trying to solve this without success.

Thanks,

Woz

Using Excel 2011 for mac

I've used this Roy but it keeps falling over on...

1. GetDesktop = CreateObject("WScript.Shell").SpecialFolders("Desktop") & _
2. Application.PathSeparator

Is it a windows v mac thing?

Woz

Now solved. My Excel file location preference was set to Pictures folder!

Woz

I have an output worksheet populated via UserForms and I'm using this simple button vba to copy and save the worksheet. It works fine apart from the fact that it randomly selects my Pictures folder to save in. How can I amend the code to always save in Desktop.

Woz (Using Excel 2011 for mac)

Sub ButtonSaveAs_Click()
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=Range("B2")
End Sub

Yes it works fine. Thank you for all your help and patience, I have learnt so much from you over the last week.

Woz

Quote from royUK

I created a new UserForm to replace yours. The controls are housed in a MultiPage control and I've adjusted the code to work with that.

I have also added a Class Control to handle the ComboBox change event to save having to enter it for each combobox.

It seems to work OK, but you test it. I'll check back tomorrow.

Please note that I have made the UserForm a more manageable size, and I think using bright colours in a UserForm should really be avoided.

Display More

Many, many thanks Roy, I have your code working on my MultiPage form and it has given me exactly what I wanted to achieve. Can I ask why I need the On Error code in the NoDupe sub? I have to confess I'm not sure what the error handling is doing!

Woz.

Quote from royUK

I created a new UserForm to replace yours. The controls are housed in a MultiPage control and I've adjusted the code to work with that.

I have also added a Class Control to handle the ComboBox change event to save having to enter it for each combobox.

It seems to work OK, but you test it. I'll check back tomorrow.

Please note that I have made the UserForm a more manageable size, and I think using bright colours in a UserForm should really be avoided.

Display More

Wow, I see what you mean about those big letters! Something weird happened there as those letters don't show in my file as you can see in my screen shot above (posted seconds before I received this post from you).

Thanks for your new code which I will incorporate this evening and let you know how it goes.

Woz.

Quote from royUK

Your comboboxes are not in numeric order. as they are in the original workbook. I think being in frames as well is causing the error on loading. Does it load for you.

The form is also way too big unless the user has impaired vision. Ir's very difficult to work with. I have quite a large monitor and it only shows part on the screen, and is very difficult to work with in the VB Editor. Also, those huge letters obscure the controls.

I don't understand Roy, the file opens ok for me (original and the link above), the link shows how it looks on my 21.5" mac. And I when I built the framed userform I spent a long time ensuring comboboxes where in the correct order and the tags lined up with textbox numbers!