CODEWITHSUNDEEP
excelargumentsuserformvba
Samatar
Samatar

Reputation: 51

VBA pass argument to userform

I have a userform which I wish to pass a range to. I have tried a couple of different ways to do it but does not seem to work.

Here is the sub code:

Option Explicit

Sub Additional_Comments_Normal()
Dim MSG1 As Integer
Dim msg As String
Dim act As Range


On Error GoTo ErrHandler

'Calls userform

MSG1 = MsgBox("Would you like to add comments", vbYesNo, "Add comments")


If MSG1 = vbYes Then

With AddComments
On Error Resume Next
Set act = Application.InputBox(Prompt:="Please choose files you wish to add comments to", Type:=8)

If act Is Nothing Then
Exit Sub
End If
    
    Application.ScreenUpdating = True
    .Show
    
End With

Else
Exit Sub
End If

ErrHandler:

If Err.Number <> 0 Then
 msg = "Error # " & Str(Err.Number) & " was generated by " _
         & Err.Source & Chr(13) & Err.Description
MsgBox msg, , "Error", Err.HelpFile, Err.HelpContext
End If
End Sub

And the userform code is here:

Public act As Range

Private Sub CommandButton1_Click()

Dim ctl As Control
Dim rng As Range
Dim MSG2 As Integer
Dim sfile As String


    If act.Column > 1 Then
        MsgBox ("Please choose File name from Column 1")
    Exit Sub
    End If

        If act.Row < 4 Then
            MsgBox ("Please choose a valid file")
        Exit Sub
        End If
         
            If Me.TxtComment.Value = "" Then
                MsgBox "Please add comments", vbExclamation, "Additional Comments"
                Me.TxtComment.SetFocus
            Exit Sub
            End If

            If Me.TxtName.Value = "" Then
                MsgBox "Please add your name", vbExclamation, "Additional Comments"
                Me.TxtName.SetFocus
            Exit Sub
            End If



MSG1 = MsgBox("Add Comments ?", vbYesNo, "Add comments")

If MSG1 = vbNo Then

End If

If MSG1 = vbYes Then

act.Offset(0, 16).Value = act.Offset(0, 16).Text & " " & Me.TxtComment.Value
act.Offset(0, 17).Value = act.Offset(0, 17).Text & " " & Me.TxtName.Value
 
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then
ctl.Value = ""
End If
Next ctl
      
AddComments.Hide

Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True

End If

End Sub


Private Sub CommandButton2_Click()

End Sub

Private Sub CommandButton3_Click()
Unload Me
End Sub

Private Sub UserForm_Click()

End Sub

I then get an error about the act not being defined variable.

Can anyone shed some light on better process for this?

Upvotes: 0

Views: 3544

Answers (1)

Ralph
Ralph

Reputation: 9434

You have set Option Explicit at the top of your code. That means that all variables need to be defined (which is considered good programming practice). So, you have two options to resolve this:

(1) Remove the line Option Explicitfrom your code or

(2) define all of your variables using the Dim command. In this case you'd have to add Dim act as Range to your Sub CommandButton1_Click on the form.

If you want to pass a variable to another sub then you can do so calling that sub with that variable like so:

Call Additional_Comments_Normal(act)

and the sub header neeeds to change like so:

Sub Additional_Comments_Normal(ByVal act as Range)

'(your code)'

End Sub

If "passing a variable to another sub" is too much trouble then you can also save the range somewhere in your file like so:

SomeHiddenSheet.Range("A1").Value2 = act

and in the other sub you can initiate act again:

act = SomeHiddenSheet.Range("A1").Value2

Upvotes: 1

Related Questions