NewYears1978
Board Regular
- Joined
- Feb 25, 2014
- Messages
- 107
So I've read about Public, Private and that Sub = Public Sub
I've seen projects with some Subs in their on modules and such but I still cannot quite understand when and where to put them.
Here is one example, I have the following sub located in my form (TradeRouteForm) code section
I figured it might be nice to move it to it's own module or a module that contains many of my other Subs already.
So I made a module named "ModuleLoadColors" and pasted it in there with some changes (.Me had to be changed since not located in my form code)
No errors are thrown but the code doesn't work..I can't think of any other reason why.
I also thought maybe it wasn't calling the sub correctly where I have it being called elsewhere so I changed "LoadColors" to "Call LoadColors" and also tried "Call TradeRouteForm.LoadColors"
I hope I have explained this well enough..it's been throwing me off ever since I started learning excel. (I have Googled and read a ton of pages about Private vs Public also..but that didn't help)
Also, I added a random msgbox in there to see if the sub was calling and it is indeed..it just isn't doing what it should (read data in cells and pass the values)
I've seen projects with some Subs in their on modules and such but I still cannot quite understand when and where to put them.
Here is one example, I have the following sub located in my form (TradeRouteForm) code section
Code:
Private Sub LoadColors()
Dim oCtrl As Control
For Each oCtrl In Me.Controls
If TypeName(oCtrl) = "TextBox" Or TypeName(oCtrl) = "Label" Or TypeName(oCtrl) = "Frame" Then
oCtrl.BorderColor = ActiveWorkbook.Colors(Worksheets("Options").Range("A4").Value)
End If
Next oCtrl
For Each oCtrl In Me.Controls
If TypeName(oCtrl) = "Label" Then
If oCtrl.Tag <> "ColorSquares" Then
oCtrl.ForeColor = ActiveWorkbook.Colors(Worksheets("Options").Range("B4").Value)
End If
End If
Next oCtrl
For Each oCtrl In Me.Controls
If TypeName(oCtrl) = "TextBox" Then
If oCtrl.Tag <> "OptionBoxes" Then
oCtrl.ForeColor = ActiveWorkbook.Colors(Worksheets("Options").Range("C4").Value)
End If
End If
Next oCtrl
For Each oCtrl In Me.Controls
If TypeName(oCtrl) = "Label" Then
If oCtrl.Tag = "Buttons" Or oCtrl.Tag = "RouteButtons" Or oCtrl.Tag = "OptionButtons" Then
oCtrl.ForeColor = ActiveWorkbook.Colors(Worksheets("Options").Range("D4").Value)
End If
End If
Next oCtrl
End Sub
I figured it might be nice to move it to it's own module or a module that contains many of my other Subs already.
So I made a module named "ModuleLoadColors" and pasted it in there with some changes (.Me had to be changed since not located in my form code)
Code:
Public Sub LoadColors()
Dim oCtrl As Control
For Each oCtrl In TradeRouteForm.Controls
If TypeName(oCtrl) = "TextBox" Or TypeName(oCtrl) = "Label" Or TypeName(oCtrl) = "Frame" Then
oCtrl.BorderColor = ActiveWorkbook.Colors(Worksheets("Options").Range("A4").Value)
End If
Next oCtrl
For Each oCtrl In TradeRouteForm.Controls
If TypeName(oCtrl) = "Label" Then
If oCtrl.Tag <> "ColorSquares" Then
oCtrl.ForeColor = ActiveWorkbook.Colors(Worksheets("Options").Range("B4").Value)
End If
End If
Next oCtrl
For Each oCtrl In TradeRouteForm.Controls
If TypeName(oCtrl) = "TextBox" Then
If oCtrl.Tag <> "OptionBoxes" Then
oCtrl.ForeColor = ActiveWorkbook.Colors(Worksheets("Options").Range("C4").Value)
End If
End If
Next oCtrl
For Each oCtrl In TradeRouteForm.Controls
If TypeName(oCtrl) = "Label" Then
If oCtrl.Tag = "Buttons" Or oCtrl.Tag = "RouteButtons" Or oCtrl.Tag = "OptionButtons" Then
oCtrl.ForeColor = ActiveWorkbook.Colors(Worksheets("Options").Range("D4").Value)
End If
End If
Next oCtrl
End Sub
No errors are thrown but the code doesn't work..I can't think of any other reason why.
I also thought maybe it wasn't calling the sub correctly where I have it being called elsewhere so I changed "LoadColors" to "Call LoadColors" and also tried "Call TradeRouteForm.LoadColors"
I hope I have explained this well enough..it's been throwing me off ever since I started learning excel. (I have Googled and read a ton of pages about Private vs Public also..but that didn't help)
Also, I added a random msgbox in there to see if the sub was calling and it is indeed..it just isn't doing what it should (read data in cells and pass the values)
Last edited: