PDA

View Full Version : Populate Form Label Captions from Worksheet



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.

Dave
01-10-2018, 12:25 AM
I'm guessing that "lblTrackPart01Description" is the name that you have given a caption on a userform. If it is, it should really be... UserformName.lblTrackPart01Description.
If U want to remove the zero in the caption names (ie. lblTrackPart01Description should be lblTrackPart1Description) then the following code will get rid of all that repetition. HTH. Dave

Private Function TrackParts(LabelNum As String, PartDescription As String, _
PartInbound As String, PartOutbound As String)
'Fills in the labels with the data from the worksheet when the part is selected for tracking.
With "lblTrackPart" & LabelNum & "Description"
.Caption = PartDescription
End With
With "lblTrackPart" & LabelNum & "Inbound"
.Caption = PartInbound
End With
With "lblTrackPart" & LabelNum & "Outbound"
.Caption = PartOutbound
End With
End Function
Then to use it for caption #1 example...

Call TrackParts(1, CStr(Sheets("Parts Tracking").Range("E5").Value), _
CStr(Sheets("Parts Tracking").Range("AA5").Value), _
CStr(Sheets("Parts Tracking").Range("AC5").Value))

snb
01-10-2018, 02:14 AM
Use a Sub, not a function


Private Sub cbTrackPart01_Click()
sn=Sheets("Parts Tracking").range("A5:AC5")

for j= 1 to 3
me("lblTrackPart01 & choose(j,"Description","Inbound","outbound")).Caption=sn(1,choose(j,5,27,29))
next
End Sub