View original (French)

Duplicate UserForm

Solved
Mistral_13200 Posted messages 649 Status Member -  
Mistral_13200 Posted messages 649 Status Member -
Hello everyone,
Before presenting my problem, I would like to extend my best wishes to the entire CCM community.
I have been asked to create a workbook to manage the accounting of an association. There will be a sheet for each month, plus a summary sheet by category and a sheet for the annual balance.
For each sheet, there will be two UserForms, one for income and another for expenses.
I will be using fairly complex UserForms for entering the various elements. Each UserForm will be called by simply clicking on a cell in a column (income or expense). I have created a test workbook that you can download from this link:
http://www.cjoint.com/c/GAdi13CwEYg
To avoid spending too much time recreating them, I would like to know if it is possible to duplicate a UserForm:
- On the same sheet
- On different sheets
If so, how do we do that?
Thank you in advance for your help.
Best regards.
Mistral

3 answers

  1.
    gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    Hello Mistral_13200 and happy new year to you,

    A fairly simple method:
    - in the same session, open another blank workbook.
    - select your form sheet and drag it into the new one.
    - rename it for the new form
    - select this userform and drag it into your workbook
    You have your 2 forms in your workbook and you can discard the blank workbook.

    Have a good day.

    PS:
    There will be one sheet per month,
    If I may, I'm not sure that this is the wisest formula.
    If you put all your entries on the same sheet, it will be much simpler and more efficient to create a (or more) pivot table on the "summary sheet by category and one sheet for the annual report".
    Always stay calm
    Perfection is attained, not when there is nothing more to add, but when there is nothing more to take away. Antoine de Saint-Exupéry
    3
  2. Mistral_13200 Posted messages 649 Status Member 4
     
    Hello Gbinform,

    Thank you for your response.
    I tried to copy my sheet and USF into another workbook and it works.

    I managed to duplicate the USF in the same workbook, I modified the cells to monitor and now I have an error message:
    Ambiguous name detected: Worksheet_SelectionChange

    I don't understand why, I must say it's the first time I've used USF.

    Can you help me clarify this?

    As for having only one sheet, I will ask the C.A.
    Best regards,
    Mistral
    0
    1. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
       
      Re:

      This doesn't come from the USF but you've created 2 procedures 
      Worksheet_SelectionChange
      and you need to include the code of the second one in the first. 
      Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("G3:G20")) Is Nothing Then Recette.Show ElseIf Not Intersect(Target, Range("F3:F20")) Is Nothing Then Répense.Show End If End Sub
      0
      1. Mistral_13200 Posted messages 649 Status Member 4 > gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention  
         
        Re,

        It works perfectly. Thank you.

        One question though, if I wanted to monitor more than two columns, with as many user forms as there are columns, can I write what is below? 

        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Not Intersect(Target, Range("G3:G20")) Is Nothing Then
         Recette.Show
        ElseIf Not Intersect(Target, Range("F3:F20")) Is Nothing Then
         Dépense.Show
        End If

        If Not Intersect(Target, Range("E3:E20")) Is Nothing Then
         Mode.Show
        ElseIf Not Intersect(Target, Range("D3:D20")) Is Nothing Then
         Ribrique.Show
        End If

        End Sub


        Or is there another way to do it?

        This is for my personal knowledge ...
        Mistral
        0
    2. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
       
      Re
      It works but why not continue?
      Instead of 
      End If If Not Intersect(Target, Range("E3:E20")) Is Nothing Then Mode.Show

      You put: 
      ElseIf Not Intersect(Target, Range("E3:E20")) Is Nothing Then Mode.Show
      0
  3. Mistral_13200 Posted messages 649 Status Member 4
     
    Hello Gbinforme,

    After testing my proposal, and after modification, I arrived at yours which works perfectly.

    I will now be able to move forward with my project. I will mark this section as resolved, but I will probably get back to you later.

    Thanks again to you.

    Best regards.
    Mistral
    0