Posts by Wozzie

    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
    1. Windows(“Testbuild.xlsm").Visible = False
    2. Application.ScreenUpdating = False
    3. Windows("Testbuild.xlsm").Visible = True
    4. 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
    1. Sub Workbook_Open()
    2. ActiveWindow.Visible = False
    3. Splashscreen.Show
    4. End Sub


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


    Code
    1. Private Sub CmdBtnStart_Click()
    2. Unload Splashscreen
    3. UF_CustForm.Show
    4. 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
    1. Sub ButtonSaveAs_Click()
    2. ActiveSheet.Copy
    3. ActiveWorkbook.SaveAs Filename:=Range("B2")
    4. 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!


    I'm having problems with your workbook. I think the issue with your code is that you have put the comboboxes in Frames.


    Does the userform and controls need to be so big?

    Roy, I did wonder if the form would be too big for my son's MacBook! But my problem is that he (the user) will need to select more than 15 items (from the same range) in some of the cost categories and I thought that using the frames was a neat way of putting 30 choices on the one form. The smaller categories will work fine with a single userform and the 'Nodupe' coding that you kindly provided. But it appears that I will not be able to use any form of dupe control across say 25 comboboxes unless I split a cost category range into two, and then have one userform for each half. In which case I will need many userforms by the time I receive all of his cost categories.


    Woz.

    You can use the function on any of the UserForms

    Yes Roy, the function is working on both UF_PlasterP1 and P2 UserForms, and my other UF's are picking it up ok too. My problem is that I'm using P2 as an extension of P1 to add more materials using the same range as P1. But ValueSelected function starts afresh on P2 , thus P2 user selections can be duplicated from P1. So my question is can the "already selected" message show if a P2 selection was selected on P1 (without having to rebuild using multipage controls)?


    I hope this better explains my problem.


    Thanks,

    Woz.

    Roy, I've amended my code to move the ValueSelected function to a public module and it's working on both P1 and P2. But I may have my wires crossed! Does this just tidy things up in terms of coding other forms, or can the ValueSelected carry over from P1 to P2? I'm still using the add-on form at this stage rather than Multipage.


    Cheers,

    Woz.

    Thanks Roy, I've applied your code across user forms PlasterCodesP1 and P2, and it works a treat. The P2 form is a continuation from P1 for instances when more than 15 of the PL code materials would be required. Is there any way that the P1 Function etc can carry over to P2 rather than it starting afresh in P2?