Dynamically change Frame Caption

[taylorpa5]

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:

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

Laugh at me later, I'm just learning!

Thanks,

pt

 

[iggydarsa]

go to VB Editor
Double Click on the worksheet that is named CONTROLS
on the main pane paste this 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]

Oops, I think I misunderstood your question.
Try this code instead:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    UserForm1.Frame1.Caption = Selection.Value
End Sub

 

[taylorpa5]

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]

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]

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]

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:

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]

Yeah Baby! I had a worksheet change event in the worksheet module for command. I just included:

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

within the subprocedure.

Thanks a million!

pt

 

[iggydarsa]

Glad to hear it worked