The hunt for the disappearing textboxes.....

[anon]

Hi all.

I'm (foolishly) creating a multipage userform laid out like a
calendar, which each page being a month. Each day has 11 entries, and
each entry has 2 text boxes. So I have to have 22 text boxes per day,
which for the year adds up to over 8000 textboxes!

Each of these textboxes needs a controlsource set to a specific cell
on a sheet in the wb.

As I see it I have two choices;

Rename 8000 textboxes so that they are easily programmed (names such
as Time1, Time2, Entry1, Entry2) in a loop - then i could set the
controlsource programatically

Or leave the names (these are random in no order, so the first might
be TextBox367 and the next might be TextBox930) and manually set the
controlsource of 8000 boxes!

So - I thought i'd try another way. Start again and write code to add
the 600+ boxes required for one month, and whilst adding giving them
numerical names in order (Time1, Time2, Time3) etc.

However I cannot get this to work. My code;

For i = 1 To 11
nm = "Time" & i
Set mytextbox =
VBAProject.July08.MultiPage1.july.Controls.Add("Forms.TextBox.1")
With mytextbox
.Name = nm
.Left = 50
.TOP = 50
.Height = 12.75
.Width = 30
.Visible = True
End With
Next i

is in the userform_initialise.

The textboxes are created....however as soon as I close the userform
the textboxes disappear. I need them to be permanent. (I know I can
create them again next time I initialise the form however I want them
there all the time!).They do not exist when I view the form in design
mode.

Can anybody solve my disappearing textbox mystery please?

 

[Bernie Deitrick]

As I see it, your two choices aren't good choices - in fact, I would call them both horrible. A
much better choice is to use another control (the Microsoft date and time picker, which is a
calendar control) where you or your user can select the month, and the day. Then you use code like
this in the change event of that date control:

MyR = Math here to select the correct row from your datasheet based on the date selected
Userform1.TextBox1.Text = Worksheets("Database").Cells(myR, 1).Value
Userform1.TextBox2.Text = Worksheets("Database").Cells(myR, 2).Value
Userform1.TextBox3.Text = Worksheets("Database").Cells(myR, 3).Value
etc....

And then use the reverse to change the values if they are edited
Worksheets("Database").Cells(myR, 1).Value = Userform1.TextBox1.Text
etc.

HTH,
Bernie
MS Excel MVP