Userforms - automating the naming of frame and page tab captions

  • Thread starter Thread starter Roger on Excel
  • Start date Start date
R

Roger on Excel

I have a spreadsheet run with userforms.

Is there a way to make frame captions and page tab captions read from
specific cells in the spreadsheet for their names shown in the userform?

I want to have a separate form in which a user can customize frame captions
and page tab captions, without having to change the properties of the
frames/page tabs manually (since I have the vba code password protected)

Ideally it would work like this :

I would have three textboxes on a userform called frmCustomize

textbox1
textbox2
textbox3

These would read the default frame names from cells Sheet1!A1 through C1.
This way the user can enter whatever they like for the frame names.

The code would then change the frame1 2 and 3 captions to equal the textbox
entries.

Can this also work for page tab captions in a similar fashion?

Can anyone help?

Thanks,
Roger
 
This might work:

Private Sub UserForm_Activate()

With frmCustomize
.TextBox1.Value = Worksheets("Sheet1").Range("A1")
.TextBox2.Value = Worksheets("Sheet1").Range("B1")
.TextBox3.Value = Worksheets("Sheet1").Range("C1")
.Frame1.Caption = .TextBox1.Value
.Frame2.Caption = .TextBox2.Value
.Frame3.Caption = .TextBox3.Value
End With

Just to let you know, I haven't tested the above. Assuming it works, you
could shorten it by getting rid of the ".TextBox1.Value = " portions and just
using ".Frame1.Caption = Worksheets("Sheet1").Range("A1")" and so on.

Hope it works.
 
Thanks Luke,

This works very nicely

Roger

Luke said:
This might work:

Private Sub UserForm_Activate()

With frmCustomize
.TextBox1.Value = Worksheets("Sheet1").Range("A1")
.TextBox2.Value = Worksheets("Sheet1").Range("B1")
.TextBox3.Value = Worksheets("Sheet1").Range("C1")
.Frame1.Caption = .TextBox1.Value
.Frame2.Caption = .TextBox2.Value
.Frame3.Caption = .TextBox3.Value
End With

Just to let you know, I haven't tested the above. Assuming it works, you
could shorten it by getting rid of the ".TextBox1.Value = " portions and just
using ".Frame1.Caption = Worksheets("Sheet1").Range("A1")" and so on.

Hope it works.
Click to expand...
 
Back
Top