Userform Frame Captions

g-man97

New Member
Joined
Sep 30, 2004
Messages
24
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

CraigM

Active Member
Joined
Feb 27, 2003
Messages
320
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

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Could it be because Sub CommandButton3_Click() is Private? Don't really know, just throwing out a possibility. Hope that helps!
 

g-man97

New Member
Joined
Sep 30, 2004
Messages
24
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

Active Member
Joined
Feb 27, 2003
Messages
320
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

New Member
Joined
Sep 30, 2004
Messages
24
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
 

Forum statistics

Threads
1,148,525
Messages
5,747,188
Members
424,068
Latest member
Salim khamis

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