Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts

    Explanation please!

    I have found a solution to my problem but would appreciate an explanation why it didn't work before and does now. Then I'll improve my VBA skills.

    When I run the following code from the sheet HL it works as I expected, setting the named range's value to zero and changing the text in the text box to Set
    Code:
    Sheets("HL").Unprotect
        Range("named range").Value = 0
        Sheets("HL").Shapes("Text Box 3740").Select
        Selection.Characters.Text = "Set"
    Sheets("HL").Protect
    However if I run it when another sheet has the focus, the first action (setting the value to zero) is taken but the text in the text box does not change.

    But if I prefix the above lines with . . .

    Code:
    Sheets("HL").Select
    . . . both actions are taken as expected.

    I don't understand why one action needs the prefixed line but the other doesn't ?

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,199
    Thanks
    48
    Thanked 987 Times in 917 Posts
    You are using commands that apply to the selected sheet as well as naming a sheet. If you start by selecting the sheet and then doing everything it will work.

    cheers, Paul
    Code:
    with Sheets("HL")
        .Unprotect
        .Range("named range").Value = 0
        .Shapes("Text Box 3740").Characters.Text = "Set"
        .Protect
    end with

  3. The Following User Says Thank You to Paul T For This Useful Post:

    MartinM (2016-01-08)

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Martin,

    The object "Selection" always referes to the ActiveSheet! Thus, Selection.Characters.Text = "Set" will work when the ActiveSheet is HL but NOT work when any other sheet is the ActiveSheet.

    Paul's code fixes this by specifying what sheet the actions take place against.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    MartinM (2016-01-08)

  6. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Paul,

    Your code fails on the line . . .

    Code:
    .Shapes("Text Box 3740").Characters.Text = "Set"
    . . .with Runtime Error 438: Object doesn't support this property or method. Is this a matter of syntax, or might it be because I am using Excel 2003 ?

    I remember now: the above is the reason I used the clumsy two line approach in the first place !

    Code:
        Sheets("HL").Shapes("Text Box 3740").Select
        Selection.Characters.Text = "Set"
    Last edited by MartinM; 2016-01-08 at 13:08.

  7. #5
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,199
    Thanks
    48
    Thanked 987 Times in 917 Posts
    I hadn't tested the code, don't have Excel these days.

    Try this:
    Code:
    with Sheets("HL")
        .Unprotect
        .Range("named range").Value = 0
        with .Shapes("Text Box 3740")
            .Characters.Text = "Set"
        end with
        .Protect
    end with
    cheers, Paul

  8. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks Paul - you got me on the right track and further digging on the Internet unearthed the exact syntax I needed:
    Code:
    With Sheets("HL")
        .Unprotect
        .Range("named range").Value = 0
        .Shapes("Text Box 3740").TextFrame.Characters.Text = "Set"
        .Protect
    End With

  9. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,199
    Thanks
    48
    Thanked 987 Times in 917 Posts
    If it's selected you don't need the TextFrame?!? How does that make sense?

    cheers, Paul

  10. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I know, it's weird.

    I started by recording into VBA the action of changing the text but when I tried to concatenate the resulting two lines I got the syntax error.

    Something else that needs explanation . . .

  11. #9
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,199
    Thanks
    48
    Thanked 987 Times in 917 Posts
    That is a foible of the macro recorder. It's really only a starting point.

    cheers, Paul

  12. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    When you select an object, the Selection is not necessarily the same object type as you selected originally. This is especially true of Shapes which can contain all manner of specific object types - such as Charts, Rectangles, TextBoxes and so on.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #11
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Understood, Rory, thanks.

    What surprised me is that the recorded macro . . .
    Code:
    Sheets("HL").Shapes("Text Box 3740").Select
    Selection.Characters.Text = "Set"
    . . . assumes in the second line that I am referring to the text in the Text Box, but the single line version . . .
    Code:
    Sheets("HL").Shapes("Text Box 3740").TextFrame.Characters.Text = "Set"
    . . . does not make this assumption and requires the extra TextFrame.

    It is this [seemingly] inconsistent need for TextFrame to be stated explicitly that caused my error and confusion.

  14. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Yup. Selection in the former actually referred to a Textbox, not a Shape, and the Textbox does have a Characters property.
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #13
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Something new learned, despite my advanced age !

  16. #14
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,199
    Thanks
    48
    Thanked 987 Times in 917 Posts
    Should learn something new every day. Anything else is being lazy.

    cheers, Paul

Posting Permissions

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