Trying to eliminate multiple copies of the SAME code within a UserForm

[JimP]

To All,
The snippet of code below (labeled "Start of Common Code") is
duplicated numerous time's in a number of "Events" associated with a
UserForm. All is currently working, but I'm trying now to clean up
duplicate code by placing the common portions in a subroutine ...
Could someone Kindly show me how to correctly reference back to the
UserForm once this common code is extracted from an event and placed
into a sub-routine? I thought I read an earlier post about "MSForms."
? ... or something like that, but Listbox doesn't seem to be a valid
option following MSForms. ... Any assistance would be greatly
appreciated.

Thanks,
JimP

'''''''''''''snippet''''''''''
With Me.ListBoxITEM
.Clear
.ColumnWidths = "50"
.ColumnHeads = True
.RowSource = gstrRowSource
.ListIndex = 0
'------ Start of Common Code -------------
' 1st) Extract FROM: Database TO: Label's Caption
LblDescription.Caption = Range(gstrRowSource).Cells(.ListIndex + 1,
2).Value
LblUnitPRICE.Caption = Range(gstrRowSource).Cells(.ListIndex + 2,
3).Value
' 2nd) Copy FROM: Label's Caption TO: Custom TextBox
TxtCustomDescription.Text = LblDescription.Caption
TxtCustomPrice.Text = LblUnitPRICE.Caption
' 3rd) Copy FROM: Custom Price TextBox TO: Global Variable
gstrDescription = TxtCustomDescription.Text
gstrUnitPrice = TxtCustomPrice.Text
'-------- End of Common Code -------------
End With

 

[Tom Ogilvy]

Just put the subroutine in the code module for the Userform. If it works
now, it should work there also.

 

[Jim Rech]

I think you want something like this:

With ListBoxITEM 'Me isn't necessary fwiw
.Clear
.ColumnWidths = "50"
.ColumnHeads = True
.RowSource = gstrRowSource
.ListIndex = 0
'------ Start of Common Code -------------
CommonCode ListBoxITEM
'-------- End of Common Code -------------
End With

Sub CommonCode(LB As MSForms.ListBox)
With LB
LblDescription.Caption = Range(gstrRowSource).Cells(.ListIndex + 1,
2).Value
LblUnitPRICE.Caption = Range(gstrRowSource).Cells(.ListIndex + 2,
3).Value
TxtCustomDescription.Text = LblDescription.Caption
TxtCustomPrice.Text = LblUnitPRICE.Caption
gstrDescription = TxtCustomDescription.Text
gstrUnitPrice = TxtCustomPrice.Text
End With
End Sub


--
Jim Rech
Excel MVP
| To All,
| The snippet of code below (labeled "Start of Common Code") is
| duplicated numerous time's in a number of "Events" associated with a
| UserForm. All is currently working, but I'm trying now to clean up
| duplicate code by placing the common portions in a subroutine ...
| Could someone Kindly show me how to correctly reference back to the
| UserForm once this common code is extracted from an event and placed
| into a sub-routine? I thought I read an earlier post about "MSForms."
| ? ... or something like that, but Listbox doesn't seem to be a valid
| option following MSForms. ... Any assistance would be greatly
| appreciated.
|
| Thanks,
| JimP
|
| '''''''''''''snippet''''''''''
| With Me.ListBoxITEM
| .Clear
| .ColumnWidths = "50"
| .ColumnHeads = True
| .RowSource = gstrRowSource
| .ListIndex = 0
| '------ Start of Common Code -------------
| ' 1st) Extract FROM: Database TO: Label's Caption
| LblDescription.Caption = Range(gstrRowSource).Cells(.ListIndex + 1,
| 2).Value
| LblUnitPRICE.Caption = Range(gstrRowSource).Cells(.ListIndex + 2,
| 3).Value
| ' 2nd) Copy FROM: Label's Caption TO: Custom TextBox
| TxtCustomDescription.Text = LblDescription.Caption
| TxtCustomPrice.Text = LblUnitPRICE.Caption
| ' 3rd) Copy FROM: Custom Price TextBox TO: Global Variable
| gstrDescription = TxtCustomDescription.Text
| gstrUnitPrice = TxtCustomPrice.Text
| '-------- End of Common Code -------------
| End With

 

[Shailesh Shah]

Move your common code to a different sub & call it wheneve requried from your
userform.


Sub CommonCode()
LblDescription.Caption = Range(gstrRowSource).Cells(.ListIndex + 1, 2).Value
LblUnitPRICE.Caption = Range(gstrRowSource).Cells(.ListIndex + 2, 3).Value
' 2nd) Copy FROM: Label's Caption TO: Custom TextBox
TxtCustomDescription.Text = LblDescription.Caption
TxtCustomPrice.Text = LblUnitPRICE.Caption
' 3rd) Copy FROM: Custom Price TextBox TO: Global Variable
gstrDescription = TxtCustomDescription.Text
gstrUnitPrice = TxtCustomPrice.Text
End Sub





With Me.ListBoxITEM
.Clear
.ColumnWidths = "50"
.ColumnHeads = True
.RowSource = gstrRowSource
.ListIndex = 0

call commoncode ' call your common code



Regrads,
Shailesh Shah

 

[JimP]

Tom,

Thanks for Responding ... I did as you indicated ...

I was operating under the belief that the Code modules associated with
Forms were to consist of only EVENT Subroutines ? Is it an acceptable
practice, as in my example, to group common code related to EVENT's and
place in a subroutine within the UserForms code - even though no EVENT
would ever call the procedure/Sub?

No doubt, it's a lot more logical to leave the subroutine with the
UserForm ...

 

[JimP]

Jim,

Thanks for replying ...

I appreciate the comment on the use of 'me' ... It was 'littered' over
many of my form's ... went back and cleaned them all up ...

Additionally, thanks for showing me the format to pass a Forms name
into a subroutine ... (LB As MSForms.ListBox) ... Your example using my
code made the point very clear ... Thanks

JimP

 

[Dave Peterson]

The original posts in this thread have aged off for me.

But I sometimes put functions and subs in the userform code module when I know
that they'll never be used by anything else.

(but I may be missing the point of your followup--since the thread is gone.)