Text boxes in Spreadsheet

[Ann]

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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Ann]

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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Ann]

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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Ann]

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