Userforms - automating the naming of frame and page tab captions
 

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

Userforms - automating the naming of frame and page tab captions
 

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.

"Roger on Excel" wrote:

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

Userforms - automating the naming of frame and page tab captio
 

Thanks Luke - this works nicely

best regards,

Roger

"Luke" wrote:

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.

"Roger on Excel" wrote:

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

Userforms - automating the naming of frame and page tab captio
 

Thanks Luke,

This works very nicely

Roger

"Luke" wrote:

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.

"Roger on Excel" wrote:

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