Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro search text boxes
Is there any way for a macro to search or iterate through text boxes?
I need to find and replace text that might be within text boxes (and not just in cells), for hundreds of spreadsheets. I don't know what excel or windows versions, but probably 2003 on XP. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro search text boxes
The following code will look at every workbook in a folder, search
every text box (ActiveX control textbox) on every worksheet, and replace FindText with ReplaceText. Change the lines marked with '<<<<< to the values you need. Sub AAA() Dim Folder As String Dim FileName As String Dim WB As Workbook Dim WS As Worksheet Dim OLEObj As OLEObject Dim FindText As String Dim ReplaceText As String Dim S As String Folder = "C:\Test" '<<<<< CHANGE AS NEEDED FindText = "abc" '<<<<< CHANGE AS NEEDED ReplaceText = "def" '<<<<< CHANGE AS NEEDED ChDrive Folder ChDir Folder FileName = Dir("*.xls", vbNormal) Do Until FileName = vbNullString Set WB = Workbooks.Open(FileName) For Each WS In WB.Worksheets For Each OLEObj In WS.OLEObjects If TypeOf OLEObj.Object Is MSForms.TextBox Then S = Replace(OLEObj.Object.Text, _ FindText, ReplaceText) OLEObj.Object.Text = S End If Next OLEObj Next WS WB.Close savechanges:=True FileName = Dir() Loop End Sub Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Fri, 12 Feb 2010 15:43:01 -0800, Mike H wrote: Is there any way for a macro to search or iterate through text boxes? I need to find and replace text that might be within text boxes (and not just in cells), for hundreds of spreadsheets. I don't know what excel or windows versions, but probably 2003 on XP. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro search text boxes
Thanks a bunch. Looks like you've done that before.
I already had the search folders part, the OLEObj is the part I needed. I'll put it to good use at work. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro search text boxes
Thanks a bunch. Looks like you've done that before. After 12 years of Office and Excel development, sometimes I feel like I've done everything before. I already had the search folders part, the OLEObj is the part I needed. The OleObject is basically a generic container with few properties of its own. The real key is OleObject.Object which gets you a reference to the actual object contained within OleObject. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Fri, 12 Feb 2010 19:53:02 -0800, Mike H wrote: Thanks a bunch. Looks like you've done that before. I already had the search folders part, the OLEObj is the part I needed. I'll put it to good use at work. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro search text boxes
Dear All,
I'm still teaching myself VBA, and there's a huge problem whose solution i cannot figure out. Your help would be greatly appreciated. i have an input workbook from which to extract data to another workbook (thus 2 different workbooks. The input sheet has data split up in five categories. Each category has five rows of data. So say the categories are titled: A, B, C, D, and E in column A. With each category separated by five rows, each row (in column C) has different numbers. The output sheet (in the other workbook) has all categories A, B, C, D, and E arranged (not alphabetically) in separate rows (column 1), with each category separated by five empty rows. The solution i desperately need is for a macro to input data within their matching category in the input sheet. I think this means that it has to check that the category within the input sheet matches that in the output sheet, so that it can place that categories data in the right category's columns. again, your help will be highly appreciated. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro search text boxes
Bryen,
You might want to move this to a new thread - this thread was already two days old when you posted it and no one will ever see it here. MH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Form Text Boxes - Copy format of text boxes | Excel Discussion (Misc queries) | |||
Setting an array of text boxes equal to individual form text boxes | Excel Programming | |||
Text Search Macro? | Excel Discussion (Misc queries) | |||
Find / Search for text boxes in a worksheet | Excel Programming | |||
How do you search values and display them in lables/text boxes | Excel Programming |