Dynamically change Frame Caption

taylorpa5

New Member
Joined
May 9, 2015
Messages
21
Hello Community:

Trying to figure out how to have a Frame caption Change has the cell value in A10 changes. Right now I have a form that opens up on the initialize event and populates the frame caption with the cell value in A10.

The frame opens up with the Modal Property set to "false" so the user can make a different selection in the workbook and see the values change on the form as they click different cells (the frame caption being one of the values that needs to change, but it remains static at the value the form was initialized on.) Simple code below:

Code:
Private Sub UserForm_Initialize()
    Frame1.Caption = Worksheets("CONTROLS").Range("A10").Text
End Sub

Laugh at me later, I'm just learning! :cool:

Thanks,

pt
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
go to VB Editor
Double Click on the worksheet that is named CONTROLS
on the main pane paste this code:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Target.Row = 10 Then
    On Error Resume Next
        UserForm1.Frame1.Caption = Worksheets("CONTROLS").Range("A10").Text
    End If
End Sub
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
Oops, I think I misunderstood your question.
Try this code instead:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    UserForm1.Frame1.Caption = Selection.Value
End Sub
 

taylorpa5

New Member
Joined
May 9, 2015
Messages
21
Thank you for the reply. I tried the code in both the CONTROLS sheet module and the FORM module itself. I renamed UserForm1 to the actual form name of frmEEReportOrders with no luck. I'm continuing to play around with this. I hope to hear from you again.

Best.
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647

ADVERTISEMENT

ignore the first one...
you changed the userform name but what about frame name, is it something other than Frame1? If so, you will need to change that too.
Also remember to put the code where I described and not in a module or a backend of the form. Just double click on the sheet name on VB Editor and paste it there.
 

taylorpa5

New Member
Joined
May 9, 2015
Messages
21
Thank you. Frame1 is named Frame1. The logic goes like this:

1. When a cell on the active worksheet named ("command") has been selected, the end user can then call the user form from a cmdbutton to view the details associated with that cell selection.
2. The form uses the code I referenced at the beginning of this thread to pull the value from cell A10 on another sheet named ("controls") into the frame caption to represent the category name of the item they clicked.
3. With the form still open (Show Modal = False) the user has the option to make a different cell selection on the ("command") worksheet with the form still open and have the new cell selection information displayed into the form. The only thing not changing while the form is open is the frame caption for the new cell selection The data in the list box for the new cell selection changes, but the caption does not (it still shows the first cell selection when the form was initialized).

If this is going beyond your scope of free help, I'll completely understand and thank you for your time.

If not, I'll check back later. I'll get this figured out sure enough!

Best.
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647

ADVERTISEMENT

ok then maybe try this and let me know if it works

In VB Editor double click on the worksheet that has "Command" and paste this code:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    frmEEReportOrders.Frame1.Caption = Worksheets("CONTROLS").Range("A10").Value
End Sub

I'm assuming when the user selects a cell in "Command", appropriate text will appear in A10 of Control sheet.
 

taylorpa5

New Member
Joined
May 9, 2015
Messages
21
Yeah Baby! I had a worksheet change event in the worksheet module for command. I just included:

Code:
frmEEReportOrders.Frame1.Caption = Worksheets("CONTROLS").Range("A10").Value

within the subprocedure.

Thanks a million!

pt
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,328
Members
414,444
Latest member
lionking15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top