Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to eliminate multiple copies of the SAME code within a UserForm
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to eliminate multiple copies of the SAME code within a UserForm
Just put the subroutine in the code module for the Userform. If it works
now, it should work there also. -- Regards, Tom Ogilvy "JimP" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to eliminate multiple copies of the SAME code within a UserForm
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 "JimP" wrote in message om... | 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to eliminate multiple copies of the SAME code within a User
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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to eliminate multiple copies of the SAME code within a UserForm
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 ... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to eliminate multiple copies of the SAME code within a UserForm
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to eliminate multiple copies of the SAME code within aUserForm
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.) JimP wrote: 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 ... -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UserForm Print Extra Copies | Excel Discussion (Misc queries) | |||
Eliminate Excel copies that have no file type | Excel Worksheet Functions | |||
eliminate data entered to a worksheet that copies to another work. | Excel Worksheet Functions | |||
Apply vba code to multiple userform objects | Excel Programming | |||
Apply vba code to multiple userform objects | Excel Programming |