1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Floating Text Boxes in Excel

Discussion in 'Business Applications' started by Poptart7912, Aug 13, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. Poptart7912

    Poptart7912 Thread Starter

    Joined:
    Aug 13, 2003
    Messages:
    2
    I am trying to create floating text boxes within a spreadsheet but have run into a wall...I would like for the boxes (oh, they have hyperlinks to other sheets within the workbook) to move as the user scrolls through the worksheet. Any ideas?

    I do not have a lot of vb knowledge so you might have to break it down Barney-style for me.

    Thanks for your help.
     
  2. mrwendal

    mrwendal

    Joined:
    Jul 25, 2001
    Messages:
    377
    I can't do what you're trying to, but perhaps some other users have some ideas for you.

    The closest i could get is this:

    Hit Alt+F11 in the spreadsheet to go to VB.
    Hit F7 to get code window

    Paste following code..

    Edited: Code error - ignore this code & see updated post below this one

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With ActiveWindow
    ActiveWindow.ScrollRow
    TextBox1.Top = Target.Top
    End With
    End Sub


    Close VB editor.

    now, when you scroll down the page, the textbox ("textbox1") doesn't move. However, if you click in a cell further up/down the page, the text box jumps to that row but remains in the same columnÂ…

    It's a start if nothing else!
     
  3. mrwendal

    mrwendal

    Joined:
    Jul 25, 2001
    Messages:
    377
    Apologies,

    just use this code:


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    TextBox1.Top = Target.Top
    End Sub
     
  4. Poptart7912

    Poptart7912 Thread Starter

    Joined:
    Aug 13, 2003
    Messages:
    2
    No apologies!! I am thankful there is someone who can visualize what I'm trying to do. Everyone in the office looks at me cross - eyed when I start talking about cool Excel stuff.

    I get an error (even on the corrected code) -- Ambiguious Name Detected: Worksheet_SelectionChange.

    Would the code be different for a shape (retangle) than for a text box -- other than the name TextBox1? I'm wondering if an autoshape can be more easily manipulated....
     
  5. mrwendal

    mrwendal

    Joined:
    Jul 25, 2001
    Messages:
    377
    Sorry, I thought you meant the text box option on the Control Toolbox as opposed the 'drawn' textbox.

    Try this:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveSheet.Shapes("Text Box 1").Top = Target.Top
    End Sub


    This presumes your drawn text box is called Text Box 1.

    Make sure you enter the code in the correct part of the vb window.

    hit Alt+F11
    Click Ctrl + R

    in the project window, double click the name of your worksheet and then paste this code in the window that appears.

    Does this help?
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/155322

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice