+ Reply to Thread
Results 1 to 7 of 7

Text boxes in Spreadsheet

  1. #1
    Ann
    Guest

    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



  2. #2
    Tom Ogilvy
    Guest

    Re: 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" <[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

    >




  3. #3
    Ann
    Guest

    Re: 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


  4. #4
    Tom Ogilvy
    Guest

    Re: 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" <[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




  5. #5
    Ann
    Guest

    Re: 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


  6. #6
    Tom Ogilvy
    Guest

    Re: 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" <[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




  7. #7
    Ann
    Guest

    Re: 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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1