Find / Search for text boxes in a worksheet
 

Does anyone know any method for searching a workbook for text that is
contained in a text box?

The basic Find command searches only the text in cells (or formulas) it does
not search the text in the text box.

If someone could helpme that would be great

Find / Search for text boxes in a worksheet
 

hi,
I don't think that is possible with build in xl features.
Regards
Frank

-----Original Message-----
Does anyone know any method for searching a workbook for
text that is
contained in a text box?

The basic Find command searches only the text in cells
(or formulas) it does
not search the text in the text box.

If someone could helpme that would be great


.

Find / Search for text boxes in a worksheet
 

What kind of textbox? From the control toolbox toolbar or from the Forms
Toolbar?

There isn't a single command for this - so are you looking for code that
loops through the textboxes?

--
Regards,
Tom Ogilvy


"John Ellis" wrote in message
...
Does anyone know any method for searching a workbook for text that is
contained in a text box?

The basic Find command searches only the text in cells (or formulas) it
does
not search the text in the text box.

If someone could helpme that would be great

Find / Search for text boxes in a worksheet
 

Tom,

It is just a "normal" text box created by a user,not code generated that
contains job names or the like.

We use an excel spreadsheet for scheduling and the text boxes are useful
as they cn be clicked and dragged around as production changes.

We would like the facility to seach for a name in the etxt boxes rather
than having to search all the way through the sheet manually.

At the moment the "Find" simply searches the cell contents and ignores
the contents of the texts boxes.

The text boxes typically have something like:
SS04 - 294
Sunline
October Mailing

A macro or VB code embedded would beperfect if anyone could advise me
how to go about it ?

Thanks in advance



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Find / Search for text boxes in a worksheet
 

There is no normal textbox. There is a textbox from the drawing toolbar (my
mistake in saying forms) and there is a textbox from the control toolbox
toolbar. As you might imagine, they are addressed differently, so it would
be important to know which.

With the worksheet active, run this code

Sub DetermineType()
msgbox "Textboxes from drawing toolbar: " & ActiveSheet.Textboxes.count
cnt = 0
for each tbox in ActiveSheet.OleObjects
if typeof tbox.object is MSForms.Textbox then
cnt = cnt + 1
end if
Next
Msgbox "Textboxes from the Control toolbox Toolbar: " & cnt
End Sub

--
Regards,
Tom Ogilvy

"John Ellis" wrote in message
...
Tom,

It is just a "normal" text box created by a user,not code generated that
contains job names or the like.

We use an excel spreadsheet for scheduling and the text boxes are useful
as they cn be clicked and dragged around as production changes.

We would like the facility to seach for a name in the etxt boxes rather
than having to search all the way through the sheet manually.

At the moment the "Find" simply searches the cell contents and ignores
the contents of the texts boxes.

The text boxes typically have something like:
SS04 - 294
Sunline
October Mailing

A macro or VB code embedded would beperfect if anyone could advise me
how to go about it ?

Thanks in advance



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Find / Search for text boxes in a worksheet
 

Tom,

User-defined type not defined

is the error I am getting !!

If TypeOf tbox.Object Is MSForms.TextBox Then

Debugger stops at TypeOf


I hope that this makes sense ?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Find / Search for text boxes in a worksheet
 

The previous code ran fine for me before I posted it. However,

Put this in a general module in your workbook (in the vbe, Insert=Module).
Put it in that module.

Sub DetermineType()
Dim cnt As Long, tbox As OLEObject
MsgBox "Textboxes from drawing toolbar: " _
& ActiveSheet.TextBoxes.Count
cnt = 0
For Each tbox In ActiveSheet.OLEObjects
If TypeOf tbox.Object Is MSForms.TextBox Then
cnt = cnt + 1
End If
Next
MsgBox "Textboxes from the Control toolbox Toolbar: " & cnt
End Sub

--
Regards,
Tom Ogilvy


"John Ellis" wrote in message
...
Tom,

User-defined type not defined

is the error I am getting !!

If TypeOf tbox.Object Is MSForms.TextBox Then

Debugger stops at TypeOf


I hope that this makes sense ?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Find / Search for text boxes in a worksheet
 

Tom,

thanks for your patience.

That's exactlyhow I tried to do it in a new module.

I click save (under vbe) then click run () then the error I get is the
same.

Perhaps I am wearing a big pair of stupid glasses and I can not see my
errors ?

Thanks in advance

My private e mail is if this helps you ?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Find / Search for text boxes in a worksheet
 

I pasted it in from the email in Excel 97 and Excel 2003. It ran fine in
both.

If there isn't anything wrong with the code, there isn't much I can say. I
assume you are using at least xl97. If not, you are using xl5 or xl95, then
the textbox from the control toolbox toolbar did not exist in those
versions.



--
Regards,
Tom Ogilvy


"John Ellis" wrote in message
...

Tom,

thanks for your patience.

That's exactlyhow I tried to do it in a new module.

I click save (under vbe) then click run () then the error I get is the
same.

Perhaps I am wearing a big pair of stupid glasses and I can not see my
errors ?

Thanks in advance

My private e mail is if this helps you ?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Find / Search for text boxes in a worksheet
 

Make that xl2002 - my typo.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
I pasted it in from the email in Excel 97 and Excel 2003. It ran fine in
both.

If there isn't anything wrong with the code, there isn't much I can say.
I
assume you are using at least xl97. If not, you are using xl5 or xl95,
then
the textbox from the control toolbox toolbar did not exist in those
versions.



--
Regards,
Tom Ogilvy


"John Ellis" wrote in message
...

Tom,

thanks for your patience.

That's exactlyhow I tried to do it in a new module.

I click save (under vbe) then click run () then the error I get is the
same.

Perhaps I am wearing a big pair of stupid glasses and I can not see my
errors ?

Thanks in advance

My private e mail is if this helps you ?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Find / Search for text boxes in a worksheet
 

I'd be willing to bet that the textboxes are from the drawing toolbar.

If I don't have any of those controls from the control toolbox toolbar (and no
userforms) in my workbook, then I'd need to set a reference to "microsoft forms
2.0 object library" to get by that line of code.

For John:
Go back to the VBE, select your workbook's project and do Insert|Userform. Then
run Tom's code.

I'm betting you'll see 0 for the control toolbox toolbar count.



Tom Ogilvy wrote:

Make that xl2002 - my typo.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
I pasted it in from the email in Excel 97 and Excel 2003. It ran fine in
both.

If there isn't anything wrong with the code, there isn't much I can say.
I
assume you are using at least xl97. If not, you are using xl5 or xl95,
then
the textbox from the control toolbox toolbar did not exist in those
versions.



--
Regards,
Tom Ogilvy


"John Ellis" wrote in message
...

Tom,

thanks for your patience.

That's exactlyhow I tried to do it in a new module.

I click save (under vbe) then click run () then the error I get is the
same.

Perhaps I am wearing a big pair of stupid glasses and I can not see my
errors ?

Thanks in advance

My private e mail is if this helps you ?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



--

Dave Peterson

Find / Search for text boxes in a worksheet
 

Tom/Dave,

that's right.

The text boxes are created from the Drawing bar in Excel.

If it helps I am using Excel 2003.

John



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Find / Search for text boxes in a worksheet
 

This may get you started:

Option Explicit
Sub testme()

Dim TBox As TextBox
Dim FindWhat As String

FindWhat = "good"

For Each TBox In ActiveSheet.TextBoxes
If InStr(1, TBox.Text, FindWhat, vbTextCompare) 0 Then
MsgBox "Found: " & FindWhat & " in " & TBox.Name
End If
Next TBox

End Sub



John Ellis wrote:

Tom/Dave,

that's right.

The text boxes are created from the Drawing bar in Excel.

If it helps I am using Excel 2003.

John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

--

Dave Peterson

Find / Search for text boxes in a worksheet
 

Dave,

You the man !!!

It works and gives me a count ref for teh box number !!!

Is there anyway I can run a macro (which it does now) to call the code
and enter a string in a dialog box to search on?

Thanks in advance!!

By the way are you in UK or USA ?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Find / Search for text boxes in a worksheet
 

how about this:

Option Explicit
Sub testme()

Dim TBox As TextBox
Dim FindWhat As String

FindWhat = InputBox(prompt:="What to find?")

If Trim(FindWhat) = "" Then
Exit Sub
End If

For Each TBox In ActiveSheet.TextBoxes
If InStr(1, TBox.Text, FindWhat, vbTextCompare) 0 Then
MsgBox "Found: " & FindWhat & " in " & TBox.Name
End If
Next TBox

End Sub

And it's actually giving the name of the textbox. Select the textbox manually
and look at the namebox (to the left of the formula bar).

And I'm sitting in the middle of the USA!



John Ellis wrote:

Dave,

You the man !!!

It works and gives me a count ref for teh box number !!!

Is there anyway I can run a macro (which it does now) to call the code
and enter a string in a dialog box to search on?

Thanks in advance!!

By the way are you in UK or USA ?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

--

Dave Peterson

Find / Search for text boxes in a worksheet
 

If you knew that, why were we playing around? Seems like it would have
just been easier to tell me.

Glad you got a solution from Dave.

--
Regards,
Tom Ogilvy

"John Ellis" wrote in message
...
Tom/Dave,

that's right.

The text boxes are created from the Drawing bar in Excel.

If it helps I am using Excel 2003.

John



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Find / Search for text boxes in a worksheet
 

Tom,

I am sorry if I have misled you in anyway.

I didn't mean to.

I do apreciate the help given on this forum.

It is often difficult to know the exact terminology when trying new
things out.

Kind regards

your humblest slave

John Ellis



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Find / Search for text boxes in a worksheet
 

I am not sure why you are reacting as if this was a reprimand or criticism.
I was merely curious why we horsed around with trying to discover what kind
of textboxes if you already knew (but apparently you didn't). With only two
kinds - drawing toolbar and control toolbox toolbar . . .

If you didn't know until now, then you didn't know. I am sure Dave's
astute recognition of the probable problem added to your knowledge base, no?
(which is why I assume you now have that knowledge).

Anyway, as I said I was just asking a question - and I assume now I have the
answer. Thanks.

--
Regards,
Tom Ogilvy





"John Ellis" wrote in message
...
Tom,

I am sorry if I have misled you in anyway.

I didn't mean to.

I do apreciate the help given on this forum.

It is often difficult to know the exact terminology when trying new
things out.

Kind regards

your humblest slave

John Ellis



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Find / Search for text boxes in a worksheet
 

Tom,

I am sorry if it came over the wrong way.
I was just trying to say sorry.
Thanks for all your help.

John



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!