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