VBA Code To Define Textbox As Data Extraction Source

raindrop
Lounger
Posts: 36
Joined: 04 Feb 2013, 06:22

VBA Code To Define Textbox As Data Extraction Source

Post by raindrop »

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
You do not have the required permissions to view the files attached to this post.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: VBA Code To Define Textbox As Data Extraction Source

Post by Rudi »

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.

raindrop
Lounger
Posts: 36
Joined: 04 Feb 2013, 06:22

Re: VBA Code To Define Textbox As Data Extraction Source

Post by raindrop »

Hi Rudi Sir!
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.

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

Re: VBA Code To Define Textbox As Data Extraction Source

Post by HansV »

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

raindrop
Lounger
Posts: 36
Joined: 04 Feb 2013, 06:22

Re: VBA Code To Define Textbox As Data Extraction Source

Post by raindrop »

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

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

Re: VBA Code To Define Textbox As Data Extraction Source

Post by HansV »

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

raindrop
Lounger
Posts: 36
Joined: 04 Feb 2013, 06:22

Re: VBA Code To Define Textbox As Data Extraction Source

Post by raindrop »

Hi Hans Sir,

It's amazing ! Thank You Very Much. It works fine. Problem is solved !

Thanks & Regards
Raindrop