Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-05-2019, 09:58 AM   #1
Indigo
Newly Registered User
 
Join Date: Nov 2008
Location: Canada
Posts: 221
Thanks: 23
Thanked 1 Time in 1 Post
Indigo is on a distinguished road
Find TextBoxes in a Workbook and change background colour

I am hoping someone can get me over a hurdle. I have a workbook with a dozen worksheets and each worksheet contains an image and several textboxes. I need to be able to loop through each worksheet in the workbook to find specific text in a textbox and once found, change the background color to yellow. The text in the text box could be repeated across several worksheets and I need each matching textbox to be colored. I found the following code that only works on a worksheet and makes the text bold and red:

Code:
Sub FindInShape2()
On Error Resume Next

    Dim shp As Shape
    Dim sFind As String
    Dim sTemp As String
    Dim iPos As Integer
    Dim Response

    sFind = InputBox("Search for?")
    If Trim(sFind) = "" Then
        MsgBox "Nothing entered"
        Exit Sub
    End If
    sFind = LCase(sFind)
    For Each shp In ActiveSheet.Shapes
        sTemp = LCase(shp.TextFrame.Characters.Text)
        iPos = InStr(sTemp, sFind)
        If iPos > 0 Then
            With shp.TextFrame.Characters(Start:=iPos, _
              Length:=Len(sFind)).Font
                .ColorIndex = 3
                .Bold = True
            End With
        End If
    Next
    MsgBox "Finished"
End Sub
So I did some more looking and modified the above code and placed in a module:

Code:
Sub FindInTB()
On Error Resume Next

    Dim wks As Worksheet, tb As TextBox
    Dim sFind As String
    Dim sTemp As String
    Dim iPos As Integer
    Dim Response

    sFind = InputBox("Search for?")
    If Trim(sFind) = "" Then
        MsgBox "Nothing entered"
        Exit Sub
    End If
    sFind = LCase(sFind)
    For Each wks In ActiveWorkbook.Worksheets
        For Each tb In wks.TextBoxes
        sTemp = LCase(tb.TextFrame.Characters.Text)
        iPos = InStr(sTemp, sFind)
        If iPos > 0 Then
            With tb.TextFrame.Characters(Start:=iPos, _
              Length:=Len(sFind)).Font
                .ColorIndex = 3
                .Bold = True
            End With
        End If
        Next tb
    Next wks
    MsgBox "Finished"
End Sub
When I step through it, I see it looping through each worksheet, but it does not change the formatting of the textboxes. What am I missing? Your help is appreciated. Thank you.

Indigo is offline   Reply With Quote
Old 03-05-2019, 11:14 AM   #2
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,144
Thanks: 349
Thanked 533 Times in 515 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Find TextBoxes in a Workbook and change background colour

The code marked red does not look correct to me?

Quote:
Originally Posted by Indigo View Post
I am hoping someone can get me over a hurdle. I have a workbook with a dozen worksheets and each worksheet contains an image and several textboxes. I need to be able to loop through each worksheet in the workbook to find specific text in a textbox and once found, change the background color to yellow. The text in the text box could be repeated across several worksheets and I need each matching textbox to be colored. I found the following code that only works on a worksheet and makes the text bold and red:

Code:
Sub FindInShape2()
On Error Resume Next

    Dim shp As Shape
    Dim sFind As String
    Dim sTemp As String
    Dim iPos As Integer
    Dim Response

    sFind = InputBox("Search for?")
    If Trim(sFind) = "" Then
        MsgBox "Nothing entered"
        Exit Sub
    End If
    sFind = LCase(sFind)
    For Each shp In ActiveSheet.Shapes
        sTemp = LCase(shp.TextFrame.Characters.Text)
        iPos = InStr(sTemp, sFind)
        If iPos > 0 Then
            With shp.TextFrame.Characters(Start:=iPos, _
              Length:=Len(sFind)).Font
                .ColorIndex = 3
                .Bold = True
            End With
        End If
    Next
    MsgBox "Finished"
End Sub
So I did some more looking and modified the above code and placed in a module:

Code:
Sub FindInTB()
On Error Resume Next

    Dim wks As Worksheet, tb As TextBox
    Dim sFind As String
    Dim sTemp As String
    Dim iPos As Integer
    Dim Response

    sFind = InputBox("Search for?")
    If Trim(sFind) = "" Then
        MsgBox "Nothing entered"
        Exit Sub
    End If
    sFind = LCase(sFind)
    For Each wks In ActiveWorkbook.Worksheets
        For Each tb In wks.TextBoxes
        sTemp = LCase(tb.TextFrame.Characters.Text)
        iPos = InStr(sTemp, sFind)
        If iPos > 0 Then
            With tb.TextFrame.Characters(Start:=iPos, _
              Length:=Len(sFind)).Font
                .ColorIndex = 3
                .Bold = True
            End With
        End If
        Next tb
    Next wks
    MsgBox "Finished"
End Sub
When I step through it, I see it looping through each worksheet, but it does not change the formatting of the textboxes. What am I missing? Your help is appreciated. Thank you.
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 03-05-2019, 11:33 AM   #3
Indigo
Newly Registered User
 
Join Date: Nov 2008
Location: Canada
Posts: 221
Thanks: 23
Thanked 1 Time in 1 Post
Indigo is on a distinguished road
Re: Find TextBoxes in a Workbook and change background colour

Hi Gasman,

The code is something I found here: https://excel.tips.net/T011281_Findi...ext_Boxes.html

The code actually gets hung up in this line when I run it:

Code:
sTemp = LCase(tb.TextFrame.Characters.Text)
With an: Object doesn't support this property or method
error

Indigo is offline   Reply With Quote
Old 03-05-2019, 11:42 AM   #4
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,144
Thanks: 349
Thanked 533 Times in 515 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Find TextBoxes in a Workbook and change background colour

So type that statement in afresh and see what intellisense allows you.?

On Error Resume Next is not helpful either.?

Should it be tb.Text property ?

https://docs.microsoft.com/en-us/off...s.textbox.text
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Gasman; 03-05-2019 at 11:52 AM.
Gasman is offline   Reply With Quote
Old 03-05-2019, 12:10 PM   #5
Indigo
Newly Registered User
 
Join Date: Nov 2008
Location: Canada
Posts: 221
Thanks: 23
Thanked 1 Time in 1 Post
Indigo is on a distinguished road
Re: Find TextBoxes in a Workbook and change background colour

Thank you for responding, Gasman...

If what you meant by this statement:

"So type that statement in afresh and see what intellisense allows you.?"

is this:Code.png

it will not prompt for "TextFrame"... only Text.....

As for the link you sent, that applies to a textbox in Access. I am struggling with this issue in Excel.
Indigo is offline   Reply With Quote
Old 03-05-2019, 12:13 PM   #6
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,144
Thanks: 349
Thanked 533 Times in 515 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Find TextBoxes in a Workbook and change background colour

No, I used it in Excel.

There is also a .Characters.Text property which seems to return the same thing.
As you could see there is no TextFrame property for a textbox ?

Try recording a macro.

https://www.ozgrid.com/forum/forum/h...t-in-a-textbox

According to this link, not possible with plain textbox?

http://www.vbforums.com/showthread.p...ng-in-text-box

so try a RichTextbox
https://stackoverflow.com/questions/...rform-excelvba
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Gasman; 03-05-2019 at 12:41 PM.
Gasman is offline   Reply With Quote
Old 03-05-2019, 05:50 PM   #7
Darrell
Newly Registered User
 
Darrell's Avatar
 
Join Date: Feb 2001
Location: New Zealand
Posts: 86
Thanks: 1
Thanked 3 Times in 2 Posts
Darrell
Re: Find TextBoxes in a Workbook and change background colour

See if this helps you...

Code:
Sub FindInTB()
On Error Resume Next

    Dim wks As Worksheet
    Dim tb As Shape
    Dim sFind As String
    Dim sTemp As String
    Dim iPos As Integer
    Dim Response

    sFind = InputBox("Search for?")
    If Trim(sFind) = "" Then
        MsgBox "Nothing entered"
        Exit Sub
    End If
    sFind = LCase(sFind)
    For Each wks In ActiveWorkbook.Worksheets
        For Each tb In wks.Shapes
            sTemp = LCase(tb.TextFrame.Characters.Text)
            iPos = InStr(sTemp, sFind)
            If iPos > 0 Then
                With tb
                    .Fill.ForeColor.RGB = RGB(255, 255, 0)
                End With
            End If
        Next tb
    Next wks
    MsgBox "Finished"
End Sub

__________________
Build a man a fire and you keep him warm for a day.
Set the man on fire and you keep him warm for the rest of his life...
Darrell is offline   Reply With Quote
The Following 2 Users Say Thank You to Darrell For This Useful Post:
Gasman (03-05-2019), Indigo (03-06-2019)
Old 03-06-2019, 11:14 AM   #8
Indigo
Newly Registered User
 
Join Date: Nov 2008
Location: Canada
Posts: 221
Thanks: 23
Thanked 1 Time in 1 Post
Indigo is on a distinguished road
Re: Find TextBoxes in a Workbook and change background colour

Darrell,

That's fantastic! Thank you so much for taking the time to do that..... so, if I want to clear the back ground color - or reset... would it be something like this?

Code:
Sub TextBoxReset()
   Dim wks As Worksheet, tb As Shape
    For Each wks In ActiveWorkbook.Worksheets
        For Each tb In wks.Shapes
            With tb
            .Fill.ForeColor.RGB = RGB(255, 255, 255)
            End With
        Next tb
    Next wks
End Sub
because it's not quite working....

Nevermind... when I added:

Code:
On Error Resume Next
it works just dandy!

Last edited by Indigo; 03-06-2019 at 11:24 AM. Reason: Provide more detail
Indigo is offline   Reply With Quote
Old 03-06-2019, 12:04 PM   #9
Darrell
Newly Registered User
 
Darrell's Avatar
 
Join Date: Feb 2001
Location: New Zealand
Posts: 86
Thanks: 1
Thanked 3 Times in 2 Posts
Darrell
Re: Find TextBoxes in a Workbook and change background colour

Define "not quite working"...

What you have should work so now I'm assuming that you may have shapes in your workbook other than TextBoxes which means that you will need to identify them first if you want to change their properties.

Code:
Sub TextBoxReset()
Dim wks As Worksheet
Dim tb As Shape

For Each wks In ActiveWorkbook.Worksheets
    For Each tb In wks.Shapes
        If Lcase(Left(tb.Name),7)) = "textbox" Then
            tb.Fill.ForeColor.RGB = RGB(255, 255, 255)
        End if
    Next tb
Next wks

End Sub
So unless your texboxes have custom names this should work ok
__________________
Build a man a fire and you keep him warm for a day.
Set the man on fire and you keep him warm for the rest of his life...
Darrell is offline   Reply With Quote
Old 03-06-2019, 01:31 PM   #10
Indigo
Newly Registered User
 
Join Date: Nov 2008
Location: Canada
Posts: 221
Thanks: 23
Thanked 1 Time in 1 Post
Indigo is on a distinguished road
Re: Find TextBoxes in a Workbook and change background colour

oh... that's a novel way to approach it, I like it....except... this line is producing a syntax error.

Code:
If Lcase(Left(tb.Name),7)) = "textbox" Then
Indigo is offline   Reply With Quote
Old 03-06-2019, 02:19 PM   #11
Darrell
Newly Registered User
 
Darrell's Avatar
 
Join Date: Feb 2001
Location: New Zealand
Posts: 86
Thanks: 1
Thanked 3 Times in 2 Posts
Darrell
Re: Find TextBoxes in a Workbook and change background colour

Sorry Indigo I typed that on the fly without checking it.

Please take out the extra closing bracket after tb.name

Code:
If LCase(Left(tb.Name, 7)) = "textbox" Then

__________________
Build a man a fire and you keep him warm for a day.
Set the man on fire and you keep him warm for the rest of his life...
Darrell is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Change group background colour fat controller Reports 2 09-27-2016 01:22 PM
Change textbox background colour pending value of two textboxes on a form andersar Forms 4 07-10-2013 09:16 PM
Change Colour of Tab Control Background simonwar Forms 3 07-31-2009 07:46 AM
Change Background Colour ! Branagan General 4 02-01-2007 06:48 AM
Change the colour of the background danielgraham General 9 10-02-2003 06:33 AM




All times are GMT -8. The time now is 06:19 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World