Passing contorl ByRef gives Type Mismatch error

[ajoy123rocks]

Private Sub Game_Num_Val_Change()


Dim cntl As Control


Me.Game_Num_Val = UCase(Me.Game_Num_Val)
Set cntl = Me.Game_Num_Val


Call VerticalAlignCenter(cntl)


End Sub

Public Sub VerticalAlignCenter(ByRef ctl As Control)
MsgBox 1
On Error GoTo ErrorCode
    Dim MinimumMargin As Integer
    Dim BorderWidth As Integer
    
    If Not ((TypeOf ctl Is TextBox) Or (TypeOf ctl Is Label)) Then Exit Sub
  
    MinimumMargin = 1 * TwipsPerPoint
    BorderWidth = (ctl.BorderWidth * TwipsPerPoint) / 2
    
    ctl.TopMargin = ((ctl.Height - (ctl.FontSize * TwipsPerPoint)) / 2) - MinimumMargin - BorderWidth
    
ErrorCode:
    Exit Sub
    
End Sub

 

[RoryA]

Declare it as MSForms.Control in both places.

 

[ajoy123rocks]

Declare it as MSForms.Control in both places.

Private Sub Game_Num_Val_Change()


Dim cntl As MSForms.Control


Me.Game_Num_Val = UCase(Me.Game_Num_Val)
Set cntl = Me.Game_Num_Val


Call VerticalAlignCenter(cntl)


End Sub
Public Sub VerticalAlignCenter(ByRef ctl As MSForms.Control)
MsgBox 1
On Error GoTo ErrorCode
    Dim MinimumMargin As Integer
    Dim BorderWidth As Integer
    
    If Not ((TypeOf ctl Is TextBox) Or (TypeOf ctl Is Label)) Then Exit Sub
  
    MinimumMargin = 1 * TwipsPerPoint
    BorderWidth = (ctl.BorderWidth * TwipsPerPoint) / 2
    
    ctl.TopMargin = ((ctl.Height - (ctl.FontSize * TwipsPerPoint)) / 2) - MinimumMargin - BorderWidth
    
ErrorCode:
    Exit Sub
    
End Sub

Still the same Type mismatch error... FYI the textbox is an ActiveX textbox

 

[RoryA]

Which line gives the error?

 

[ajoy123rocks]

Set cntl = Me.Game_Num_Val

 

[Alex Blakenburg]

I am no expert on this so hopefully Rory can help fill in the gaps but your code looks remarkable like what can be found at the link below and that is in an MS Access forum not Excel.

Set TextBox and Label text to Vertically Align Center

Public Sub VerticalAlignCenter(ByRef ctl As Control) On Error GoTo ErrorCode Dim MinimumMargin As Integer Dim BorderWidth As Integer Dim TwipsPerPoint TwipsPerPoint = 20 If Not ((TypeOf ctl Is TextBox) Or (TypeOf ctl Is Label)) Then Exit Sub 'Figure out how many lines it...

www.access-programmers.co.uk

I can fix your mismatch error by declaring it in both places as:

msforms.TextBox

(declaring both as Object seems to work as well)

Once past there I get additional errors ie
• TwipsPerPoint is not declared and not given a value
• BorderWidth & TopMargin are not a properties of the Active X Text box. It has Height, Width & Top
• This line did not work.
If Not ((TypeOf ctl Is TextBox) Or (TypeOf ctl Is Label)) Then Exit Sub
The Active X Equivalent seems to be:
If TypeName(ctl) <> "TextBox" And TypeName(ctl) <> "Label" Then Exit Sub