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
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
excel 2016 BTW
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 :
The userform will remain on screen whilst you scroll around until dismissed.Please Login or Register to view this content.
HTH
DominicB
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.
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
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.
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??!!!
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
Userform box open and picture is added.
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
okay got that done ... how do I call it to the sheet?
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) :
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.Please Login or Register to view this content.
And that's just about it.
HTH
DominicB
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?
Hi phillyd023
I think we have three options here :If you want to go with either of the last two options and need some guidance, let me know.
- 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
HTH
DominicB
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?
Hi phillyd023
Something like this :
This needs to be placed as a module in the ThisWorkbook object - you can find this in the Project Explorer window of the VBE.Please Login or Register to view this content.
HTH
DominicB
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.
Hi phillyd023
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 :
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.Please Login or Register to view this content.
To enter a macro go to Insert > Module and paste some code like this into it:
To assign a shortcut go to Developer > Macros > Options and enter a shortcut to be entered with Ctrl.Please Login or Register to view this content.
HTH
DominicB
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.
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
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
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.
Hi phillyd023
Post 16 is answering your question on how to run the userform on opening the file.
This needs to be placed as a module in the ThisWorkbook object - you can find this in the Project Explorer window of the VBE.Please Login or Register to view this content.
When you close and re-open the file, the userform should open automtically without any action required on your behalf.
HTH
DominicB
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks