Duplicate UserForm
Solved
Mistral_13200
Posted messages
649
Status
Membre
-
Mistral_13200 Posted messages 649 Status Membre -
Mistral_13200 Posted messages 649 Status Membre -
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
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 réponses
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
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
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
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
Re:
This doesn't come from the USF but you've created 2 procedures
This doesn't come from the USF but you've created 2 procedures
Worksheet_SelectionChangeand 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
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?
Or is there another way to do it?
This is for my personal knowledge ...
Mistral
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