Passing an user form result to vba code variable

Question

I have a code that counts the files in a folder if they contain a specific string on their name.

For example: If I want it to count the files with close on their name (Close_26_03_2003.csv).

Currently the code reads the value of a cell in the sheet and searches for that string in the file name with the (InStr function). Problem is I have to write the type of file in the cell.

What I am trying to do is create an user form, with three option buttons (open, close and cancel). For open it sets the string equal to open, and search for files that have it on their name (same as for close). Cancel ends the sub.

Problem is I don't know which code I have to use in the user form for this and don't know how to pass it to the code that counts files (I though about assigning it to a variable).

Code as is:

Sub CountFiles3()

Dim path As String, count As Integer, i As Long, var As Integer
Dim ws As Worksheet
Dim Filename As String
Dim FileTypeUserForm As UserForm1

Application.Calculation = xlCalculationManual

path = ThisWorkbook.path & "\*.*"
Filename = Dir(path)

'the problem is here:
'x = user form result***************
    'if cancel = true, end sub



Set ws = ThisWorkbook.Sheets("FILES")
i = 0
Do While Filename <> "" 
    'var = InStr(Filename, ws.Cells(2, 7).Value)  'this is current code, it checks if the cell has open or close
    var = InStr(Filename, x)

    If var <> 0 Then
        i = i + 1
        ws.Cells(i + 1, 1) = Filename
        Filename = Dir()

    Else: Filename = Dir()
    End If

Loop

Application.Calculation = xlCalculationAutomatic

ws.Cells(1, 2) = i

MsgBox i & " : files found in folder"
End Sub

And this is my current user form code:

Private Sub Cancel_Click()
Me.Tag = 3 ' EndProcess
Me.Hide
End Sub

Private Sub ClosingType_Click()
Me.Tag = 2 ' "CLOSING"
Me.Hide
End Sub

Private Sub OpeningType_Click()
Me.Tag = 1 ' "OPENING"
Me.Hide
End Sub

Any ideas?

Possible duplicate of Calling a userform and returning a value — Carrosive
– Carrosive, Commented Jan 16, 2017 at 11:16

user3598756 · Accepted Answer · 2017-01-16 11:14:51Z

1

add following code to your CountFiles3() sub in the "'the problem is here:" section:

Dim x As String
x = GetValue
If x = "end" Then Exit Sub

then add following code in any module:

Function GetValue()
    With MyUserForm '<--| change "MyUserForm " to your actual UserForm name
        .Show
        GetValue = .Tag
    End With
    Unload MyUserForm '<--| change "MyUserForm " to your actual UserForm name
End Function

and change your Userform code as follwos

Private Sub Cancel_Click()
    Me.Tag = "end" ' EndProcess
    Me.Hide
End Sub

Private Sub ClosingType_Click()
    Me.Tag = "close" ' "CLOSING"
    Me.Hide
End Sub


Private Sub OpeningType_Click()
    Me.Tag = "open" ' "OPENING"
    Me.Hide
End Sub

edited Jan 16, 2017 at 11:14

answered Jan 16, 2017 at 11:01

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DGMS89 Over a year ago

Thanks for the answer. Couple of things: in this case, the GetValue Function is returning an specific value (1,2 or 3). IF it is 2, how can the code pass the Close one? Second: I am getting a Type Mismatch in the Close. UserForm (I changed the name to my own).

user3598756 Over a year ago

see edited answer. if it solves your question you may want to mark answer as accepted. thank you!

Collectives™ on Stack Overflow

Passing an user form result to vba code variable

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related