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. :)

    Sure - like this:


    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.

    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.

    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.

    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.


    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.

    Code
    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

    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...


    Code
    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...


    Code
    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 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)

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

    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.

    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.

    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!