rename label captions

[jinx_uk_98]

Hi all,

Bit of a newbie here so please be gentle.

I have 200 cells that require data to be entered into them but I want
to do this via a userform.

The 200 cells can be divided in 4 major headings with 25 subheadings
(cells) in each section. (these can be increased or decreased over
time)

Rather than have 200 textboxes on my userform I wanted to list the 4
major heading in a listbox then when a major heading is clicked the
captions and visablity of the 25 labels and textboxes would be set.


Is this possible?


Regards
Kevin

 

[Tom Ogilvy]

Yes,

You should put the information in a table or something you can read from a
sheet.

then just loop through the appropriate range of cells per the heading
selected and update your label captions.

writing the information back would utilize offsets from a base range which
is selected based on teh header selected.

The complexity of growing cells could be communicated by the size of the
area containing the labels and then you could have the maximium number of
textboxes and labels on the userform and hide the ones not needed (visible =
False).

 

[jinx_uk_98]

thanks Tom,

So I have now created a table on sheet1, from range A11 are the
headings that will appear in the listbox on the userform. in range
a2::d10 I have the captions that I want to appear on as lables on
userform1.

How do I utilize offsets to to lookup the range and rename the captions
of the labels?

greatful for any advice

Kevin

 

[jinx_uk_98]

Hi all,

anyoneelse able to offer me some advice on this please?

best regards

Kevin

 

[Tom Ogilvy]

for i = 1 to 4
Userform1.Controls("Label" & i).Caption = _
Worksheets("Sheet1").Cells(rw,i).Value
Next

Set rw to the row to look in.