Can a Form Feed data to a Macro?

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Can a Form Feed data to a Macro?

Post by bradjedis »

Greetings,

I have VBA that will search a Column for specific data, and then highlights that specific string within a cell. I am wondering if I can build a Userform and feed the input to the code, so that I do not have to Edit the VBA each time I need to search.


I am assuming you will need to see the code, but that is on my work computer so that will have to be tomorrow.


Brad

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can a Form Feed data to a Macro?

Post by HansV »

While a userform is loaded, a macro can read the content of text boxes and other controls on the userform:

Code: Select all

    Dim strFind As String
    ...
    strFind = UserForm1.TextBox1.Value
    ...
It is also possible to change the macro to a procedure that takes an argument:

Code: Select all

Sub SearchForData(strFind As String)
    ' do something with strFind
    ...
End Sub
Call the procedure from the userform, for example when the user clicks a button:

Code: Select all

Private Sub CommandButton1_Click()
    If Me.TextBox1.Value <> "" Then
        Call SearchForData(Me.TextBox1.Value)
    End If
End Sub
Best wishes,
Hans

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Can a Form Feed data to a Macro?

Post by bradjedis »

Hans,

I fear I have not created or implemented correctly.

Please the attached. I have some sample data in Col 1 and added a Command button to invoke the UserForm, but it is not working. Can you please check the file to see where I went wrong? I added the highlight code as well.


Thanks,
Brad
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can a Form Feed data to a Macro?

Post by HansV »

The line

Code: Select all

    strFind = "UserForm1.TextBox1.Value"
sets strFind to the literal text "UserForm1.TextBox1.Value" because of the quotes, not to the value of the text box. It should be

Code: Select all

    strFind = UserForm1.TextBox1.Value
Apart from that, you should use only one of the methods that I suggested, not both.
See the attached version.

Example.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Can a Form Feed data to a Macro?

Post by bradjedis »

Ok Thanks. I will review what I did, vs. your example to understand better. How do I cause the form to close after I enter the search info, and select the Search button?

IT is working, but the Form remains until I click the "X".

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can a Form Feed data to a Macro?

Post by HansV »

Change the On Click event procedure of the command button on the userform:

Code: Select all

Private Sub CommandButton1_Click()
    If Me.TextBox1.Value <> "" Then
        Call FindAndHighlight
        ' **** New: close the form ***
        Unload Me
        ' ****************************
    End If
End Sub
Best wishes,
Hans

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Can a Form Feed data to a Macro?

Post by bradjedis »

Thanks ,,, that is interesting.

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Can a Form Feed data to a Macro?

Post by bradjedis »

So I tried to do something similar with the Range selection... Where the existing code has "With Range("H:H")"

I tried With Range("UserForm1.TextBox2.Value"). This failed.

I did add the second text box in the Userform. I would guess I am being too simplistic. If I can feed the range, I can use this on different tabs that have different arrangements of data.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can a Form Feed data to a Macro?

Post by HansV »

What does TextBox2 contain? A range address? If so, you should use

Code: Select all

With Range(UserForm1.TextBox2.Value)
again, without the quotes, since you want to refer to the text box, not to the literal text string "UserForm1.TextBox2.Value".
Best wishes,
Hans

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Can a Form Feed data to a Macro?

Post by bradjedis »

oh sweet.

I am soooo happy right now.


Thanks again!