Text boxes in Spreadsheet
 

I am running the following macro to search for Account #'s in other excel
files. It is working great, except some of the files have worksheets with
Text Boxes on them, and the account # has been entered into the text box -
and the macro won't find it there. Is there any change that can be made to
also search within the text boxes?

Thanks!
Ann

Macro (Ignore the from my cut/paste):

Sub AcNos()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim AcNo As String
Dim eAc As Long
Dim i As Long
Dim sh As Long
Dim fndAc As Range

On Error GoTo Errorhandler
Application.ScreenUpdating = False

eAc = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("c:\Data") 'change directory

For i = 2 To eAc
AcNo = Sheets("Sheet1").Cells(i, 1).Value

For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path _
& "\" & objFile.Name

With Workbooks(objFile.Name)
For sh = 1 To .Sheets.Count
With .Sheets(sh).Cells
Set fndAc = .Find(AcNo _
, lookat:=xlPart _
, MatchCase:=True)
End With
If Not fndAc Is Nothing Then
ThisWorkbook.Sheets("Sheet1"). _
Cells(i, 3).Value = "Yes"
Exit For
End If
Next sh
.Close False
End With
Set objFile = Nothing
End If
Next
With Sheets("Sheet1").Cells(i, 3)
If .Value < "Yes" Then .Value = "No"
End With
Next i

Errorhandler:
Application.ScreenUpdating = True
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub

Text boxes in Spreadsheet
 

the Find command won't search textboxes. You would have to add code to look
in the textboxes. What kind of textboxes? From the control toolbox toolbar
or from the Drawing Toolbar?

--
Regards,
Tom Ogilvy


"Ann" wrote in message
...
I am running the following macro to search for Account #'s in other excel
files. It is working great, except some of the files have worksheets with
Text Boxes on them, and the account # has been entered into the text box -
and the macro won't find it there. Is there any change that can be made to
also search within the text boxes?

Thanks!
Ann

Macro (Ignore the from my cut/paste):

Sub AcNos()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim AcNo As String
Dim eAc As Long
Dim i As Long
Dim sh As Long
Dim fndAc As Range

On Error GoTo Errorhandler
Application.ScreenUpdating = False

eAc = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("c:\Data") 'change directory

For i = 2 To eAc
AcNo = Sheets("Sheet1").Cells(i, 1).Value

For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path _
& "\" & objFile.Name

With Workbooks(objFile.Name)
For sh = 1 To .Sheets.Count
With .Sheets(sh).Cells
Set fndAc = .Find(AcNo _
, lookat:=xlPart _
, MatchCase:=True)
End With
If Not fndAc Is Nothing Then
ThisWorkbook.Sheets("Sheet1"). _
Cells(i, 3).Value = "Yes"
Exit For
End If
Next sh
.Close False
End With
Set objFile = Nothing
End If
Next
With Sheets("Sheet1").Cells(i, 3)
If .Value < "Yes" Then .Value = "No"
End With
Next i

Errorhandler:
Application.ScreenUpdating = True
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub

Text boxes in Spreadsheet
 

Tom,

I am not sure how the boxes were created. Is there a way to tell? I did not
creat the workborks that have the text boxes in them, they are from another
dept.

Thanks!
Ann

"Tom Ogilvy" wrote:

the Find command won't search textboxes. You would have to add code to look
in the textboxes. What kind of textboxes? From the control toolbox toolbar
or from the Drawing Toolbar?

--
Regards,
Tom Ogilvy

Text boxes in Spreadsheet
 

Open one of the workbooks and select a sheet that has at least one of the
textboxes.

by default, the control toolbox toolbar textbox has a impression of
depth/being embedded. However, this isn't foolproof

You can try running this:

Sub Checkthebox()
Dim obj as OleObject
Dim cnt as Long
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSforms.TextBox Then
cnt = cnt + 1
End If
Next
If cnt 0 Then
MsgBox "Control Toolbox Textboxes exist"
Else
MsgBox "control Toolbox Textboxes do not exist"
End If
End Sub

If the code won't run or it says they don't exist, then you probably have
textboxes from the drawing toolbar.

this assumes there is not a mixture.

--
Regards,
Tom Ogilvy




"Ann" wrote in message
...
Tom,

I am not sure how the boxes were created. Is there a way to tell? I did
not
creat the workborks that have the text boxes in them, they are from
another
dept.

Thanks!
Ann

"Tom Ogilvy" wrote:

the Find command won't search textboxes. You would have to add code to
look
in the textboxes. What kind of textboxes? From the control toolbox
toolbar
or from the Drawing Toolbar?

--
Regards,
Tom Ogilvy

Text boxes in Spreadsheet
 

Tom,

I am getting a "User-defined type not defined" error for this line:
If TypeOf obj.Object Is MSforms.TextBox Then

Can I assume that they are Drawing Toolbar Textboxes? If so, is there a way
to search through them?

Thanks!
Ann

"Tom Ogilvy" wrote:

Open one of the workbooks and select a sheet that has at least one of the
textboxes.

by default, the control toolbox toolbar textbox has a impression of
depth/being embedded. However, this isn't foolproof

You can try running this:

Sub Checkthebox()
Dim obj as OleObject
Dim cnt as Long
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSforms.TextBox Then
cnt = cnt + 1
End If
Next
If cnt 0 Then
MsgBox "Control Toolbox Textboxes exist"
Else
MsgBox "control Toolbox Textboxes do not exist"
End If
End Sub

If the code won't run or it says they don't exist, then you probably have
textboxes from the drawing toolbar.

this assumes there is not a mixture.

--
Regards,
Tom Ogilvy

Text boxes in Spreadsheet
 

Dim tbox as Textbox

.. . .

for each tbox in .sheets(sh).Textboxes
if instr(1,AcNo,tbox.Text,vbTextcompare) then
' AcNo found

end if
Next

--
Regards,
Tom Ogilvy


"Ann" wrote in message
...
Tom,

I am getting a "User-defined type not defined" error for this line:
If TypeOf obj.Object Is MSforms.TextBox Then

Can I assume that they are Drawing Toolbar Textboxes? If so, is there a
way
to search through them?

Thanks!
Ann

"Tom Ogilvy" wrote:

Open one of the workbooks and select a sheet that has at least one of
the
textboxes.

by default, the control toolbox toolbar textbox has a impression of
depth/being embedded. However, this isn't foolproof

You can try running this:

Sub Checkthebox()
Dim obj as OleObject
Dim cnt as Long
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSforms.TextBox Then
cnt = cnt + 1
End If
Next
If cnt 0 Then
MsgBox "Control Toolbox Textboxes exist"
Else
MsgBox "control Toolbox Textboxes do not exist"
End If
End Sub

If the code won't run or it says they don't exist, then you probably
have
textboxes from the drawing toolbar.

this assumes there is not a mixture.

--
Regards,
Tom Ogilvy

Text boxes in Spreadsheet
 

Tom,

I'm not sure where to insert this:

for each tbox in .sheets(sh).Textboxes
if instr(1,AcNo,tbox.Text,vbTextcompare) then
' AcNo found

end if
Next

Into this:

Sub FastAcNos()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim tbox As TextBox
Dim AcNo As String
Dim eAc As Long
Dim i As Long
Dim sh As Long
Dim fndAc As Range

On Error GoTo Errorhandler

Application.ScreenUpdating = False

eAc = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFolder = objFSO.GetFolder("c:\Documents and Settings" & _
"\zzfy98\My Documents\Test") 'change directory

For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then

Workbooks.Open Filename:=objFolder.Path _
& "\" & objFile.Name, UpdateLinks:=False

With Workbooks(objFile.Name)

For sh = 1 To .Sheets.Count

bDone = True

For i = 1 To eAc
If LCase(ThisWorkbook.Sheets("Sheet1") _
.Cells(i, 2).Value) < "yes" Then

' All accounts not found

bDone = False
AcNo = ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value
With .Sheets(sh).Cells
Set fndAc = .Find(AcNo _
, LookIn:=xlValues _
, Lookat:=xlPart _
, MatchCase:=True)


End With
If Not fndAc Is Nothing Then
ThisWorkbook.Sheets("Sheet1"). _
Cells(i, 2).Value = "Yes"
End If
End If
Next i
If bDone Then
.Close False
Exit Sub
End If
Next sh
.Close False

Set objFile = Nothing
End With
End If
Next

For i = 1 To eAc
With ThisWorkbook.Sheets("sheet1")
If IsEmpty(.Cells(i, 2)) Then
.Cells(i, 2).Value = "No"
End If
End With
Next

Errorhandler:
Application.ScreenUpdating = True
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing

End Sub