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

 

[Guest]

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

 

[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.)