modify a form on the fly

[drhalter]

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

 

[JP]

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

 

[Jon Peltier]

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

 

[drhalter]

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]

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