DragonWood
01-09-2018, 09:08 PM
Greetings and Salutations,
I could use some help with this code please. I know it could be simplified with an Array and/or a Loop feature. Unfortunately, I'm not very good at either of those. I will keep working on it and will post my results here, if/when I figure it out.
In the meantime, some guidance would be nice.
The ultimate plan is to provide a tracking mechanism for the parts / supplies being shipped to the field engineers. Since parts are shipped from different locations and at different times an engineer could have several packages on the way to or from the work site at any given time. As the information is provided to the engineer as far as what the item is and what the tracking number is he or she will fill it in on the Parts Tracking worksheet in the workbook.
When the engineer wants to track the package(s) he or she will use the form. Select the packages to be tracked, and press the button that says to track the packages. This button will then pull up the FedEx package tracking site and fill in the tracking number(s) to be tracked. This part works fine.
The code below works fine too, but it is cumbersome and not "Clean Code". That is what I need help with. A better way to write this so it is cleaner.
Each function is linked to a checkbox on the form for each corresponding part. When the checkbox is checked, it calls the function to fill in the label. (See the sample below:)
Private Sub cbTrackPart01_Click()
Call TrackPart01
End Sub
FYI: I set all my variables at the beginning of the form so they can be used throughout without having to declare them each time they are used. Because the code to populate the FedEx tracking numbers page uses the same variables.
Private Function TrackPart01()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part01Description = .Range("E5").Value
Part01Inbound = .Range("AA5").Value
Part01Outbound = .Range("AC5").Value
End With
With lblTrackPart01Description
.Caption = Part01Description
End With
With lblTrackPart01Inbound
.Caption = Part01Inbound
End With
With lblTrackPart01Outbound
.Caption = Part01Outbound
End With
End Function
Private Function TrackPart02()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part02Description = .Range("E6").Value
Part02Inbound = .Range("AA6").Value
Part02Outbound = .Range("AC6").Value
End With
With lblTrackPart02Description
.Caption = Part02Description
End With
With lblTrackPart02Inbound
.Caption = Part02Inbound
End With
With lblTrackPart02Outbound
.Caption = Part02Outbound
End With
End Function
Private Function TrackPart03()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part03Description = .Range("E7").Value
Part03Inbound = .Range("AA7").Value
Part03Outbound = .Range("AC7").Value
End With
With lblTrackPart03Description
.Caption = Part03Description
End With
With lblTrackPart03Inbound
.Caption = Part03Inbound
End With
With lblTrackPart03Outbound
.Caption = Part03Outbound
End With
End Function
Private Function TrackPart04()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part04Description = .Range("E8").Value
Part04Inbound = .Range("AA8").Value
Part04Outbound = .Range("AC8").Value
End With
With lblTrackPart04Description
.Caption = Part04Description
End With
With lblTrackPart04Inbound
.Caption = Part04Inbound
End With
With lblTrackPart04Outbound
.Caption = Part04Outbound
End With
End Function
Private Function TrackPart05()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part05Description = .Range("E9").Value
Part05Inbound = .Range("AA9").Value
Part05Outbound = .Range("AC9").Value
End With
With lblTrackPart05Description
.Caption = Part05Description
End With
With lblTrackPart05Inbound
.Caption = Part05Inbound
End With
With lblTrackPart05Outbound
.Caption = Part05Outbound
End With
End Function
Private Function TrackPart06()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part06Description = .Range("E10").Value
Part06Inbound = .Range("AA10").Value
Part06Outbound = .Range("AC10").Value
End With
With lblTrackPart06Description
.Caption = Part06Description
End With
With lblTrackPart06Inbound
.Caption = Part06Inbound
End With
With lblTrackPart06Outbound
.Caption = Part06Outbound
End With
End Function
Private Function TrackPart07()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part07Description = .Range("E11").Value
Part07Inbound = .Range("AA11").Value
Part07Outbound = .Range("AC11").Value
End With
With lblTrackPart07Description
.Caption = Part07Description
End With
With lblTrackPart07Inbound
.Caption = Part07Inbound
End With
With lblTrackPart07Outbound
.Caption = Part07Outbound
End With
End Function
Private Function TrackPart08()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part08Description = .Range("E12").Value
Part08Inbound = .Range("AA12").Value
Part08Outbound = .Range("AC12").Value
End With
With lblTrackPart08Description
.Caption = Part08Description
End With
With lblTrackPart08Inbound
.Caption = Part08Inbound
End With
With lblTrackPart08Outbound
.Caption = Part08Outbound
End With
End Function
Private Function TrackPart09()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part09Description = .Range("E13").Value
Part09Inbound = .Range("AA13").Value
Part09Outbound = .Range("AC13").Value
End With
With lblTrackPart09Description
.Caption = Part09Description
End With
With lblTrackPart09Inbound
.Caption = Part09Inbound
End With
With lblTrackPart09Outbound
.Caption = Part09Outbound
End With
End Function
Private Function TrackPart10()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part10Description = .Range("E14").Value
Part10Inbound = .Range("AA14").Value
Part10Outbound = .Range("AC14").Value
End With
With lblTrackPart10Description
.Caption = Part10Description
End With
With lblTrackPart10Inbound
.Caption = Part10Inbound
End With
With lblTrackPart10Outbound
.Caption = Part10Outbound
End With
End Function
Private Function TrackPart11()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part11Description = .Range("E15").Value
Part11Inbound = .Range("AA15").Value
Part11Outbound = .Range("AC15").Value
End With
With lblTrackPart11Description
.Caption = Part11Description
End With
With lblTrackPart11Inbound
.Caption = Part11Inbound
End With
With lblTrackPart11Outbound
.Caption = Part11Outbound
End With
End Function
Private Function TrackPart12()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part12Description = .Range("E16").Value
Part12Inbound = .Range("AA16").Value
Part12Outbound = .Range("AC16").Value
End With
With lblTrackPart12Description
.Caption = Part12Description
End With
With lblTrackPart12Inbound
.Caption = Part12Inbound
End With
With lblTrackPart12Outbound
.Caption = Part12Outbound
End With
End Function
Private Function TrackPart13()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part13Description = .Range("E17").Value
Part13Inbound = .Range("AA17").Value
Part13Outbound = .Range("AC17").Value
End With
With lblTrackPart13Description
.Caption = Part13Description
End With
With lblTrackPart13Inbound
.Caption = Part13Inbound
End With
With lblTrackPart13Outbound
.Caption = Part13Outbound
End With
End Function
Private Function TrackPart14()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part14Description = .Range("E18").Value
Part14Inbound = .Range("AA18").Value
Part14Outbound = .Range("AC18").Value
End With
With lblTrackPart14Description
.Caption = Part14Description
End With
With lblTrackPart14Inbound
.Caption = Part14Inbound
End With
With lblTrackPart14Outbound
.Caption = Part14Outbound
End With
End Function
Private Function TrackPart15()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part15Description = .Range("E19").Value
Part15Inbound = .Range("AA19").Value
Part15Outbound = .Range("AC19").Value
End With
With lblTrackPart15Description
.Caption = Part15Description
End With
With lblTrackPart15Inbound
.Caption = Part15Inbound
End With
With lblTrackPart15Outbound
.Caption = Part15Outbound
End With
End Function
Private Function TrackPart16()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part16Description = .Range("E20").Value
Part16Inbound = .Range("AA20").Value
Part16Outbound = .Range("AC20").Value
End With
With lblTrackPart16Description
.Caption = Part16Description
End With
With lblTrackPart16Inbound
.Caption = Part16Inbound
End With
With lblTrackPart16Outbound
.Caption = Part16Outbound
End With
End Function
Private Function TrackPart17()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part17Description = .Range("E21").Value
Part17Inbound = .Range("AA21").Value
Part17Outbound = .Range("AC21").Value
End With
With lblTrackPart17Description
.Caption = Part17Description
End With
With lblTrackPart17Inbound
.Caption = Part17Inbound
End With
With lblTrackPart17Outbound
.Caption = Part17Outbound
End With
End Function
Private Function TrackPart18()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part18Description = .Range("E22").Value
Part18Inbound = .Range("AA22").Value
Part18Outbound = .Range("AC22").Value
End With
With lblTrackPart18Description
.Caption = Part18Description
End With
With lblTrackPart18Inbound
.Caption = Part18Inbound
End With
With lblTrackPart18Outbound
.Caption = Part18Outbound
End With
End Function
Private Function TrackPart19()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part19Description = .Range("E23").Value
Part19Inbound = .Range("AA23").Value
Part19Outbound = .Range("AC23").Value
End With
With lblTrackPart19Description
.Caption = Part19Description
End With
With lblTrackPart19Inbound
.Caption = Part19Inbound
End With
With lblTrackPart19Outbound
.Caption = Part19Outbound
End With
End Function
Private Function TrackPart20()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part20Description = .Range("E24").Value
Part20Inbound = .Range("AA24").Value
Part20Outbound = .Range("AC24").Value
End With
With lblTrackPart20Description
.Caption = Part20Description
End With
With lblTrackPart20Inbound
.Caption = Part20Inbound
End With
With lblTrackPart20Outbound
.Caption = Part20Outbound
End With
End Function
Thanks in advance for any help.
I could use some help with this code please. I know it could be simplified with an Array and/or a Loop feature. Unfortunately, I'm not very good at either of those. I will keep working on it and will post my results here, if/when I figure it out.
In the meantime, some guidance would be nice.
The ultimate plan is to provide a tracking mechanism for the parts / supplies being shipped to the field engineers. Since parts are shipped from different locations and at different times an engineer could have several packages on the way to or from the work site at any given time. As the information is provided to the engineer as far as what the item is and what the tracking number is he or she will fill it in on the Parts Tracking worksheet in the workbook.
When the engineer wants to track the package(s) he or she will use the form. Select the packages to be tracked, and press the button that says to track the packages. This button will then pull up the FedEx package tracking site and fill in the tracking number(s) to be tracked. This part works fine.
The code below works fine too, but it is cumbersome and not "Clean Code". That is what I need help with. A better way to write this so it is cleaner.
Each function is linked to a checkbox on the form for each corresponding part. When the checkbox is checked, it calls the function to fill in the label. (See the sample below:)
Private Sub cbTrackPart01_Click()
Call TrackPart01
End Sub
FYI: I set all my variables at the beginning of the form so they can be used throughout without having to declare them each time they are used. Because the code to populate the FedEx tracking numbers page uses the same variables.
Private Function TrackPart01()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part01Description = .Range("E5").Value
Part01Inbound = .Range("AA5").Value
Part01Outbound = .Range("AC5").Value
End With
With lblTrackPart01Description
.Caption = Part01Description
End With
With lblTrackPart01Inbound
.Caption = Part01Inbound
End With
With lblTrackPart01Outbound
.Caption = Part01Outbound
End With
End Function
Private Function TrackPart02()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part02Description = .Range("E6").Value
Part02Inbound = .Range("AA6").Value
Part02Outbound = .Range("AC6").Value
End With
With lblTrackPart02Description
.Caption = Part02Description
End With
With lblTrackPart02Inbound
.Caption = Part02Inbound
End With
With lblTrackPart02Outbound
.Caption = Part02Outbound
End With
End Function
Private Function TrackPart03()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part03Description = .Range("E7").Value
Part03Inbound = .Range("AA7").Value
Part03Outbound = .Range("AC7").Value
End With
With lblTrackPart03Description
.Caption = Part03Description
End With
With lblTrackPart03Inbound
.Caption = Part03Inbound
End With
With lblTrackPart03Outbound
.Caption = Part03Outbound
End With
End Function
Private Function TrackPart04()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part04Description = .Range("E8").Value
Part04Inbound = .Range("AA8").Value
Part04Outbound = .Range("AC8").Value
End With
With lblTrackPart04Description
.Caption = Part04Description
End With
With lblTrackPart04Inbound
.Caption = Part04Inbound
End With
With lblTrackPart04Outbound
.Caption = Part04Outbound
End With
End Function
Private Function TrackPart05()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part05Description = .Range("E9").Value
Part05Inbound = .Range("AA9").Value
Part05Outbound = .Range("AC9").Value
End With
With lblTrackPart05Description
.Caption = Part05Description
End With
With lblTrackPart05Inbound
.Caption = Part05Inbound
End With
With lblTrackPart05Outbound
.Caption = Part05Outbound
End With
End Function
Private Function TrackPart06()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part06Description = .Range("E10").Value
Part06Inbound = .Range("AA10").Value
Part06Outbound = .Range("AC10").Value
End With
With lblTrackPart06Description
.Caption = Part06Description
End With
With lblTrackPart06Inbound
.Caption = Part06Inbound
End With
With lblTrackPart06Outbound
.Caption = Part06Outbound
End With
End Function
Private Function TrackPart07()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part07Description = .Range("E11").Value
Part07Inbound = .Range("AA11").Value
Part07Outbound = .Range("AC11").Value
End With
With lblTrackPart07Description
.Caption = Part07Description
End With
With lblTrackPart07Inbound
.Caption = Part07Inbound
End With
With lblTrackPart07Outbound
.Caption = Part07Outbound
End With
End Function
Private Function TrackPart08()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part08Description = .Range("E12").Value
Part08Inbound = .Range("AA12").Value
Part08Outbound = .Range("AC12").Value
End With
With lblTrackPart08Description
.Caption = Part08Description
End With
With lblTrackPart08Inbound
.Caption = Part08Inbound
End With
With lblTrackPart08Outbound
.Caption = Part08Outbound
End With
End Function
Private Function TrackPart09()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part09Description = .Range("E13").Value
Part09Inbound = .Range("AA13").Value
Part09Outbound = .Range("AC13").Value
End With
With lblTrackPart09Description
.Caption = Part09Description
End With
With lblTrackPart09Inbound
.Caption = Part09Inbound
End With
With lblTrackPart09Outbound
.Caption = Part09Outbound
End With
End Function
Private Function TrackPart10()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part10Description = .Range("E14").Value
Part10Inbound = .Range("AA14").Value
Part10Outbound = .Range("AC14").Value
End With
With lblTrackPart10Description
.Caption = Part10Description
End With
With lblTrackPart10Inbound
.Caption = Part10Inbound
End With
With lblTrackPart10Outbound
.Caption = Part10Outbound
End With
End Function
Private Function TrackPart11()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part11Description = .Range("E15").Value
Part11Inbound = .Range("AA15").Value
Part11Outbound = .Range("AC15").Value
End With
With lblTrackPart11Description
.Caption = Part11Description
End With
With lblTrackPart11Inbound
.Caption = Part11Inbound
End With
With lblTrackPart11Outbound
.Caption = Part11Outbound
End With
End Function
Private Function TrackPart12()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part12Description = .Range("E16").Value
Part12Inbound = .Range("AA16").Value
Part12Outbound = .Range("AC16").Value
End With
With lblTrackPart12Description
.Caption = Part12Description
End With
With lblTrackPart12Inbound
.Caption = Part12Inbound
End With
With lblTrackPart12Outbound
.Caption = Part12Outbound
End With
End Function
Private Function TrackPart13()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part13Description = .Range("E17").Value
Part13Inbound = .Range("AA17").Value
Part13Outbound = .Range("AC17").Value
End With
With lblTrackPart13Description
.Caption = Part13Description
End With
With lblTrackPart13Inbound
.Caption = Part13Inbound
End With
With lblTrackPart13Outbound
.Caption = Part13Outbound
End With
End Function
Private Function TrackPart14()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part14Description = .Range("E18").Value
Part14Inbound = .Range("AA18").Value
Part14Outbound = .Range("AC18").Value
End With
With lblTrackPart14Description
.Caption = Part14Description
End With
With lblTrackPart14Inbound
.Caption = Part14Inbound
End With
With lblTrackPart14Outbound
.Caption = Part14Outbound
End With
End Function
Private Function TrackPart15()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part15Description = .Range("E19").Value
Part15Inbound = .Range("AA19").Value
Part15Outbound = .Range("AC19").Value
End With
With lblTrackPart15Description
.Caption = Part15Description
End With
With lblTrackPart15Inbound
.Caption = Part15Inbound
End With
With lblTrackPart15Outbound
.Caption = Part15Outbound
End With
End Function
Private Function TrackPart16()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part16Description = .Range("E20").Value
Part16Inbound = .Range("AA20").Value
Part16Outbound = .Range("AC20").Value
End With
With lblTrackPart16Description
.Caption = Part16Description
End With
With lblTrackPart16Inbound
.Caption = Part16Inbound
End With
With lblTrackPart16Outbound
.Caption = Part16Outbound
End With
End Function
Private Function TrackPart17()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part17Description = .Range("E21").Value
Part17Inbound = .Range("AA21").Value
Part17Outbound = .Range("AC21").Value
End With
With lblTrackPart17Description
.Caption = Part17Description
End With
With lblTrackPart17Inbound
.Caption = Part17Inbound
End With
With lblTrackPart17Outbound
.Caption = Part17Outbound
End With
End Function
Private Function TrackPart18()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part18Description = .Range("E22").Value
Part18Inbound = .Range("AA22").Value
Part18Outbound = .Range("AC22").Value
End With
With lblTrackPart18Description
.Caption = Part18Description
End With
With lblTrackPart18Inbound
.Caption = Part18Inbound
End With
With lblTrackPart18Outbound
.Caption = Part18Outbound
End With
End Function
Private Function TrackPart19()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part19Description = .Range("E23").Value
Part19Inbound = .Range("AA23").Value
Part19Outbound = .Range("AC23").Value
End With
With lblTrackPart19Description
.Caption = Part19Description
End With
With lblTrackPart19Inbound
.Caption = Part19Inbound
End With
With lblTrackPart19Outbound
.Caption = Part19Outbound
End With
End Function
Private Function TrackPart20()
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With ActiveWorkbook.Worksheets("Parts Tracking")
Part20Description = .Range("E24").Value
Part20Inbound = .Range("AA24").Value
Part20Outbound = .Range("AC24").Value
End With
With lblTrackPart20Description
.Caption = Part20Description
End With
With lblTrackPart20Inbound
.Caption = Part20Inbound
End With
With lblTrackPart20Outbound
.Caption = Part20Outbound
End With
End Function
Thanks in advance for any help.