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
Can a Form Feed data to a Macro?
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can a Form Feed data to a Macro?
While a userform is loaded, a macro can read the content of text boxes and other controls on the userform:
It is also possible to change the macro to a procedure that takes an argument:
Call the procedure from the userform, for example when the user clicks a button:
Code: Select all
Dim strFind As String
...
strFind = UserForm1.TextBox1.Value
...
Code: Select all
Sub SearchForData(strFind As String)
' do something with strFind
...
End Sub
Code: Select all
Private Sub CommandButton1_Click()
If Me.TextBox1.Value <> "" Then
Call SearchForData(Me.TextBox1.Value)
End If
End Sub
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Can a Form Feed data to a Macro?
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
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.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can a Form Feed data to a Macro?
The line
sets strFind to the literal text "UserForm1.TextBox1.Value" because of the quotes, not to the value of the text box. It should be
Apart from that, you should use only one of the methods that I suggested, not both.
See the attached version.
Code: Select all
strFind = "UserForm1.TextBox1.Value"
Code: Select all
strFind = UserForm1.TextBox1.Value
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Can a Form Feed data to a Macro?
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".
IT is working, but the Form remains until I click the "X".
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can a Form Feed data to a Macro?
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
Hans
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Can a Form Feed data to a Macro?
Thanks ,,, that is interesting.
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Can a Form Feed data to a Macro?
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.
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.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can a Form Feed data to a Macro?
What does TextBox2 contain? A range address? If so, you should use
again, without the quotes, since you want to refer to the text box, not to the literal text string "UserForm1.TextBox2.Value".
Code: Select all
With Range(UserForm1.TextBox2.Value)
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Can a Form Feed data to a Macro?
oh sweet.
I am soooo happy right now.
Thanks again!
I am soooo happy right now.
Thanks again!