Userform Frame Captions

[g-man97]

This board has been such a great resource, I'd like to throw this out there for some experts to look at.

I have the following lines of code in the two different userform modules.

In ReportsMenu userform module:


Private Sub CommandButton3_Click()
'pass office & district names to new form
Off1Name = "ABERDEEN"
RDORpt2.Frame8.Caption = Off1Name
Off2Name = "Huron"
RDORpt2.Frame9.Caption = Off2Name
Off3Name = "Watertown"
RDORpt2.Frame10.Caption = Off3Name
RDORpt2.Label218.Caption = 1
RDORpt2.Label220.Caption = Off1Name
RDORpt2.Label224.Caption = "CURRENT ALIGNMENT"
'display the form
Load RDORpt2
RDORpt2.Show
End Sub


Public Sub CommandButton4_Click()
'pass office & district names to new form
Off1Name = "SIOUX FALLS"
RDORpt2.Frame8.Caption = Off1Name
Off2Name = "Mitchell"
RDORpt2.Frame9.Caption = Off2Name
Off3Name = "Yankton"
RDORpt2.Frame10.Caption = Off3Name
RDORpt2.Label218.Caption = 2
RDORpt2.Label220.Caption = Off1Name
RDORpt2.Label224.Caption = "CURRENT ALIGNMENT"
'display the form
Load RDORpt2
RDORpt2.Show
End Sub

Sub CommandButton7_Click()
'pass office & district names to new form
Off1Name = "RAPID CITY"
RDORpt2.Frame8.Caption = Off1Name
Off2Name = "Pierre"
RDORpt2.Frame9.Caption = Off2Name
Off3Name = "Sturgis"
RDORpt2.Frame10.Caption = Off3Name
RDORpt2.Label218.Caption = 3
RDORpt2.Label220.Caption = Off1Name
RDORpt2.Label224.Caption = "CURRENT ALIGNMENT"
'display form
Load RDORpt2
RDORpt2.Show
End Sub
In RDORpt2 userform module:

Private Sub UserForm_initialize()
'get office names from userform
Off1 = RDORpt2.Frame8.Caption 'THESE VALUES NEVER CHANGE
Off2 = RDORpt2.Frame9.Caption 'THESE VALUES NEVER CHANGE
Off3 = RDORpt2.Frame10.Caption 'THESE VALUES NEVER CHANGE
'Fill in office #1 (labels 16 To 29)
'loop until c=74 is col. AFTER last cnty in tbl
C = 8
L1 = 16
Do Until C = 74 Or L1 = 29
If Sheets("ScenariosTbl").Cells(3, C) = Off1 Then
RDORpt2.Controls("Label" & L1).Caption = _
Sheets("ScenariosTbl").Cells(2, C)
L1 = L1 + 1
End If
C = C + 1
Loop
'Fill in office #2 (labels 30-43)
C = 8
L1 = 30
Do Until C = 74 Or L1 = 43
If Sheets("ScenariosTbl").Cells(3, C) = Off2 Then
RDORpt2.Controls("Label" & L1).Caption = _
Sheets("ScenariosTbl").Cells(2, C)
L1 = L1 + 1
End If
C = C + 1
Loop
'Fill in office #3 (labels = 50 To 63)
C = 8
L1 = 50
Do Until C = 74 Or L1 = 63
If Sheets("ScenariosTbl").Cells(3, C) = Off3 Then
RDORpt2.Controls("Label" & L1).Caption = _
Sheets("ScenariosTbl").Cells(2, C)
L1 = L1 + 1
End If
C = C + 1
Loop
End Sub

The problem is that the values of Off1, Off2, Off3 NEVER CHANGE! They are always equal to the values ( Frame8.Caption, Frame9.Caption and Frame10.Caption) assigned in ComanndButton3_Click.

Curiously, when i click on CommandButton4_Click or CommandButton7_Click the correct values do update to the frames in RDORpt2 userform but I can't pick up these changes in my Userform_initialize sub in the RDORpt2 userfom module.

I'd appreciate some help on this since sitting here starring at my code hasn't worked too well. As always, thank you everyone for taking a look at this!

G-Man

 

[CraigM]

What are Off1Name and Off1? Are they text boxes? Should the Off1 refered to in the initialise event actually be Off1Name?

As an aside, it is a great idea to name your controls with a prefix such as txt for textboxes (ie txtOff1Name), cmd for command buttons etc. It makes it easier, not only for someone else to understand, but also for yourself. Just a suggestion. I'm sure there will be lots of info on such naming conventions out there.

 

[Tazguy37]

Could it be because Sub CommandButton3_Click() is Private? Don't really know, just throwing out a possibility. Hope that helps!

 

[g-man97]

Great suggestion Craig. Yes Off1 and Off1Name are text and are the same value. I tried just passing the value of Off1Name to the Userform_initialize sub but I couldn't get that to work so I thought I'd really came up with a bright idea using the value of the frame captions.

Taz, I tried making it just a sub and even a public sub, but neither worked. When I step through this code, starting in CommandButton4, as soon as it gets to the line RDORpt2.Frame8.Caption = Off1Name in ReportsMenu userform module (Module 1), execution switches to the Userform_initialize module in RDORpt2 userform (Module 2) and runs the entire initialize module before returning to the next line back in Module 1.

So now i know what is happening, just not why, or how to fix this! Ain't Excel wonderful!!!!


G-Man

 

[CraigM]

OK. I think I've got it.
Try changing the Initilaise event to an Activate Event (ie Private Sub UserForm_Activate()). This is because the initialise event fires when the form is first mentioned (I think), ie before you actually change the values of Off1 etc. So it is actually working, but at the time that the line Off1 = RDORpt2.Frame8.Caption runs, the line RDORpt2.Frame8.Caption = Off1Name has not, and RDORpt2.Frame8.Caption is therefore still as it was. Follow me?

 

[g-man97]

Taz, you are a genious! Changing to the Activate event did the trick. Thanks a million! And, I even was able to follow your thinking. Keep up the good work!

G-Man