Hi,
I would like a little change in my macro for extraction, not C1 as source but textbox. I tried but get error on reconstructing my code string...
Set rSrc = Range("C:C").SpecialCells(xlCellTypeConstants). How can I put textbox as a source? Please have a look at my attachment.
Thanks & Regards
Raindrop
VBA Code To Define Textbox As Data Extraction Source
-
- Lounger
- Posts: 36
- Joined: 04 Feb 2013, 06:22
VBA Code To Define Textbox As Data Extraction Source
You do not have the required permissions to view the files attached to this post.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: VBA Code To Define Textbox As Data Extraction Source
The following code will get the text from the Textbox...
Code: Select all
Sub Extail()
Dim re As Object, mc As Object, m As Object
Dim c As Range, rSrc As Object, sSrc As String, rDest As Range
Dim i As Long
Dim S As String
Set rSrc = Sheets("Sheet1").TextBox1
sSrc = rSrc.Text
...
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Lounger
- Posts: 36
- Joined: 04 Feb 2013, 06:22
Re: VBA Code To Define Textbox As Data Extraction Source
Hi Rudi Sir!
I changed my code in my macro but it shows Run-time error '438'.
Thanks & Regards
Raindrop
I changed my code in my macro but it shows Run-time error '438'.
Thanks & Regards
Raindrop
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA Code To Define Textbox As Data Extraction Source
Can you tell us what you want to accomplish? It's not clear why you're trying to loop through cells to get the text from a single text box.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 36
- Joined: 04 Feb 2013, 06:22
Re: VBA Code To Define Textbox As Data Extraction Source
Hi Hans Sir,
Actually I use macro (R1.xlsm) to extract emails from bunch data located in cell C1 to Column H, it works. But Instead of Cell C1, I want to use textbox, so I can run macro over textbox and get result in column H. Is It possible ? I used textbox as source according to Mr. Rudi but i get error.
Thanks & Regards
Raindrop
Actually I use macro (R1.xlsm) to extract emails from bunch data located in cell C1 to Column H, it works. But Instead of Cell C1, I want to use textbox, so I can run macro over textbox and get result in column H. Is It possible ? I used textbox as source according to Mr. Rudi but i get error.
Thanks & Regards
Raindrop
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA Code To Define Textbox As Data Extraction Source
Try this version:
Code: Select all
Sub Extail()
Dim re As Object, mc As Object, m As Object
Dim c As Object, rDest As Range
Dim i As Long
Dim S As String
Set c = Sheet1.TextBox1
Set rDest = Range("H1")
rDest.EntireColumn.ClearContents
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Global = True
re.Pattern = "\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,6}\b"
S = c.Value
If re.test(S) = True Then
Set mc = re.Execute(S)
For Each m In mc
rDest.Offset(i, 0).Value = m
i = i + 1
Next m
End If
End Sub
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 36
- Joined: 04 Feb 2013, 06:22
Re: VBA Code To Define Textbox As Data Extraction Source
Hi Hans Sir,
It's amazing ! Thank You Very Much. It works fine. Problem is solved !
Thanks & Regards
Raindrop
It's amazing ! Thank You Very Much. It works fine. Problem is solved !
Thanks & Regards
Raindrop