Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract text in middle using Mid and Find or Search | Excel Discussion (Misc queries) | |||
Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
Find and replace should work in Excel text boxes | Excel Discussion (Misc queries) | |||
Search/Find in any worksheet | Excel Programming | |||
How do you search values and display them in lables/text boxes | Excel Programming |