+ Reply to Thread
Results 1 to 24 of 24

Floating text box

  1. #1
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2016
    Posts
    129

    Floating text box

    How can I make a text box or picture stay visible on my screen even if I scroll the spreadsheet?

    I googled a couple things but I cant get them to work. Any help would be great!!

    Thanks

  2. #2
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2016
    Posts
    129

    Re: Floating text box

    excel 2016 BTW

  3. #3
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Floating text box

    Good afternoon phillyd023

    Please take the time to alter your profile, stating that you now use Excel 2016.

    As for your request, a userform (set to vbModeless) will do what you need. You can place text or an image within it, and call it using the code below :
    Please Login or Register  to view this content.
    The userform will remain on screen whilst you scroll around until dismissed.

    HTH

    DominicB

  4. #4
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2016
    Posts
    129

    Re: Floating text box

    Still learning VBA. I right click on the sheet tab. Then I go to INSERT-USERFORM. if I add a text box it lets me type but I cant enter down to the next line to make another sentence. I click on image but nothing happens. I used snipping tool and copied something but I cant paste it in the userform box.

  5. #5
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Floating text box

    Hi phillyd023

    OK. First things first. Open the attached file. Does this do what you want to do and does it look (sort of) how you want it to look?
    If so, I'll talk you through how to do it, but there's no point me spending time doing that if you don't like it.

    BTW, I might not be able to get back on here tonight, so if I don't come back to you straight away, just check back in the morning and I'll pick up with it then (if nobody else has jumped in and taken over which sometimes happens).

    HTH

    DominicB
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2016
    Posts
    129

    Re: Floating text box

    I assume you are using the button as a prop ........... when I scroll the sheet down the button disappears. Just in case I was in clear in what I wanted ....... lets say I want a picture (or text box that says Hi!) to appear on screen in the top right. if I start scrolling down through the spreadsheet the picture or text box will always be visible in the top right ..... regardless of where I am on the spreadsheet. an overlay that never moves if you will.

  7. #7
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2016
    Posts
    129

    Re: Floating text box

    oh wait .......... I clicked the button and saw the screen that popped up ........ yes that is what I want!!!! even better I can put it where I want!! how do I do that??!!!

  8. #8
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Floating text box

    Hi phillyd023

    There's a few steps to this, so we'll take them one at a time.

    It seems like you've already figured out how to create a blank userform. Then using the toolbox, select Image, and then use the crosshairs to draw a frame on your userform.
    Make sure that the frame is the selected object (if you've just drawn it, it still should be) and in the properties pane click in the picture field, and then click on the "..." button.
    If you cannot see the properties pane, go to View > Properties Window.

    This will open the familiar file selection dialog to select your image file. You can use the PictureSizeMode option to fit your image to the size of your frame.

    Let me know when you've got that bit working and we'll look at the text.

    HTH

    DominicB

  9. #9
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2016
    Posts
    129

    Re: Floating text box

    Userform box open and picture is added.

  10. #10
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Floating text box

    Hi phillyd023

    Way to go

    Now for the text. Add a Label from the toolbox. To type your text in it, make sure the label frame is selected and click in it once. this should allow you to type your text directly into the label.
    If you need to do a line break, use Shift + Enter, and carry on typing.

    For the "close" button, use CommandButton in the toolbox, and draw it. You can click to change the text, or do it from the properties window. Double click to open the code that will drive the button and copy the middle line from the code below in there :
    Please Login or Register  to view this content.

    That's pretty much it for the userform. You can press F5 to test it.

    Let me know if you need help with calling it from your spreadsheet.

    HTH

    DominicB

  11. #11
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2016
    Posts
    129

    Re: Floating text box

    okay got that done ... how do I call it to the sheet?

  12. #12
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Floating text box

    Hi phillyd023

    From Excel, do you have the Developer tab visible at the top? If not, you'll need to make it visible - follow this link for instructions.

    From the Developer tab, click Insert and find the Command Button gadget under ActiveX Controls.

    Once you have drawn it, double click on it, to enter the code pane for that button.

    The code in this pane should be something like this (you'll have to enter the middle line yourself) :
    Please Login or Register  to view this content.
    Go back to the sheet, and click on the Design Mode button under the Developer tab. Creating a gadget puts you into Design Mode automatically - the gadget will not work in Design Mode. Any changes you make to the button, size, colour, text etc, and you must go into Design Mode first, then exit Design Mode when you're done.

    And that's just about it.

    HTH

    DominicB

  13. #13
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2016
    Posts
    129

    Re: Floating text box

    wow that is pretty neat!!! it worked! Thank you.

    Question though ........... the command button scrolls away so if I scroll down, make a change and save the spreadsheet then close the spreadsheet, the next time I open the spreadsheet I have to scroll back to the top to press the command button to get the pop up window then scroll back all the way back down. is there a way to make the command button stay put as well or make the pop up window stay permanently when closed and reopened?

  14. #14
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Floating text box

    Hi phillyd023

    I think we have three options here :
    • If your command button is at the top of your page, in rows 1, 2 or 3, then you could freeze the screen at row 3 or 4, so that the top few rows are always visible (the command button included). Do this from View > Freeze Panes > Freeze Panes (have your cursor in the place where you want the freeze point to go)
    • Set up a keyboard shortcut so that, say, Ctrl + q will open the userform (then you don't have to scroll to the top)
    • Set up a macro that opens the userform and set that macro to run upon the workbook opening
    If you want to go with either of the last two options and need some guidance, let me know.

    HTH

    DominicB

  15. #15
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2016
    Posts
    129

    Re: Floating text box

    Yeah, after I sent that last reply I thought about just attaching the command button to a CTRL function ......... that is what I will probably do, however, I am kind of interested in how to set up that macro to run upon opening .. that sounds interesting and might be something I can use in a couple other applications I have. Can you teach me that, please?

  16. #16
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Floating text box

    Hi phillyd023

    Something like this :
    Please Login or Register  to view this content.
    This needs to be placed as a module in the ThisWorkbook object - you can find this in the Project Explorer window of the VBE.

    HTH

    DominicB

  17. #17
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2016
    Posts
    129

    Re: Floating text box

    what do I put in the "your code goes here"?


    Also, I cant figure out how to assign the ctrl + q to the command button. I thought it would be easy lie I do when I create a macro but I am stumped.

  18. #18
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Floating text box

    Hi phillyd023
    Quote Originally Posted by phillyd023 View Post
    what do I put in the "your code goes here"?
    If you wanted to open your userform on opening, you would use the code below, but you can stick any macro in there to run on opening :
    Please Login or Register  to view this content.
    Quote Originally Posted by phillyd023 View Post
    Also, I cant figure out how to assign the ctrl + q to the command button. I thought it would be easy lie I do when I create a macro but I am stumped.
    You can only assign a keyboard shortcut to a macro in a module - not one in a sheet module which is what we have used.
    To enter a macro go to Insert > Module and paste some code like this into it:
    Please Login or Register  to view this content.
    To assign a shortcut go to Developer > Macros > Options and enter a shortcut to be entered with Ctrl.

    HTH

    DominicB

  19. #19
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2016
    Posts
    129

    Re: Floating text box

    I have been struggling with this ...... i can't get either to work so i am not understanding something.

    As far as adding a control + Q to the command bar, how do i do that. apparently i cant assign a macro shortcut to the the command button active x the same way i do with a normal button.

  20. #20
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Floating text box

    Hi phillyd023

    All the information you need is in post 16 and 18. Have you actually read these? I have told you that you can't assign a shortcut to a command button and how to get around it in my last post.

    If you are still struggling then post back and tell me exactly what you are struggling with and what is happening (or not happening). Please don't just tell me "it doesn't work" - remember I can't see your workbook, it's not obvious to me what isn't working or how it's not working.

    Also, can we take these one at a time?

    DominicB

  21. #21
    Forum Contributor
    Join Date
    03-08-2018
    Location
    Denmark
    MS-Off Ver
    2016 for Windows
    Posts
    359

    Re: Floating text box

    Hi phillyd023

    As it being weekend, you now can enjoy theese 2 youtube channels that will get you startet if you find USERFORMS interesting.


    https://www.youtube.com/watch?v=O1L5cPQXv1Q

    https://www.youtube.com/user/OnLinePCLearning/videos

  22. #22
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2016
    Posts
    129

    Re: Floating text box

    yeah I am missing something here, sorry

    Per post 16, i double clicked on command button, inserted module, and pasted your code. what am i putting in the "your code goes in here"? i am assuming i need to enter the Control + Q to assign the shortcut? if so i don't know how to assign that in the VBA.

  23. #23
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Floating text box

    Hi phillyd023

    Post 16 is answering your question on how to run the userform on opening the file.
    Please Login or Register  to view this content.
    This needs to be placed as a module in the ThisWorkbook object - you can find this in the Project Explorer window of the VBE.

    When you close and re-open the file, the userform should open automtically without any action required on your behalf.

    HTH

    DominicB

  24. #24
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2016
    Posts
    129

    Re: Floating text box

    AHA ........ 'ThisWorkbook object"

    Sorry I read through the above as the workbook I wanted to open ..... not literally 'ThisWorkbook' in the code .... I was making a module. it is working now!!!

    This will help me quite a bit plus I have learned quite a bit more about the VBA from you, Dominic. The youtube vids I was watching to get this done were going an entirely different route, but given what you have taught me here going back to the vids might make a little more sense.

    I appreciate the time and the teachings, you've been a great help I appreciate the patience. have a good weekend!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. floating text box when scroll
    By NYBoy in forum Excel General
    Replies: 4
    Last Post: 05-18-2020, 09:30 AM
  2. Floating Text Box
    By drgkt in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-30-2017, 01:19 PM
  3. Creating a Floating Text Box
    By MatthewIJClark in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-19-2014, 11:41 AM
  4. Floating Text Box
    By MatthewIJClark in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-14-2013, 11:45 AM
  5. How to add a floating text box in 2007
    By zhuowang1980 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-24-2013, 03:54 PM
  6. Floating Text Box
    By Phil Walters in forum Excel General
    Replies: 3
    Last Post: 11-12-2012, 06:06 AM
  7. How do I add a floating text box in Excel?
    By Prashant in forum Excel General
    Replies: 1
    Last Post: 09-27-2005, 09:05 AM

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