I have a problem, I am trying to convert one of my first programs to
be more stable. In doing so, I found that one of my form modules is
upwards of 550kb in size and I know that it is causing a lot of my
instability. I have offloaded all private subs to another module to
bring the size down, but I am faced with a number of similar
repetitive textbox_change subs.

The program is for a budget program for a software testing group, so I
have a ton of these textboxes with names like:
txtAlphaMGRHours
txtAlphaSrLeadHours
txtBetaSrTesterDays
txt1PartyTempTesterPeople

and so on...

Each name has 4 parts, for example 'txtAlphaMGRHours' has these parts:
1. 'txt', just a prefix
2. 'Alpha', the phase of the project
3. 'MGR', the row we are calculating
4. 'Hours', the column we are calculating

I have posted a link to the UI here for your reference:
http://drop.wowtcgdeckbuilder.com/budget.png

Here is the change code for the 'txtAlphaMGRHours' text box.
=============================================
Private Sub txtAlphaMGRHours_Change()
'runs when the box changes

If boolLoadAlpha = True Then Exit Sub
If InChange = True Then Exit Sub

InChange = True

'look for a trailing '.' or ','
Dim RightChar As String
RightChar = Right(Me.txtAlphaMGRHours.Value, 1)

If RightChar = "." Or RightChar = "," Then
Me.txtAlphaMGRHours.Value = Left(Me.txtAlphaMGRHours.Value, Len
(Me.txtAlphaMGRHours.Value) - 1)
End If

'check for a valid entry
Me.txtAlphaMGRHours.Value = ValidateChangeX
(Me.txtAlphaMGRHours.Value)

'assign the value to the Total Hours box
dblAlphaMGRTotalHours = Me.txtAlphaMGRPeople.Value * _
Me.txtAlphaMGRHours.Value * _
Me.txtAlphaMGRDays.Value

'get the total dollars for the phase
dblAlphaMGRTotalDollars = GetAlphaTotalDollars(1,
Me.txtAlphaMGRPeople.Value, _

Me.txtAlphaMGRHours.Value, _

Me.txtAlphaMGRDays.Value, _
dblMGRRate)

'assign the variable a value
Me.txtAlphaMGRTotalHours.Value = dblAlphaMGRTotalHours

'assign the variable a value
Me.txtAlphaMGRTotalDollars.Value = FormatCurrency
(dblAlphaMGRTotalDollars)

'assisgn the subphase hours
dblAlpha1Hours = dblAlphaMGRTotalHours + dblAlphaSrLeadTotalHours
+ _
dblAlphaProjectLeadTotalHours +
dblAlphaSrTesterTotalHours + _
dblAlphaTempTesterTotalHours

'Assign the SubPhase Dollars
dblAlpha1Dollars = dblAlphaMGRTotalDollars +
dblAlphaSrLeadTotalDollars + _
dblAlphaProjectLeadTotalDollars +
dblAlphaSrTesterTotalDollars + _
dblAlphaTempTesterTotalDollars

'call the subs that calc the Total Dollars and hours
AlphaTotalDollars
AlphaTotalHours

If RightChar = "." Or RightChar = "," Then
Me.txtAlphaMGRHours.Value = Me.txtAlphaMGRHours.Value &
RightChar
End If

InChange = False

End Sub
=============================================

First mistake I made the when I made this was not knowing how to use a
tab strip which would cut this down by 80%, but I don't have the time
to make a transition like that.

I was thinking about doing something like this...
==============================================
Public Sub HoursTestSub(ObjHours As MSForms.TextBox, _
ObjPeople As MSForms.TextBox, _
ObjDays As MSForms.TextBox)

'look for a trailing '.' or ','
Dim RightChar As String
RightChar = Right(ObjHours.Value, 1)

If RightChar = "." Or RightChar = "," Then
ObjHours.Value = Left(ObjHours.Value, Len(ObjHours.Value) - 1)
End If

'check for a valid entry
ObjHours.Value = ValidateChangeX(ObjHours.Value)
==============================================

But get stuck when i need to assign phase, position, type variables
like 'dblAlphaMGRTotalHours'

Any help here would be great, I may have to switch to tabstrip I
really want to get this down. FYI, the program runs with a form
module size of 590kb, but it requires a save before all the code is
run through.

You can call Control object dynamically like below. this may be able to
reduce the amount of your code.


boxes = Array("Textbox1", "Textbox2", "Textbox3")
For Each box In boxes
Set mybox = ActiveSheet.OLEObjects(box)
mybox.Object.Text = "Joel"
Next box


"theSquirrel" wrote:

I have a problem, I am trying to convert one of my first programs to
be more stable. In doing so, I found that one of my form modules is
upwards of 550kb in size and I know that it is causing a lot of my
instability. I have offloaded all private subs to another module to
bring the size down, but I am faced with a number of similar
repetitive textbox_change subs.

The program is for a budget program for a software testing group, so I
have a ton of these textboxes with names like:
txtAlphaMGRHours
txtAlphaSrLeadHours
txtBetaSrTesterDays
txt1PartyTempTesterPeople

and so on...

Each name has 4 parts, for example 'txtAlphaMGRHours' has these parts:
1. 'txt', just a prefix
2. 'Alpha', the phase of the project
3. 'MGR', the row we are calculating
4. 'Hours', the column we are calculating

I have posted a link to the UI here for your reference:
http://drop.wowtcgdeckbuilder.com/budget.png

Here is the change code for the 'txtAlphaMGRHours' text box.
=============================================
Private Sub txtAlphaMGRHours_Change()
'runs when the box changes

If boolLoadAlpha = True Then Exit Sub
If InChange = True Then Exit Sub

InChange = True

'look for a trailing '.' or ','
Dim RightChar As String
RightChar = Right(Me.txtAlphaMGRHours.Value, 1)

If RightChar = "." Or RightChar = "," Then
Me.txtAlphaMGRHours.Value = Left(Me.txtAlphaMGRHours.Value, Len
(Me.txtAlphaMGRHours.Value) - 1)
End If

'check for a valid entry
Me.txtAlphaMGRHours.Value = ValidateChangeX
(Me.txtAlphaMGRHours.Value)

'assign the value to the Total Hours box
dblAlphaMGRTotalHours = Me.txtAlphaMGRPeople.Value * _
Me.txtAlphaMGRHours.Value * _
Me.txtAlphaMGRDays.Value

'get the total dollars for the phase
dblAlphaMGRTotalDollars = GetAlphaTotalDollars(1,
Me.txtAlphaMGRPeople.Value, _

Me.txtAlphaMGRHours.Value, _

Me.txtAlphaMGRDays.Value, _
dblMGRRate)

'assign the variable a value
Me.txtAlphaMGRTotalHours.Value = dblAlphaMGRTotalHours

'assign the variable a value
Me.txtAlphaMGRTotalDollars.Value = FormatCurrency
(dblAlphaMGRTotalDollars)

'assisgn the subphase hours
dblAlpha1Hours = dblAlphaMGRTotalHours + dblAlphaSrLeadTotalHours
+ _
dblAlphaProjectLeadTotalHours +
dblAlphaSrTesterTotalHours + _
dblAlphaTempTesterTotalHours

'Assign the SubPhase Dollars
dblAlpha1Dollars = dblAlphaMGRTotalDollars +
dblAlphaSrLeadTotalDollars + _
dblAlphaProjectLeadTotalDollars +
dblAlphaSrTesterTotalDollars + _
dblAlphaTempTesterTotalDollars

'call the subs that calc the Total Dollars and hours
AlphaTotalDollars
AlphaTotalHours

If RightChar = "." Or RightChar = "," Then
Me.txtAlphaMGRHours.Value = Me.txtAlphaMGRHours.Value &
RightChar
End If

InChange = False

End Sub
=============================================

First mistake I made the when I made this was not knowing how to use a
tab strip which would cut this down by 80%, but I don't have the time
to make a transition like that.

I was thinking about doing something like this...
==============================================
Public Sub HoursTestSub(ObjHours As MSForms.TextBox, _
ObjPeople As MSForms.TextBox, _
ObjDays As MSForms.TextBox)

'look for a trailing '.' or ','
Dim RightChar As String
RightChar = Right(ObjHours.Value, 1)

If RightChar = "." Or RightChar = "," Then
ObjHours.Value = Left(ObjHours.Value, Len(ObjHours.Value) - 1)
End If

'check for a valid entry
ObjHours.Value = ValidateChangeX(ObjHours.Value)
==============================================

But get stuck when i need to assign phase, position, type variables
like 'dblAlphaMGRTotalHours'

Any help here would be great, I may have to switch to tabstrip I
really want to get this down. FYI, the program runs with a form
module size of 590kb, but it requires a save before all the code is
run through.