Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify a form on the fly
I have a form with multiple texbox controls. I want the number of these
controls to change based on the number of worksheets in the workbook. What I have currently is a form with 36 textbox controls. If I only have 30 worksheets, then the last 6 are hidden. If, however, the user wants to add worksheets until there are greater than 36, then I have to manually edit the code and form so that there will be enough textboxes for the worksheets. So, is there a way to modify the form so that when a worksheet is added (or removed) a new textbox will be added (or removed) from the form? I should add there is small amount of code associated with each textbox_change event that would also need to be added or removed. .... The other option would be to redesign the form entirely, though I'm not quite sure how to make that happen. The workbook is a financial planner, with each sheet functioning as basically an account register. The form in question allows one to make a transfer of funds from one sheet (chosen by a combobox) to one or multiple other sheets (represented in the multiple textboxes). The essential function of the form is to allow multiple transactions at the press of one button, rather than making multiple individual transactions. Any ideas for either method? Thanks drhalter using XL 2003 SP3 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify a form on the fly
The Workbook_NewSheet event occurs when you add a new sheet to the
workbook. The event code would be placed in the ThisWorkbook module and you could use it to manipulate your userform when a new sheet is added. The Worksheet object is passed to the procedure, so you could test it like this: Private Sub Workbook_NewSheet(ByVal Sh As Object) MsgBox Sh.Name End Sub Remember: when you are coding outside of the userform module, and referencing controls on the userform, you need to fully qualify those references. So if your form was named frmMyForm, and the 1st textbox was named txtInputAmt, you would reference it in the NewSheet event like this: frmMyForm.txtInputAmt.Value = "Something here" instead of txtInputAmt.Value (if you were in the code module behind the userform) HTH, JP On Mar 20, 11:36*am, drhalter wrote: I have a form with multiple texbox controls. *I want the number of these controls to change based on the number of worksheets in the workbook. *What I have currently is a form with 36 textbox controls. *If I only have 30 worksheets, then the last 6 are hidden. *If, however, the user wants to add worksheets until there are greater than 36, then I have to manually edit the code and form so that there will be enough textboxes for the worksheets. So, is there a way to modify the form so that when a worksheet is added (or removed) a new textbox will be added (or removed) from the form? * I should add there is small amount of code associated with each textbox_change event that would also need to be added or removed. ... The other option would be to redesign the form entirely, though I'm not quite sure how to make that happen. *The workbook is a financial planner, with each sheet functioning as basically an account register. *The form in question allows one to make a transfer of funds from one sheet (chosen by a combobox) to one or multiple other sheets (represented in the multiple textboxes). *The essential function of the form is to allow multiple transactions at the press of one button, rather than making multiple individual transactions. Any ideas for either method? Thanks drhalter using XL 2003 SP3 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify a form on the fly
I would use a multi-select listbox for the sheet names. This is populated
dynamically as the form is loaded, always has enough rows for the number of sheets, and no empty rows. Also it takes a fixed space, and scrolls if the list is long. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "drhalter" wrote in message ... I have a form with multiple texbox controls. I want the number of these controls to change based on the number of worksheets in the workbook. What I have currently is a form with 36 textbox controls. If I only have 30 worksheets, then the last 6 are hidden. If, however, the user wants to add worksheets until there are greater than 36, then I have to manually edit the code and form so that there will be enough textboxes for the worksheets. So, is there a way to modify the form so that when a worksheet is added (or removed) a new textbox will be added (or removed) from the form? I should add there is small amount of code associated with each textbox_change event that would also need to be added or removed. ... The other option would be to redesign the form entirely, though I'm not quite sure how to make that happen. The workbook is a financial planner, with each sheet functioning as basically an account register. The form in question allows one to make a transfer of funds from one sheet (chosen by a combobox) to one or multiple other sheets (represented in the multiple textboxes). The essential function of the form is to allow multiple transactions at the press of one button, rather than making multiple individual transactions. Any ideas for either method? Thanks drhalter using XL 2003 SP3 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify a form on the fly
Thanks Jon.
I had thought of a multi-select form. The only difficulty I see is how to display and make changes to each of the amounts designated to go to each sheet. The transaction amount to each individual sheet varies. Could I use a two column listbox to display each amount? Could the two-column listbox be used to change the amount (i.e. can I place an user editable textbox in the second column of the listbox), or would I have to use a textbox on the side to update the amount if it needs to be changed? How does one use the listbox1.additem command to fill the second column of a listbox? Thanks drhalter "Jon Peltier" wrote: I would use a multi-select listbox for the sheet names. This is populated dynamically as the form is loaded, always has enough rows for the number of sheets, and no empty rows. Also it takes a fixed space, and scrolls if the list is long. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "drhalter" wrote in message ... I have a form with multiple texbox controls. I want the number of these controls to change based on the number of worksheets in the workbook. What I have currently is a form with 36 textbox controls. If I only have 30 worksheets, then the last 6 are hidden. If, however, the user wants to add worksheets until there are greater than 36, then I have to manually edit the code and form so that there will be enough textboxes for the worksheets. So, is there a way to modify the form so that when a worksheet is added (or removed) a new textbox will be added (or removed) from the form? I should add there is small amount of code associated with each textbox_change event that would also need to be added or removed. ... The other option would be to redesign the form entirely, though I'm not quite sure how to make that happen. The workbook is a financial planner, with each sheet functioning as basically an account register. The form in question allows one to make a transfer of funds from one sheet (chosen by a combobox) to one or multiple other sheets (represented in the multiple textboxes). The essential function of the form is to allow multiple transactions at the press of one button, rather than making multiple individual transactions. Any ideas for either method? Thanks drhalter using XL 2003 SP3 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify a form on the fly
I once played with a technique that simulated an editable second column by
overlaying a textbox on the listbox. It failed spectacularly. Now I keep it simple stupid with a textbox alongside the listbox. It's a little kludgey, because I hadn't envisioned this requirement in your description. ListBox1.AddItem adds a new row to the listbox. To read or write an element in the listbox, use ListBox1.List(i,j), where i and j are the row and column indices of the listbox (starting with the first row or column at index 0). I use this frequently with hidden columns to store additional information, using the listbox as a multidimiensional array. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "drhalter" wrote in message ... Thanks Jon. I had thought of a multi-select form. The only difficulty I see is how to display and make changes to each of the amounts designated to go to each sheet. The transaction amount to each individual sheet varies. Could I use a two column listbox to display each amount? Could the two-column listbox be used to change the amount (i.e. can I place an user editable textbox in the second column of the listbox), or would I have to use a textbox on the side to update the amount if it needs to be changed? How does one use the listbox1.additem command to fill the second column of a listbox? Thanks drhalter "Jon Peltier" wrote: I would use a multi-select listbox for the sheet names. This is populated dynamically as the form is loaded, always has enough rows for the number of sheets, and no empty rows. Also it takes a fixed space, and scrolls if the list is long. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "drhalter" wrote in message ... I have a form with multiple texbox controls. I want the number of these controls to change based on the number of worksheets in the workbook. What I have currently is a form with 36 textbox controls. If I only have 30 worksheets, then the last 6 are hidden. If, however, the user wants to add worksheets until there are greater than 36, then I have to manually edit the code and form so that there will be enough textboxes for the worksheets. So, is there a way to modify the form so that when a worksheet is added (or removed) a new textbox will be added (or removed) from the form? I should add there is small amount of code associated with each textbox_change event that would also need to be added or removed. ... The other option would be to redesign the form entirely, though I'm not quite sure how to make that happen. The workbook is a financial planner, with each sheet functioning as basically an account register. The form in question allows one to make a transfer of funds from one sheet (chosen by a combobox) to one or multiple other sheets (represented in the multiple textboxes). The essential function of the form is to allow multiple transactions at the press of one button, rather than making multiple individual transactions. Any ideas for either method? Thanks drhalter using XL 2003 SP3 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modify Automatic Form | Excel Discussion (Misc queries) | |||
User Form to Modify Row | Excel Programming | |||
Replace/modify standard data form? | Excel Discussion (Misc queries) | |||
Using a form to modify a list | Excel Programming | |||
Can I create a FORM (that I can modify etc) from an existing spreadsheet? | Excel Programming |