I made a userform consisting of several modules with a similar code structure. With 4 of them having the same starting procedure, i tried to replace it with a set of functions.

userform with set of pictures

The goal of the form is arrange a set of pictures according to the user's will. By filling the textbox next the 'pictures per set', you tell how many pictures there are per row. The other commands are self explanatory. The form works fine as long as the needed boxes are filled.

I wanted to create a function that does the following: return the value from the according box and send a message if there is no value written. I tried to do this by checking the name of the box, but all I get is 'Invalid Qualifier'. Am I misunderstanding how functions work? Not sure how to continue.

Function check(value As String)
Dim v As Integer

If value = Empty Then
    Select Case value.Name
    
        Case Is = TxtB_Pictures
            MsgBox ("give the number of pictures per set")
        Case Is = TxtB_R1
            MsgBox ("give the first row")
        Case Is = TxtB_R2
            MsgBox ("give the second row")
        Case Is = TxtB_C1
            MsgBox ("give the first column")
        Case Is = TxtB_C2
            MsgBox ("give the second column")
     
Else
   v = CInt(value)
End If

check = v

End Function

Here's one of the subs, in case it's needed.

Private Sub CMB_Sort_Click()

Dim S As Shape, r, c, x, y, z, a, b, dAR, T, Ttl, sp As Integer
Dim Ar() As Integer
Dim h, h1, w, w1 As Double
Dim rAr(1 To 99) As Integer
Dim cAr(1 To 99) As Integer

Dim ws As Worksheet

'rename the pictures
x = 1
For Each S In ActiveSheet.Shapes
    S.Name = "Picture " & x
    x = x   1

Next S

dAR = detect
sp = starting_position

'starting positions
r = sp   2
c = 2
b = 1
'current pictures placed within set'
a = 0

T = check(TxtB_Pictures.value)

For Each S In ActiveSheet.Shapes
 h = S.Height / 2
 w = S.Width / 2
 h1 = h - w
 w1 = h1

If a = 0 Then
 
    ActiveSheet.Shapes("Picture " & b).Top = ActiveSheet.Rows(r).Top
    ActiveSheet.Shapes("Picture " & b).Left = ActiveSheet.Columns(c).Left
    
        c = c   Application.WorksheetFunction.RoundUp(dAR / 3, 0)

Else
    ActiveSheet.Shapes("Picture " & b).Top = ActiveSheet.Rows(r).Top
    ActiveSheet.Shapes("Picture " & b).Left = ActiveSheet.Columns(c).Left

        c = c   Application.WorksheetFunction.RoundUp(dAR / 3, 0)

End If

If ActiveSheet.Shapes("picture " & b).Rotation = 90 Then
    ActiveSheet.Shapes("Picture " & b).IncrementLeft h1
    ActiveSheet.Shapes("Picture " & b).IncrementTop -h1
End If

a = a   1
b = b   1

    'all pictures in set placed, go to next row and back to the first column'
If a = T Then
    r = r   dAR
    c = 2
    a = 0
End If
    
    
Next S

End Sub

CodePudding user response：

Please, try using the next updated function. It receives as argument the control itself:

Function check(ctrl As msforms.Control) As Integer
Dim v As Integer

If ctrl.value = "" Then
    Select Case ctrl
    
        Case TxtB_Pictures
            MsgBox ("give the number of pictures per set")
        Case TxtB_R1
            MsgBox ("give the first row")
        Case TxtB_R2
            MsgBox ("give the second row")
        Case TxtB_C1
            MsgBox ("give the first column")
        Case TxtB_C2
            MsgBox ("give the second column")
    End Select
Else
   v = CInt(ctrl.value)
End If

check = v

End Function

It can be called in the next way:

Private Sub CommandButton1_Click()
   Dim ctrlVal As Integer
    ctrlVal = check(Me.TxtB_Pictures) 'use here the control you need/wont
     If ctrlVal > 0 Then
          MsgBox ctrlVal
          'do watever you need with it...
     End If
End Sub

I answer your question as it was formulated, but I cannot imagine how you try using/calling it. I asked a clarification question about that, in a comment...