Calling a module through a "For Each" VBA

[Pienuts]

Hi, all! I have found and inserted a module to center text vertically (don't even get me started about how that isn't just a part of Access ), and it is called VerticallyCenter. The forum I found it on said to use it as:

VerticallyCenter Me.YourLabelControlName

But I have a metric S-Tonne of controls to center, so I tagged the controls with "Center" and wanted to use something like this:

Private Sub Form_Load()
For Each vntControl In Me.Controls
If vntControl.Tag = "Center" Then
VerticallyCenter
End If
Next vntControl
End Sub

But I can't figure out the correct syntax.
Thanks for your help!!
Here's the module, for anyone interested:

Private Sub VerticallyCenter(ctl As Control)
 Dim lngHeight As Long
 lngHeight = fTextHeight(ctl)
 ' Rounding will result in a 1 to 2 pixel margin of error
 ' of every control before it renders text.
 ctl.TopMargin = ((ctl.Height - lngHeight) / 2)
 
End Sub

 

[Fran Lombard]

try this

Private Sub Form_Load()
For Each vntControl In Me.Controls
If vntControl.Tag = "Center" Then
    VerticallyCenter vntControl
End If
Next vntControl
End Sub

 

[Pienuts]

Thanks for the reply, Fran!
Now it says ByRef argument type mismatch. That's a new one for me...

 

[Fran Lombard]

try this

I'm assuming vntControl is an undeclared variant - it needs to be declared as a control

Private Sub Form_Load()

dim ctl as Control
For Each ctl In Me.Controls
If ctl.Tag = "Center" Then
    VerticallyCenter ctl
End If
Next ctl
End Sub

 

[Pienuts]

Okay, this is driving me batty!
Now it says sub or function not defined - am I putting the module in correctly? I inserted the module and pasted the code - it's under Module2. Should I rename the module?
Should both be under Private Sub?
Sorry, I really thought this was going to be a quick syntax fix...

 

[Fran Lombard]

If the sub VerticallyCenter is in a seperate module than the calling code ie. your form event - then yes it needs to be Public.

If its a sub in the class module of the form than it only needs to be private.

Most likely - you would want to define VerticallyCenter as Public in a separate module so than it could be used by any form in your project.

 

[Pienuts]

Okay, thanks, Fran - that's what I was thinking.
I think I'm going to shelf this for now - this time it's telling me the fTextHeight in the module is not defined. Ugh.
Thank you so much for your assistance!

 

[Fran Lombard]

Looks like fTextHeight is a separate function that you need to include in your module. Recheck the site you found the function and see if you missed coping that one

 

[Pienuts]

Yep, that was it. Sigh. Thanks for all your help!